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 replica and the source.
REPLICA | SLAVE STATUS statement, which you must
execute on each replica, provides information about the
configuration and status of the connection between the replica
server and the source server. From MySQL 8.0.22,
SHOW SLAVE STATUS is deprecated,
SHOW REPLICA STATUS is
available to use instead. The Performance Schema has replication
tables that provide this information in a more accessible form.
See Performance Schema Replication Tables.
The replication heartbeat information shown in the Performance
Schema replication tables lets you check that the replication
connection is active even if the source has not sent events to
the replica recently. The source sends a heartbeat signal to a
replica 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
Performance Schema table shows when the most recent heartbeat
signal was received by a replica, and how many heartbeat signals
it has received.
If you are using the
REPLICA | SLAVE STATUS statement to check on the
status of an individual replica, the statement provides the
mysql> SHOW REPLICA STATUS\G *************************** 1. row *************************** Replica_IO_State: Waiting for master to send event Source_Host: source1 Source_User: root Source_Port: 3306 Connect_Retry: 60 Source_Log_File: mysql-bin.000004 Read_Source_Log_Pos: 931 Relay_Log_File: replica1-relay-bin.000056 Relay_Log_Pos: 950 Relay_Source_Log_File: mysql-bin.000004 Replica_IO_Running: Yes Replica_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_Source_Log_Pos: 931 Relay_Log_Space: 1365 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: 0
The key fields from the status report to examine are:
Replica_IO_Running: Whether the I/O thread for reading the source's binary log is running. Normally, you want this to be
Yesunless you have not yet started replication or have explicitly stopped it with
STOP REPLICA | SLAVE.
Replica_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
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_Source: The number of seconds that the replication SQL thread is behind processing the source binary log. A high number (or an increasing one) can indicate that the replica is unable to handle events from the source in a timely fashion.
A value of 0 for
Seconds_Behind_Sourcecan usually be interpreted as meaning that the replica 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 replica is broken but the replication I/O thread has not yet noticed this; that is,
slave_net_timeouthas not yet elapsed.
It is also possible that transient values for
Seconds_Behind_Sourcemay not reflect the situation accurately. When the replication SQL thread has caught up on I/O,
Seconds_Behind_Sourcedisplays 0; but when the replication I/O thread is still queuing up a new event,
Seconds_Behind_Sourcemay show a large value until the replication SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute
SHOW REPLICA | SLAVE STATUSseveral 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 replica in reading events from the source binary log and processing them in the relay log:
Read_Master_Log_Pos): Coordinates in the source binary log indicating how far the replication I/O thread has read events from that log.
Exec_Master_Log_Pos): Coordinates in the source binary log indicating how far the replication SQL thread has executed events received from that log.
Relay_Log_Pos): Coordinates in the replica relay log indicating how far the replication SQL thread has executed the relay log. These correspond to the preceding coordinates, but are expressed in replica relay log coordinates rather than source binary log coordinates.
On the source, you can check the status of connected replicas
SHOW PROCESSLIST to examine
the list of running processes. Replica connections have
Binlog Dump in the
mysql> SHOW PROCESSLIST \G; *************************** 4. row *************************** Id: 10 User: root Host: replica1: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 replica that drives the replication process, very little information is available in this report.
For replicas that were started with the
--report-host option and are
connected to the source, the
SHOW REPLICAS |
SHOW SLAVE HOSTS statement on the source shows basic
information about the replicas. The output includes the ID of
the replica server, the value of the
--report-host option, the
connecting port, and source ID:
mysql> SHOW REPLICAS; +-----------+----------+------+-------------------+-----------+ | Server_id | Host | Port | Rpl_recovery_rank | Source_id | +-----------+----------+------+-------------------+-----------+ | 10 | replica1 | 3306 | 0 | 1 | +-----------+----------+------+-------------------+-----------+ 1 row in set (0.00 sec)