MYSQL Master-Slave Synchronization time

Question

I have a mysql master database and multiple slave databases. Master is used for WRITE only and slaves for READ only.

If I update (WRITE) to master, how much time it will take to synchronize with the slaves?

What will happen if anyone tries to READ the data from any of the slaves DB simultaneous to the synchronization time?

Answer 1

You can execute Show slave status; on your Slaves to see a number of parameters that are associated with how your slave is operating in sync with Master.

In your case Seconds_Behind_Master: would tell you how many seconds your Slave is lagging behind the Master. If you see 0 seconds there, it usually means that the Slave is in sync with the master and that the Slave is waiting for the next query.

You can verify this by looking at the Slave_SQL_Running_State: and seeing if it reads Slave has read all relay log; waiting for the slave I/O thread to update it and on the Master side Has sent all binlog to slave; waiting for binlog to be updated under show processlist;

Now there are certain outlying cases, errors, and one offs that you should be aware of. You can read more Here and Here.

Answer 2

The sync time can vary quite a bit depending on the connection speed between the two machines. It's a good idea to benchmark it.

To answer your second question, if the data has not yet been transferred from the master to the slave and you read from the slave, then the data won't be there yet to read.