Related Documentation Download this Excerpt
PDF (US Ltr) - 1.1Mb
PDF (A4) - 1.1Mb

MySQL Replication  /  ...  /  Checking Replication Status

2.5.1 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 source. The primary statement for this is SHOW SLAVE STATUS, which you must execute on each slave:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: source1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 931
               Relay_Log_File: slave1-relay-bin.000056
                Relay_Log_Pos: 950
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 931
              Relay_Log_Space: 1365
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0
  Replicate_Ignore_Server_Ids: 0

The key fields from the status report to examine are:

  • Slave_IO_State: The current status of the slave. See Replication Replica I/O Thread States, and Replication Replica SQL Thread States, for more information.

  • Slave_IO_Running: Whether the I/O thread for reading the source'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 source's binary log. A high number (or an increasing one) can indicate that the slave is unable to handle events from the source in a timely fashion.

    A value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the source, but there are some cases where this is not strictly true. For example, this can occur if the network connection between source 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 source's binary log and processing them in the relay log:

  • (Master_Log_file, Read_Master_Log_Pos): Coordinates in the source's 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 source's 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 the replica's relay log coordinates rather than the source's binary log coordinates.

The SHOW STATUS statement also provides some information relating specifically to replication slaves. The replication heartbeat information displayed by SHOW STATUS lets you check that the replication connection is active even if the source has not sent events to the slave recently. The source sends a heartbeat signal to a slave if there are no updates to, and no unsent events in, the binary log for a longer period than the heartbeat interval. The MASTER_HEARTBEAT_PERIOD setting on the source (set by the CHANGE MASTER TO statement) specifies the frequency of the heartbeat, which defaults to half of the connection timeout interval for the slave (slave_net_timeout). The Slave_last_heartbeat variable for SHOW STATUS shows when the replication slave last received a heartbeat signal.

On the source, 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:

*************************** 4. row ***************************
     Id: 10
   User: root
   Host: slave1:58371
     db: NULL
Command: Binlog Dump
   Time: 777
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

Because it is the slave that drives the replication process, very little information is available in this report.

For slaves that were started with the --report-host option and are connected to the source, the SHOW SLAVE HOSTS statement on the source shows basic information about the slaves. The output includes the ID of the slave server, the value of the --report-host option, the connecting port, and source ID:

| Server_id | Host   | Port | Rpl_recovery_rank | Master_id |
|        10 | slave1 | 3306 |                 0 |         1 |
1 row in set (0.00 sec)