Checking MySQL Replication Status
The most common task when managing a replication process is to ensure that the 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 must be executed on each slave.
The key fields from the status report to examine are :
- 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.
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 SLAVE, you can check running processes using 'SHOW PROCESSLIST' command.
On the MASTER, you can also check the status of connected slaves using 'SHOW PROCESSLIST' to examine the list of running processes.
Because it is the slave that drives the replication process, very little information is available in this report.