MySQL : Cheking MySQL replication status
Once replication has been started it should execute without requiring much regular administration. Depending on your replication environment, you will want to check the replication status of each slave periodically, daily, or even more frequently.
Checking Replication Status:
The most common task when managing a replication process is to ensure that replication is taking place and that there have been no errors between the slave and the master. The primary statement for this is
SHOW SLAVE STATUS
which you must execute on each slave:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000114
Read_Master_Log_Pos: 931
Relay_Log_File: slave1-relay-bin.000156
Relay_Log_Pos: 950
Relay_Master_Log_File: mysql-bin.000114
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: 931
Relay_Log_Space: 1365
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Let’s see the key field of slave status command
Slave_IO_State: The current status of the slave.
Slave_IO_Running:Â Whether the I/O thread for reading the master’s binary log is running. Normally, you want this to be Yes unless you have not yet started replication or have explicitly stopped it with STOP SLAVE.
Slave_SQL_Running: Whether the SQL thread for executing events in the relay log is running. As with the I/O thread, this should normally be Yes.
Last_IO_Error, Last_SQL_Error: The last errors registered by the I/O and SQL threads when processing the relay log. Ideally these should be blank, indicating no errors.
Seconds_Behind_Master: The number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to handle events from the master in a timely fashion.
Value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the master, but there are some cases where this is not strictly true. For example, this can occur if the network connection between master and slave is broken but the slave I/O thread has not yet noticed this—that is, slave_net_timeout has not yet elapsed.
It is also possible that transient values for Seconds_Behind_Master may not reflect the situation accurately. When the slave SQL thread has caught up on I/O, Seconds_Behind_Master displays 0; but when the slave I/O thread is still queuing up a new event, Seconds_Behind_Master may show a large value until the SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute SHOW SLAVE STATUS several times in a relatively short period, you may see this value change back and forth repeatedly between 0 and a relatively large value.
Several pairs of fields provide information about the progress of the slave in reading events from the
master binary log and processing them in the relay log:
Master_Log_file, Read_Master_Log_Pos: Coordinates in the master binary log indicating how far the slave I/O thread has read events from that log.
Relay_Master_Log_File, Exec_Master_Log_Pos: Coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log.
Relay_Log_File, Relay_Log_Pos: Coordinates in the slave relay log indicating how far the slave SQL thread has executed the relay log. These correspond to the preceding coordinates, but are expressed in slave relay log coordinates rather than master binary log coordinates.
On the master, you can check the status of connected slaves using SHOW PROCESSLIST to examine the list of running processes. Slave connections have Binlog Dump in the Command field:
mysql> SHOW PROCESSLIST \G;
*************************** 4. row ***************************
Id: 15
User: root
Host: slave1:583722
db: NULL
Command: Binlog Dump
Time: 771
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
