MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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:
mysql> SHOW SLAVE STATUS\G
*************************** 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
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:
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 Section 8.14.7, “Replication Replica I/O Thread States”, and
Section 8.14.8, “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:
mysql> SHOW PROCESSLIST \G;
*************************** 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:
mysql> SHOW SLAVE HOSTS;
+-----------+--------+------+-------------------+-----------+
| Server_id | Host | Port | Rpl_recovery_rank | Master_id |
+-----------+--------+------+-------------------+-----------+
| 10 | slave1 | 3306 | 0 | 1 |
+-----------+--------+------+-------------------+-----------+
1 row in set (0.00 sec)