Skip navigation links
12.4.5.22 SHOW STATUS Syntax »
Section Navigation      [Toggle]

12.4.5.21. SHOW SLAVE STATUS Syntax

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

Depending on your version of MySQL, you may not see all the fields just shown. In particular, several fields are present only as of MySQL 4.1.1.

SHOW SLAVE STATUS returns the following fields:

  • 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 Section 14.3, “Replication Implementation Details”. For versions of MySQL prior to 4.1.14, it is necessary to check this field for connection problems. In those versions, the thread could be running while unsuccessfully trying to connect to the master; only this field makes you aware of the problem. The state of the SQL thread is not copied because it is simpler. If it is running, there is no problem; if it is not, you can find the error in the Last_Error field (described later).

    This field is present beginning with MySQL 4.1.1.

  • 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 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 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 (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.4 No
      5.5 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.

  • 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.

    These fields are present beginning with MySQL 4.1.1.

  • 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.

    These fields are present beginning with MySQL 4.1.1.

  • Last_Errno, Last_Error

    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. For example: 

    Last_Errno: 1051
Last_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.)

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

  • Skip_Counter

    The current value of the sql_slave_skip_counter system variable. See Section 12.5.2.6, “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 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.

    These fields are present beginning with MySQL 4.1.1.

  • 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, and MASTER_SSL_KEY options to the CHANGE MASTER TO statement. See Section 12.5.2.1, “CHANGE MASTER TO Syntax”.

    These fields are present beginning with MySQL 4.1.1.

  • Seconds_Behind_Master

    This field is present beginning with MySQL 4.1.1. It is been experimental and has been changed in MySQL 4.1.9. The following applies to slaves running MySQL 4.1.9 or newer. 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 is NULL (“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 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 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.


User Comments

Posted by Johannes Ullrich on October 31 2006 1:53pm[Delete] [Edit]

Between different versions of mysql, the capitalization of the column names chagned. Careful if you write your own scripts around this.

Posted by Rachel McConnell on September 10 2009 7:21pm[Delete] [Edit]

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.

Posted by Alexey Zilber on October 6 2009 11:35pm[Delete] [Edit]

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.