MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
SHOW SLAVE STATUS
This statement provides status information on essential
parameters of the replica 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: 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”.
Slave_IO_State
A copy of the State
field of the
SHOW PROCESSLIST
output 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”.
Master_Host
The source host that the replica is connected to.
Master_User
The user name of the account used to connect to the source.
Master_Port
The port used to connect to the source.
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 source binary log file from which the I/O thread is currently reading.
Read_Master_Log_Pos
The position in the current source 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 source 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 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,
Slave_IO_Running
is
No
.
MYSQL_SLAVE_RUN_NOT_CONNECT.
The replica I/O thread is running, but is not
connected to a replication source. For this state,
Slave_IO_Running
is
Connecting
.
MYSQL_SLAVE_RUN_CONNECT.
The replica I/O thread is running, and is connected to
a replication source. For this state,
Slave_IO_Running
is
Yes
.
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
and
Last_SQL_Error
.
Issuing RESET MASTER
or
RESET SLAVE
resets the values
shown in these columns.
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 STATUS
shows a
nonzero value for Last_SQL_Errno
even
though Slave_SQL_Running
still displays
Yes
.
Skip_Counter
The current value of the
sql_slave_skip_counter
system variable. See
Section 13.4.2.4, “SET GLOBAL sql_slave_skip_counter Statement”.
Exec_Master_Log_Pos
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
TO
statement's
MASTER_LOG_POS
option when starting a new
replica from an existing replica, so that the new replica
reads from this point. The coordinates given by
(Relay_Master_Log_File
,
Exec_Master_Log_Pos
) in the source's
binary log correspond to the coordinates given by
(Relay_Log_File
,
Relay_Log_Pos
) in the relay log.
When using a multithreaded replica (by setting
slave_parallel_workers
to 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.
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 the START SLAVE
statement.
Until_Condition
has these values:
None
if no UNTIL
clause was specified
Master
if the replica is reading
until a given position in the source's binary log
Relay
if the replica is reading until
a given position in its relay log
SQL_BEFORE_GTIDS
if the replica SQL
thread is processing transactions until it has reached
the first transaction whose GTID is listed in the
gtid_set
.
SQL_AFTER_GTIDS
if the replica
threads are processing all transactions until the last
transaction in the gtid_set
has been
processed by both threads.
SQL_AFTER_MTS_GAPS
if a multithreaded
replica's SQL threads are running until no more gaps are
found in the relay log.
Until_Log_File
and
Until_Log_Pos
indicate the log file name
and position that define the coordinates at which the SQL
thread stops executing.
For more information on UNTIL
clauses,
see Section 13.4.2.5, “START SLAVE Statement”.
Master_SSL_Allowed
,
Master_SSL_CA_File
,
Master_SSL_CA_Path
,
Master_SSL_Cert
,
Master_SSL_Cipher
,
Master_SSL_CRL_File
,
Master_SSL_CRL_Path
,
Master_SSL_Key
,
Master_SSL_Verify_Server_Cert
These fields show the SSL parameters used by the replica to connect to the source, if any.
Master_SSL_Allowed
has these values:
Yes
if an SSL connection to the
source is permitted
No
if an SSL connection to the source
is not permitted
Ignored
if 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_CA
,
MASTER_SSL_CAPATH
,
MASTER_SSL_CERT
,
MASTER_SSL_CIPHER
,
MASTER_SSL_CRL
,
MASTER_SSL_CRLPATH
,
MASTER_SSL_KEY
, and
MASTER_SSL_VERIFY_SERVER_CERT
options to
the CHANGE MASTER TO
statement. See Section 13.4.2.1, “CHANGE MASTER TO Statement”.
Seconds_Behind_Master
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_Master
often 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 Seconds_Behind_Master
less
reliable.
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
NULL
if 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
TO
statement or
--master-connect-retry
option
(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_Master
is set to 0.
The value of Seconds_Behind_Master
is
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
TIMESTAMP
successfully.
However, the problem for
Seconds_Behind_Master
is that if M1 also
receives direct updates from clients, the
Seconds_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.
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.
Last_IO_Errno
,
Last_IO_Error
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_Error
value 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 Last_IO_Error_Timestamp
column.
Issuing RESET MASTER
or
RESET SLAVE
resets the values
shown in these columns.
Last_SQL_Errno
,
Last_SQL_Error
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_Error
value 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 Last_SQL_Error_Timestamp
column.
Issuing RESET MASTER
or
RESET SLAVE
resets the values
shown in these columns.
Replicate_Ignore_Server_Ids
In MySQL 5.6, you set a replica to ignore
events from 0 or more sources using the
IGNORE_SERVER_IDS
option of the
CHANGE MASTER TO
statement.
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_Ids
when 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, 9
Ignored_server_ids
also 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 TO
statement 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
where 3
is the total number of server
IDs being ignored.
Replicate_Ignore_Server_Ids
filtering 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.
Master_Server_Id
The server_id
value from
the source.
Master_UUID
The server_uuid
value from
the source.
Master_Info_File
The location of the master.info
file.
SQL_Delay
The number of seconds that the replica must lag the source.
SQL_Remaining_Delay
When Slave_SQL_Running_State
is
Waiting until MASTER_DELAY seconds after master
executed event
, this field contains the number of
delay seconds remaining. At other times, this field is
NULL
.
Slave_SQL_Running_State
The state of the SQL thread (analogous to
Slave_IO_State
). The value is identical
to the State
value of the SQL thread as
displayed by SHOW
PROCESSLIST
;
Section 8.14.8, “Replication Replica SQL Thread States”, provides a
listing of possible states.
Master_Retry_Count
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_COUNT
option of the CHANGE MASTER
TO
statement (preferred) or the older
--master-retry-count
server
option (still supported for backward compatibility).
Master_Bind
The network interface that the replica is bound to, if any.
This is set using the MASTER_BIND
option
for the CHANGE MASTER TO
statement.
Last_IO_Error_Timestamp
A timestamp in YYMMDD hh:mm:ss
format that shows when the most recent I/O error took place.
Last_SQL_Error_Timestamp
A timestamp in YYMMDD hh:mm:ss
format that shows when the most recent SQL error occurred.
Retrieved_Gtid_Set
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_event
is received. This can
cause partially transmitted transactions to have their GTIDs
included in the set.
When all relay logs are lost due to executing
RESET SLAVE
or
CHANGE MASTER TO
, or due to
the effects of the
--relay-log-recovery
option,
the set is cleared. When
relay_log_purge = 1
, the
newest relay log is always kept, and the set is not cleared.
Executed_Gtid_Set
The set of global transaction IDs written in the binary log.
This is the same as the value for the global
gtid_executed
system
variable on this server, as well as the value for
Executed_Gtid_Set
in the output of
SHOW MASTER STATUS
on this
server. Empty if GTIDs are not in use. See
GTID Sets for
more information.
Auto_Position
1 if autopositioning is in use; otherwise 0.