Mysql – Anyway to identify thesql slaves have read upto which binary logs

MySQLreplication

I have multiple slaves reading from one master.. But few of them are keep disconnecting due to network issue.

Is there any way that i can make sure before setting up the bin log purging script on master that all the slave have read upto certain bin logs which we can purge?

Best Answer

The easiest method is the following

  • Go to each Slave and run SHOW SLAVE STATUS\G
  • Look for Relay_Master_Log_File on Each Slave
  • Whichever Slave has the oldest Relay_Master_Log_File is the one you purge to on the Master

Why Relay_Master_Log_File ? First Look at SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000255
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

You see Master_Log_File and Relay_Master_Log_File

In this display they are different. So why choose Relay_Master_Log_File ?

  • Master_Log_File represents the binlog with the last binlog event downloaded to the Slave
  • Relay_Master_Log_File represents the binlog with the last binlog event executed on the Slave

In the event of any replication lag, Relay_Master_Log_File is always the oldest. If they are the same, fine. You choose Relay_Master_Log_File always.

In this case, you purge to mysql-bin.000254 on the Master. In the event the SQL thread dies, you don't want to erase binlogs from the Master the Slave has not processed yet.

Getting back to multiple slaves, you choose the oldest Relay_Master_Log_File of all Slaves.

To clarify, the oldest Relay_Master_Log_File is the binary log name with the lowest number.

Related Question