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:
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:
SHOW SLAVE STATUS
returns the
following fields:
Slave_IO_State
A copy of the State
field of the output
of SHOW PROCESSLIST
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 16.4.1, “Replication Implementation Details”. It is
necessary to check this field for older versions of MySQL
which allowed the thread to continue running while
unsuccessfully trying to connect to the master. If it is
running, there is no problem; if it is not, you can find the
error in the Last_Error
field (described
below).
Master_Host
The current master host.
Master_User
The current user used to connect to the master.
Master_Port
The current master port.
Connect_Retry
The number of seconds between connect retries (default 60).
This may be set with the CHANGE MASTER
TO
statement or
--master-connect-retry
option.
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 up to which the I/O thread has read in the current master binary log.
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 up to which the SQL thread has read and executed in the current relay log.
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 3 values; these are shown with their meanings in the following list:
MYSQL_SLAVE_NOT_RUN
.
The slave I/O thread is not running.
MYSQL_SLAVE_RUN_NOT_CONNECT
.
The slave I/O thread is running, but is not connected
to a replication master.
MYSQL_SLAVE_RUN_CONNECT
.
The slave I/O thread is running, and is connected to a
replication master.
Different values are displayed for
Slave_IO_running
depending on the slave
I/O thread's actual state and the version of MySQL used
on the replication slave, as shown in the following table.
MySQL Version | MYSQL_SLAVE_NOT_RUN |
MYSQL_SLAVE_RUN_NOT_CONNECT |
MYSQL_SLAVE_RUN_CONNECT |
---|---|---|---|
4.1 (4.1.13 and earlier); 5.0 (5.0.11 and earlier) | No |
Yes |
Yes |
4.1 (4.1.14 and later); 5.0 (5.0.12 and later) | No |
No |
Yes |
5.1; 5.4 (5.4.3 and earlier) | No |
No |
Yes |
5.4 (5.4.4 and later) | No |
Connecting |
Yes |
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
As of MySQL 5.1.20, these columns are aliases for
Last_SQL_Errno
and
Last_SQL_Error
. Before 5.1.20, they
indicate the error number and error message returned by the
most recently executed statement. An error number of 0 and
message of the empty string mean “no error.” If
the Last_Error
value is not empty, it
also appears as a message in the slave's error log.
Beginning with MySQL 5.1.37, and with MySQL Cluster NDB
6.2.17, MySQL Cluster NDB 6.3.23, and MySQL Cluster NDB
6.4.3: Issuing RESET MASTER
or RESET SLAVE
resets the
values shown in these columns. (Bug#34654, Bug#44270)
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 STATUS
shows a nonzero value
for Last_Errno
even though
Slave_SQL_Running
still displays
Yes
.
Skip_Counter
The most recently used value for
SQL_SLAVE_SKIP_COUNTER
.
Exec_Master_Log_Pos
The position of the last event executed by the SQL thread
from the master's binary log
(Relay_Master_Log_File
).
(Relay_Master_Log_File
,
Exec_Master_Log_Pos
) in the master's
binary log corresponds to
(Relay_Log_File
,
Relay_Log_Pos
) in the relay log.
Relay_Log_Space
The total combined size of all existing relay logs.
Until_Condition
,
Until_Log_File
,
Until_Log_Pos
The values specified in the UNTIL
clause
of the START SLAVE
statement.
Until_Condition
has these values:
None
if no UNTIL
clause was specified
Master
if the slave is reading until
a given position in the master's binary logs
Relay
if the slave is reading until a
given position in its relay logs
Until_Log_File
and
Until_Log_Pos
indicate the log file name
and position values that define the point 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
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 permitted
No
if an SSL connection to the master
is not permitted
Ignored
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
, and
MASTER_SSL_VERIFY_SERVER_CERT
options to
the CHANGE MASTER TO
statement. See Section 12.6.2.1, “CHANGE MASTER TO
Syntax”.
MASTER_SSL_VERIFY_SERVER_CERT
was added
in MySQL 5.1.18.
Seconds_Behind_Master
This field is an indication of how “late” the slave is:
When the slave SQL thread is actively running (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 goes 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
is
NULL
(which means “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 sleeping for the number
of seconds given by the CHANGE MASTER
TO
statement or
--master-connect-retry
option
(default 60) before reconnecting, NULL
is
shown, as the slave cannot know what the master is doing,
and so cannot say reliably how late it is.
This field has one limitation. The timestamp is preserved
through replication, which means that, if a master M1 is
itself a slave of M0, any event from M1's binlog which
originates in replicating an event from M0's binlog has the
timestamp of that event. This enables MySQL to replicate
TIMESTAMP
successfully.
However, the problem for
Seconds_Behind_Master
is that if M1 also
receives direct updates from clients, the value randomly
deviates, because sometimes the last M1's event is from M0
and sometimes it is the most recent timestamp from a direct
update.
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, it
also appears as a message in the slave's error log. These
columns were added in MySQL 5.1.20.
MySQL Cluster.
Beginning with MySQL Cluster NDB 6.2.17, 6.3.23, and
6.4.3: Issuing RESET MASTER
or RESET SLAVE
resets the
values shown in these columns. This applies to MySQL
Cluster only. (Bug#34654)
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_IO_Error
value is not empty, it
also appears as a message in the slave's error log. These
columns were added in MySQL 5.1.20.
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, so DROP
TABLE
failed on the slave. (This might occur, for
example, if you forget to copy the table to the slave when
setting up replication.)
MySQL Cluster.
Beginning with MySQL Cluster NDB 6.2.17, 6.3.23, and
6.4.3: Issuing RESET MASTER
or RESET SLAVE
resets the
values shown in these columns. This applies to MySQL
Cluster only. (Bug#34654)
User Comments
Between different versions of mysql, the capitalization of the column names chagned. Careful if you write your own scripts around this.
If you find your slave status to show this:
Slave_IO_State: Connecting to Master
...
Slave_IO_Running: No
Slave_SQL_Running: Yes
...
and replication is not happening, your problem may be the slave configuration is wrong. Either the log file name is incorrect, or the replication user's password is wrong, are probably the most common cases. There doesn't seem to be any error message shown anywhere, but you can try these things. Check the file names by SHOW MASTER STATUS on the master, and doublecheck it against the master.info file on the slave. For the password, you can issue a new GRANT statement on the master, and a matching CHANGE MASTER statement on the slave, to be sure they are the same.
Here's a quick php code snippet to read your master.info and relay-log.info and generate a change master sql command from it. Very useful if you get corrupted relay logs or have had to purge your relay logs, etc.
<?php
$master_info=array();
$relay_info=array();
$x=0;
$mi=fopen('/tmp/master.info','r'); //<-- replace
$ri=fopen('/tmp/relay-log.info','r'); //<-- replace
if($mi){
while(!feof($mi)){
$master_info[$x]=chop(fgets($mi,512));
$x++;
}
fclose($mi);
}
$x=0;
if($ri){
while(!feof($ri)){
$relay_info[$x]=chop(fgets($ri,512));
$x++;
}
fclose($ri);
}
$x=0;
echo "CHANGE MASTER TO MASTER_HOST='$master_info[3]',MASTER_USER='$master_info[4]', MASTER_PASSWORD='$master_info[5]', MASTER_LOG_FILE='$relay_info[2]', MASTER_LOG_POS=$relay_info[3];";
exit(0);
?>
Add your own comment.