Rechercher dans le manuel MySQL
17.1.7.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 master.
The SHOW SLAVE STATUS
statement,
which you must execute on each slave, provides information about
the configuration and status of the connection between the slave
server and the master server. From MySQL 5.7, the Performance
Schema has replication tables that provide this information in a
more accessible form. See
Section 26.12.11, “Performance Schema Replication Tables”.
The SHOW STATUS
statement also
provided some information relating specifically to replication
slaves. From MySQL 5.7, the following status variables
previously monitored using SHOW
STATUS
were deprecated and moved to the 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 master has not sent events to
the slave recently. The master 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 master
(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
replication_connection_status
Performance Schema table shows when the most recent heartbeat
signal was received by a replication slave, and how many
heartbeat signals it has received.
If you are using the SHOW SLAVE
STATUS
statement to check on the status of an
individual slave, the statement provides the following
information:
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: master1
- Master_User: root
- Master_Port: 3306
- Connect_Retry: 60
- Read_Master_Log_Pos: 931
- Relay_Log_Pos: 950
- 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.4, “Replication Slave I/O Thread States”, and Section 8.14.5, “Replication Slave SQL Thread States”, for more information.Slave_IO_Running
: Whether the I/O thread for reading the master's binary log is running. Normally, you want this to beYes
unless you have not yet started replication or have explicitly stopped it withSTOP 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 beYes
.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.A 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 executeSHOW 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:
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 master, the SHOW SLAVE
HOSTS
statement on the master 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 master ID:
- +-----------+--------+------+-------------------+-----------+
- | Server_id | Host | Port | Rpl_recovery_rank | Master_id |
- +-----------+--------+------+-------------------+-----------+
- | 10 | slave1 | 3306 | 0 | 1 |
- +-----------+--------+------+-------------------+-----------+
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-replication-administration-status.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.