SHOW SLAVE STATUS
This statement provides status information on essential
parameters of the slave threads. It requires either the
SUPER
or
REPLICATION CLIENT
privilege.
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
layout:
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 17.1.4.1, “Checking Replication Status”.
Slave_IO_State
A copy of the
State
field of theSHOW PROCESSLIST
output 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. Possible states are listed in Section 17.2.1, “Replication Implementation Details”.Master_Host
The master host that the slave is connected to.
Master_User
The user name of the account used to connect to the master.
Master_Port
The port used to connect to the master.
Connect_Retry
The number of seconds between connect retries (default 60). This can be set with the
CHANGE MASTER TO
statement.Master_Log_File
The name of the master binary log file from which the I/O thread is currently reading.
Read_Master_Log_Pos
The position in the current master binary log file up to which the I/O thread has read.
Relay_Log_File
The name of the relay log file from which the SQL thread is currently reading and executing.
Relay_Log_Pos
The position in the current relay log file up to which the SQL thread has read and executed.
Relay_Master_Log_File
The name of the master binary log file containing the most recent event executed by the SQL thread.
Slave_IO_Running
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,Slave_IO_Running
isNo
.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_Running
depends on the server version as shown in the following table.MySQL Version Slave_IO_Running
4.1 (4.1.13 and earlier); 5.0 (5.0.11 and earlier) Yes
4.1 (4.1.14 and later); 5.0 (5.0.12 and later) No
5.1 (5.1.45 and earlier); 5.4 No
5.1 (5.1.46 and later); 5.5 Connecting
6.0 (6.0.10 and earlier) No
6.0 (6.0.11 and later) Connecting
MYSQL_SLAVE_RUN_CONNECT
. The slave I/O thread is running, and is connected to a replication master. For this state,Slave_IO_Running
isYes
.
The value of the
Slave_running
system status variable corresponds with this value.Slave_SQL_Running
Whether the SQL thread is started.
Replicate_Do_DB
,Replicate_Ignore_DB
The lists of databases that were specified with the
--replicate-do-db
and--replicate-ignore-db
options, if any.Replicate_Do_Table
,Replicate_Ignore_Table
,Replicate_Wild_Do_Table
,Replicate_Wild_Ignore_Table
The lists of tables that were specified with the
--replicate-do-table
,--replicate-ignore-table
,--replicate-wild-do-table
, and--replicate-wild-ignore-table
options, if any.Last_Errno
,Last_Error
These columns are aliases for
Last_SQL_Errno
andLast_SQL_Error
.Issuing
RESET MASTER
orRESET SLAVE
resets the values shown in these columns.NoteWhen 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 STATUS
shows a nonzero value forLast_SQL_Errno
even thoughSlave_SQL_Running
still displaysYes
.Skip_Counter
The current value of the
sql_slave_skip_counter
system variable. See Section 12.5.2.4, “SET GLOBAL sql_slave_skip_counter
Syntax”.Exec_Master_Log_Pos
The position in the current master binary file up to which the SQL thread has read and executed. The coordinates given by (
Relay_Master_Log_File
,Exec_Master_Log_Pos
) in the master's binary log correspond to the coordinates given by (Relay_Log_File
,Relay_Log_Pos
) in the relay log.Relay_Log_Space
The total combined size of all existing relay log files.
Until_Condition
,Until_Log_File
,Until_Log_Pos
The values specified in the
UNTIL
clause of theSTART SLAVE
statement.Until_Condition
has these values:None
if noUNTIL
clause was specifiedMaster
if the slave is reading until a given position in the master's binary logRelay
if the slave is reading until a given position in its relay log
Until_Log_File
andUntil_Log_Pos
indicate the log file name and position that define the coordinates at which the SQL thread stops executing.Master_SSL_Allowed
,Master_SSL_CA_File
,Master_SSL_CA_Path
,Master_SSL_Cert
,Master_SSL_Cipher
,Master_SSL_Key
,Master_SSL_Verify_Server_Cert
These fields show the SSL parameters used by the slave to connect to the master, if any.
Master_SSL_Allowed
has these values:Yes
if an SSL connection to the master is permittedNo
if an SSL connection to the master is not permittedIgnored
if 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_CA
,MASTER_SSL_CAPATH
,MASTER_SSL_CERT
,MASTER_SSL_CIPHER
,MASTER_SSL_KEY
, andMASTER_SSL_VERIFY_SERVER_CERT
options to theCHANGE MASTER TO
statement. See Section 12.5.2.1, “CHANGE MASTER TO
Syntax”.Seconds_Behind_Master
This field is an indication of how “late” the slave is:
When the slave SQL thread is actively processing updates, this field is the number of seconds that have elapsed since the timestamp of the most recent event on the master executed by that thread.
When the SQL thread has caught up to the slave I/O thread and is idle waiting for more events from the I/O thread, this field is zero.
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_Master
often 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 though the master and slave do not have identical clocks (the clock difference is computed when the slave I/O thread starts, and assumed to remain constant from then on).
Seconds_Behind_Master
isNULL
(“unknown”) if the slave SQL thread is not running, or if the slave I/O thread is not running or not connected to 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 set by theMASTER_CONNECT_RETRY
option of theCHANGE MASTER TO
statement (default 60) before attempting to reconnect, the value isNULL
. This is because the slave cannot know what the master is doing, and so cannot say reliably how late it is.The value of this field is 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
TIMESTAMP
successfully. However, the problem forSeconds_Behind_Master
is that if M1 also receives direct updates from clients, theSeconds_Behind_Master
value randomly fluctuates because sometimes the last event from M1 originates from M0 and sometimes is the result of a direct update on M1.Last_IO_Errno
,Last_IO_Error
The error number and error message of the last 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_Error
value is not empty, the error values also appear in the slave's error log.Prior to MySQL 5.5,
Last_IO_Error
andLast_IO_Errno
were not set in the event that replication failed due to exceedingmax_allowed_packet
(Bug#42914).Issuing
RESET MASTER
orRESET SLAVE
resets the values shown in these columns.Last_SQL_Errno
,Last_SQL_Error
The error number and error message of the last 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_Error
value is not empty, the error values also appear in the slave's error log.Example:
Last_SQL_Errno: 1051 Last_SQL_Error: error 'Unknown table 'z'' on query 'drop table z'
The message indicates that the table
z
existed on the master and was dropped there, but it did not exist on the slave, soDROP TABLE
failed on the slave. (This might occur, for example, if you forget to copy the table to the slave when setting up replication.)Issuing
RESET MASTER
orRESET SLAVE
resets the values shown in these columns.Replicate_Ignore_Server_Ids
Beginning with MySQL 5.5, you can tell a slave to ignore events from 0 or more masters using the
IGNORE_SERVER_IDS
option in aCHANGE MASTER TO
statement. This is normally of interest only when using a circular or other multi-master replication setup.The message shown for
Replicate_Ignore_Server_Ids
consists 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 aCHANGE MASTER TO
statement containing theIGNORE_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
Master_Server_Id
The
server_id
value from the master.