SHOW SLAVE STATUS Syntax

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: 13000
 Connect_Retry: 60
 Master_Log_File: master-bin.000001
 Read_Master_Log_Pos: 245
 Relay_Log_File: slave-relay-bin.000002
 Relay_Log_Pos: 399
 Relay_Master_Log_File: master-bin.000001
 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: 245
 Relay_Log_Space: 562
 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: a6c7893a-5335-11e1-96ca-c80aa9429562
 Master_Info_File: /home/jon/bin/mysql-trunk/mysql-test/var/mysqld.2/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Master_Retry_Count: 10
 Master_Bind:
 Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
 Master_SSL_Crl:
 Master_SSL_Crlpath:
1 row in set (0.00 sec)

The following list describes the fields returned by SHOW SLAVE STATUS. For additional information about interpreting their meanings, see , "Checking Replication Status".

• Slave_IO_State

  A copy of the State field of the SHOW 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 , "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 is No.
  • 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.13 and earlier); 5.0 (5.0.11 and earlier)	Yes
    (4.1.14 and later); 5.0 (5.0.12 and later)	No
    (5.1.45 and earlier); 5.4	No
    (5.1.46 and later); 5.5; 5.6	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 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.Note

  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_SQL_Errno even though Slave_SQL_Running still displays Yes.

• Skip_Counter

  The current value of the sql-slave-skip-counter system variable. See , "SET GLOBAL sql_slave_skip_counter Syntax".

• Exec_Master_Log_Pos

  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 TO statement's MASTER_LOG_POS option when starting a new slave from an existing slave, so that the new slave reads from this point. 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.

  When using a multi-threaded slave (by setting slave_parallel_workers to a nonzero value in MariaDB 5.6.3 and later), 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 slave than on the master, 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 slave is reading until a given position in the master's binary log
  • Relay if the slave is reading until a given position in its 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.

• 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 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_CRL, MASTER_SSL_CRLPATH, MASTER_SSL_KEY, and MASTER_SSL_VERIFY_SERVER_CERT options to the CHANGE MASTER TO statement. See , "CHANGE MASTER TO Syntax". Master_SSL_CRL_File and Master_SSL_CRL_Path were added in MariaDB 5.6.3.

• 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 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 Seconds_Behind_Master less reliable.

  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 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 given by the CHANGE MASTER TO statement or --master-connect-retry option (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_Master 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 MariaDB 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 multi-threaded slave (MySQL 5.6.3 and later), 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 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.

  Beginning with MariaDB 5.6.1, 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.

  Previous to MariaDB 5.6.3, this timestamp was prefixed to the error message text shown in the Last_IO_Error column. In MariaDB 5.6.3 and later, the timestamp appears instead in the Last_SQL_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 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.

  Beginning with MariaDB 5.6.1, SQL error information includes a timestamp showing when the most recent SQL thread error occurred. This timestamp uses the format YYMMDD HH:MM:SS.

  Previous to MariaDB 5.6.3, this timestamp was prefixed to the error message text shown in the Last_SQL_Error column. In MariaDB 5.6.3 and later, the timestamp appears instead 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 MariaDB 5.6, you can tell a slave to ignore events from 0 or more masters using the IGNORE_SERVER_IDS option in a CHANGE 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 a CHANGE MASTER TO statement 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
  

• Master_Server_Id

  The server_id value from the master.

• Master_UUID

  The server_uuid value from the master. This field was added in MariaDB 5.6.0.

• Master_Info_File

  The location of the master.info file. This field was added in MariaDB 5.6.0.

• SQL_Delay

  The number of seconds that the slave must lag the master. This field was added in MariaDB 5.6.0.

• SQL_Remaining_Delay

  When Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master executed event, this field contains the number of seconds left of the delay. At other times, this field is NULL. This field was added in MariaDB 5.6.0.

• 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. This field was added in MariaDB 5.6.0.

• Master_Retry_Count

  The number of times the slave can attempt to reconnect to the master in the event of a lost connection. This value can be set using the MASTER_RETRY_COUNT option in a CHANGE MASTER TO statement (preferred) or the older --master-retry-count server option (still supported for backward compatibility). This field was added in MariaDB 5.6.1.

• Master_Bind

  Shows the network interface that the client is bound to, if any, set using the MASTER_BIND option for the CHANGE MASTER TO statement.

  This column was added in MariaDB 5.6.2.

• Last_IO_Error_Timestamp

  A timestamp in YYMMDD HH:MM:SS format that shows when the most recent I/O error took place.

  This column was added in MariaDB 5.6.3. Previously in MariaDB 5.6, this timestamp was prepended to the error text shown in Last_IO_Error.

• Last_SQL_Error_Timestamp

  A timestamp in YYMMDD HH:MM:SS format that shows when the last SQL error occurred.

  This column was added in MariaDB 5.6.3. Previously in MariaDB 5.6, this timestamp was prepended to the error text shown in Last_SQL_Error.

Retornar