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: 3306 Connect_Retry: 3 Master_Log_File: gbichot-bin.005 Read_Master_Log_Pos: 79 Relay_Log_File: gbichot-relay-bin.005 Relay_Log_Pos: 548 Relay_Master_Log_File: gbichot-bin.005 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: 79 Relay_Log_Space: 552 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: 8 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 Master_Server_Id: 1
The following list describes the fields returned by
SHOW SLAVE STATUS. For additional
information about interpreting their meanings, see
Section 8.14.6, “Replication Slave I/O Thread States”.
A copy of the
Statefield of the
SHOW PROCESSLISToutput for the slave I/O thread. This tells you what the thread is doing: trying to connect to the master, waiting for events from the master, reconnecting to the master, and so on. For a listing of possible states, see Section 8.14.6, “Replication Slave I/O Thread States”.
The master host that the slave is connected to.
The user name of the account used to connect to the master.
The port used to connect to the master.
The number of seconds between connect retries (default 60). This can be set with the
CHANGE MASTER TOstatement.
The name of the master binary log file from which the I/O thread is currently reading.
The position in the current master 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 master 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 master. Internally, the state of this thread is represented by one of the following three values:
MYSQL_SLAVE_NOT_RUN. The slave I/O thread is not running. For this state,
MYSQL_SLAVE_RUN_NOT_CONNECT. The slave I/O thread is running, but is not connected to a replication master. For this state,
Slave_IO_Runningdepends on the server version as shown in the following table.
4.1 (4.1.13 and earlier); 5.0 (5.0.11 and earlier)
4.1 (4.1.14 and later); 5.0 (5.0.12 and later)
5.1 (5.1.45 and earlier)
5.1 (5.1.46 and later); 5.5
MYSQL_SLAVE_RUN_CONNECT. The slave I/O thread is running, and is connected to a replication master. 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 slave 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 188.8.131.52, “SET GLOBAL sql_slave_skip_counter Syntax”.
The position in the current master 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 slave from an existing slave, so that the new slave reads from this point. The coordinates given by (
Exec_Master_Log_Pos) in the master's binary log correspond to the coordinates given by (
Relay_Log_Pos) in the relay log.
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 slave is reading until a given position in the master's binary log
Relayif the slave is reading until a given position in its relay log
Until_Log_Posindicate the log file name and position that define the coordinates at which the SQL thread stops executing.
These fields show the SSL parameters used by the slave to connect to the master, if any.
Master_SSL_Allowedhas these values:
Yesif an SSL connection to the master is permitted
Noif an SSL connection to the master is not permitted
Ignoredif an SSL connection is permitted but the slave 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 184.108.40.206, “CHANGE MASTER TO Syntax”.
This field is an indication of how “late” the slave is:
When the slave is actively processing updates, this field shows the difference between the current timestamp on the slave and the original timestamp logged on the master for the event currently being processed on the slave.
When no event is currently being processed on the slave, this value is 0.
In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread. If the network connection between master and slave is fast, the slave I/O thread is very close to the master, so this field is a good approximation of how late the slave SQL thread is compared to the master. If the network is slow, this is not a good approximation; the slave SQL thread may quite often be caught up with the slow-reading slave I/O thread, so
Seconds_Behind_Masteroften shows a value of 0, even if the I/O thread is late compared to the master. In other words, this column is useful only for fast networks.
This time difference computation works even if the master and slave do not have identical clock times, provided that the difference, computed when the slave I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock skews that can make calculation of
This field is
NULL(undefined or unknown) if the slave SQL thread is not running, or if the slave I/O thread is not running or is not connected to the master. For example, if the slave I/O thread is running but is not connected to the master and is sleeping for the number of seconds given by the
CHANGE MASTER TOstatement or
--master-connect-retryoption (default 60) before reconnecting, the value is
NULL. This is because the slave cannot know what the master is doing, and so cannot say reliably how late it is.
The value of
Seconds_Behind_Masteris based on the timestamps stored in events, which are preserved through replication. This means that if a master M1 is itself a slave 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.
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 slave's error log.
Prior to MySQL 5.5,
Last_IO_Errnowere not set in the event that replication failed due to exceeding
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 slave's error log.
Last_SQL_Errno: 1051 Last_SQL_Error: error 'Unknown table 'z'' on query 'drop table z'
The message indicates that the table
zexisted on the master and was dropped there, but it did not exist on the slave, so
DROP TABLEfailed on the slave. (This might occur, for example, if you forget to copy the table to the slave when setting up replication.)
Beginning with MySQL 5.5, you can tell a slave to ignore events from 0 or more masters using the
IGNORE_SERVER_IDSoption of the
CHANGE MASTER TOstatement. This is normally of interest only when using a circular or other multi-master replication setup.
The message shown for
Replicate_Ignore_Server_Idsconsists of a space-delimited list of one or more numbers, the first value indicating the number of servers to be ignored; if not 0 (the default), this server-count value is followed by the actual server IDs. For example, if a
CHANGE MASTER TOstatement containing the
IGNORE_SERVER_IDS = (2,6,9)option has been issued to tell a slave to ignore masters having the server ID 2, 6, or 9, that information appears as shown here:
Replicate_Ignore_Server_Ids: 3 2 6 9
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 master.