SHOW SLAVE STATUS
If you issue this statement using the mysql
client, you can use a
\G statement terminator
rather than a semicolon to obtain a more readable vertical
mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 13000 Connect_Retry: 60 Master_Log_File: source-bin.000002 Read_Master_Log_Pos: 1307 Relay_Log_File: replica-relay-bin.000003 Relay_Log_Pos: 1508 Relay_Master_Log_File: source-bin.000002 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: 1307 Relay_Log_Space: 1858 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: Master_Server_Id: 1 Master_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562 Master_Info_File: /var/mysqld.2/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Reading event from the relay log Master_Retry_Count: 10 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5 Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5 Auto_Position: 1
The following list describes the fields returned by
SHOW SLAVE STATUS. For additional
information about interpreting their meanings, see
Section 8.14.7, “Replication Replica I/O Thread States”.
A copy of the
Statefield of the
SHOW PROCESSLISToutput for the replica I/O thread. This tells you what the thread is doing: trying to connect to the source, waiting for events from the source, reconnecting to the source, and so on. For a listing of possible states, see Section 8.14.7, “Replication Replica I/O Thread States”.
The source host that the replica is connected to.
The user name of the account used to connect to the source.
The port used to connect to the source.
The number of seconds between connect retries (default 60). This can be set with the
CHANGE MASTER TOstatement.
The name of the source binary log file from which the I/O thread is currently reading.
The position in the current source binary log file up to which the I/O thread has read.
The name of the relay log file from which the SQL thread is currently reading and executing.
The position in the current relay log file up to which the SQL thread has read and executed.
The name of the source binary log file containing the most recent event executed by the SQL thread.
Whether the I/O thread is started and has connected successfully to the source. Internally, the state of this thread is represented by one of the following three values:
MYSQL_SLAVE_NOT_RUN. The replica I/O thread is not running. For this state,
MYSQL_SLAVE_RUN_NOT_CONNECT. The replica I/O thread is running, but is not connected to a replication source. For this state,
MYSQL_SLAVE_RUN_CONNECT. The replica I/O thread is running, and is connected to a replication source. For this state,
The value of the
Slave_runningsystem status variable corresponds with this value.
Whether the SQL thread is started.
These columns are aliases for
When the replica SQL thread receives an error, it reports the error first, then stops the SQL thread. This means that there is a small window of time during which
SHOW SLAVE STATUSshows a nonzero value for
The current value of the
sql_slave_skip_countersystem variable. See Section 220.127.116.11, “SET GLOBAL sql_slave_skip_counter Statement”.
The position in the current source binary log file to which the SQL thread has read and executed, marking the start of the next transaction or event to be processed. You can use this value with the
CHANGE MASTER TOstatement's
MASTER_LOG_POSoption when starting a new replica from an existing replica, so that the new replica reads from this point. The coordinates given by (
Exec_Master_Log_Pos) in the source's binary log correspond to the coordinates given by (
Relay_Log_Pos) in the relay log.
When using a multithreaded replica (by setting
slave_parallel_workersto a nonzero value), the value in this column actually represents a “low-water” mark, before which no uncommitted transactions remain. Because the current implementation allows execution of transactions on different databases in a different order on the replica than on the source, this is not necessarily the position of the most recently executed transaction.
The total combined size of all existing relay log files.
The values specified in the
UNTILclause of the
Until_Conditionhas these values:
UNTILclause was specified
Masterif the replica is reading until a given position in the source's binary log
Relayif the replica is reading until a given position in its relay log
SQL_BEFORE_GTIDSif the replica SQL thread is processing transactions until it has reached the first transaction whose GTID is listed in the
SQL_AFTER_GTIDSif the replica threads are processing all transactions until the last transaction in the
gtid_sethas been processed by both threads.
SQL_AFTER_MTS_GAPSif a multithreaded replica's SQL threads are running until no more gaps are found in the relay log.
Until_Log_Posindicate the log file name and position that define the coordinates at which the SQL thread stops executing.
For more information on
UNTILclauses, see Section 18.104.22.168, “START SLAVE Statement”.
These fields show the SSL parameters used by the replica to connect to the source, if any.
Master_SSL_Allowedhas these values:
Yesif an SSL connection to the source is permitted
Noif an SSL connection to the source is not permitted
Ignoredif an SSL connection is permitted but the replica server does not have SSL support enabled
The values of the other SSL-related fields correspond to the values of the
MASTER_SSL_VERIFY_SERVER_CERToptions to the
CHANGE MASTER TOstatement. See Section 22.214.171.124, “CHANGE MASTER TO Statement”.
This field is an indication of how “late” the replica is:
When the replica is actively processing updates, this field shows the difference between the current timestamp on the replica and the original timestamp logged on the source for the event currently being processed on the replica.
When no event is currently being processed on the replica, this value is 0.
In essence, this field measures the time difference in seconds between the replica SQL thread and the replica I/O thread. If the network connection between source and replica is fast, the replica I/O thread is very close to the source, so this field is a good approximation of how late the replica SQL thread is compared to the source. If the network is slow, this is not a good approximation; the replica SQL thread may quite often be caught up with the slow-reading replica I/O thread, so
Seconds_Behind_Masteroften shows a value of 0, even if the I/O thread is late compared to the source. In other words, this column is useful only for fast networks.
This time difference computation works even if the source and replica do not have identical clock times, provided that the difference, computed when the replica I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock skews that can make calculation of
In MySQL 5.6.9 and later, this field is
NULL(undefined or unknown) if the replica SQL thread is not running, or if the SQL thread has consumed all of the relay log and the replica I/O thread is not running. Previously, this field was
NULLif the replica SQL thread or the replica I/O thread was not running or was not connected to the source. (Bug #12946333) For example, if (prior to MySQL 5.6.9) the replica I/O thread was running but was not connected to the source and was sleeping for the number of seconds given by the
CHANGE MASTER TOstatement or
--master-connect-retryoption (default 60) before reconnecting, the value was
NULL. Now in such cases, the connection to the source is not tested; instead, if the I/O thread is running but the relay log is exhausted,
Seconds_Behind_Masteris set to 0.
The value of
Seconds_Behind_Masteris based on the timestamps stored in events, which are preserved through replication. This means that if a source M1 is itself a replica of M0, any event from M1's binary log that originates from M0's binary log has M0's timestamp for that event. This enables MySQL to replicate
TIMESTAMPsuccessfully. However, the problem for
Seconds_Behind_Masteris that if M1 also receives direct updates from clients, the
Seconds_Behind_Mastervalue randomly fluctuates because sometimes the last event from M1 originates from M0 and sometimes is the result of a direct update on M1.
When using a multithreaded replica, you should keep in mind that this value is based on
Exec_Master_Log_Pos, and so may not reflect the position of the most recently committed transaction.
The error number and error message of the most recent error that caused the I/O thread to stop. An error number of 0 and message of the empty string mean “no error.” If the
Last_IO_Errorvalue is not empty, the error values also appear in the replica's error log.
I/O error information includes a timestamp showing when the most recent I/O thread error occurred. This timestamp uses the format
YYMMDD hh:mm:ss, and appears in the
The error number and error message of the most recent error that caused the SQL thread to stop. An error number of 0 and message of the empty string mean “no error.” If the
Last_SQL_Errorvalue is not empty, the error values also appear in the replica's error log.
SQL error information includes a timestamp showing when the most recent SQL thread error occurred. This timestamp uses the format
YYMMDD hh:mm:ss, and appears in the
In MySQL 5.6, you set a replica to ignore events from 0 or more sources using the
IGNORE_SERVER_IDSoption of the
CHANGE MASTER TOstatement. By default this is blank, and is usually modified only when using a circular or other multi-source replication setup. The message shown for
Replicate_Ignore_Server_Idswhen not blank consists of a comma-delimited list of one or more numbers, indicating the server IDs to be ignored. For example:
Replicate_Ignore_Server_Ids: 2, 6, 9Note
Ignored_server_idsalso shows the server IDs to be ignored, but is a space-delimited list, which is preceded by the total number of server IDs to be ignored. For example, if a
CHANGE MASTER TOstatement containing the
IGNORE_SERVER_IDS = (2,6,9)option has been issued to tell a replica to ignore sources having the server ID 2, 6, or 9, that information appears as:
Ignored_server_ids: 3 2 6 9
3is the total number of server IDs being ignored.
Replicate_Ignore_Server_Idsfiltering is performed by the I/O thread, rather than by the SQL thread, which means that events which are filtered out are not written to the relay log. This differs from the filtering actions taken by server options such
--replicate-do-table, which apply to the SQL thread.
server_idvalue from the source.
server_uuidvalue from the source.
The location of the
The number of seconds that the replica must lag the source.
Waiting until MASTER_DELAY seconds after master executed event, this field contains the number of delay seconds remaining. At other times, this field is
The state of the SQL thread (analogous to
Slave_IO_State). The value is identical to the
Statevalue of the SQL thread as displayed by
SHOW PROCESSLIST; Section 8.14.8, “Replication Replica SQL Thread States”, provides a listing of possible states.
The number of times the replica can attempt to reconnect to the source in the event of a lost connection. This value can be set using the
MASTER_RETRY_COUNToption of the
CHANGE MASTER TOstatement (preferred) or the older
--master-retry-countserver option (still supported for backward compatibility).
The network interface that the replica is bound to, if any. This is set using the
MASTER_BINDoption for the
CHANGE MASTER TOstatement.
A timestamp in
YYMMDD hh:mm:ssformat that shows when the most recent I/O error took place.
A timestamp in
YYMMDD hh:mm:ssformat that shows when the most recent SQL error occurred.
The set of global transaction IDs corresponding to all transactions received by this replica. Empty if GTIDs are not in use.
This is the set of all GTIDs that exist or have existed in the relay logs. Each GTID is added as soon as the
Gtid_log_eventis received. This can cause partially transmitted transactions to have their GTIDs included in the set.
When all relay logs are lost due to executing
CHANGE MASTER TO, or due to the effects of the
--relay-log-recoveryoption, the set is cleared. When
relay_log_purge = 1, the newest relay log is always kept, and the set is not cleared.
The set of global transaction IDs written in the binary log. This is the same as the value for the global
gtid_executedsystem variable on this server, as well as the value for
Executed_Gtid_Setin the output of
SHOW MASTER STATUSon this server. Empty if GTIDs are not in use. See GTID Sets for more information.
1 if autopositioning is in use; otherwise 0.