Search code examples
mysqldatabase-replicationmaster-slave

Mysql finding the updated slave

I've set up simple MySQL replication: 1 master and 3 slaves.

How to find out in those 3 slaves which is the latest and perfectly sync before the master server is down? Let's say I don't have access on the server because it is totally damage.

Solution

  • A slave works with 2 threads. The Slave_IO thread and the Slave_SQL thread.

    The Slave_IO thread is responsible for reading the binary log from the master and writing what he read in the relay log on the slave.

    The Slave_SQL thread is responsible for executing the statements in the relay log.

    A slave is up to date not when Seconds_behind_master is 0 (I'm talking about the output of SHOW SLAVE STATUS\G), but when

    • Relay_Master_log_file is the same as Master_log_file and
    • Exec_Master_log_pos is the same as Read_Master_log_pos

    Again, in other words Master_Log_File and Read_Master_Log_Pos is what the slave is fetching from the master.
    And Relay_Master_Log_File and Exec_Master_Log_Pos is what is being executed in master "coordinates".
    And, just for completeness but not really interesting, Relay_Log_File and Relay_Log_Pos is what is being executed from the relay log.

    Hope this answers your question.