Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.0Mb
PDF (A4) - 35.1Mb
PDF (RPM) - 34.1Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 200.1Kb
Man Pages (Zip) - 305.3Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

14.7.5.34 SHOW SLAVE STATUS Syntax

SHOW SLAVE STATUS [NONBLOCKING # Removed in MySQL 5.7.6] 
                  [FOR CHANNEL channel]

This statement provides status information on essential parameters of the slave threads. It requires either the SUPER or REPLICATION CLIENT privilege.

Between MySQL 5.7.1 and MySQL 5.7.5, an optional NONBLOCKING clause could be used. The NONBLOCKING clause caused SHOW SLAVE STATUS, when run concurrently with STOP SLAVE, to return without waiting for STOP SLAVE to finish shutting down the slave SQL thread or slave I/O thread (or both). This option was intended for use in monitoring and other applications where getting an immediate response from SHOW SLAVE STATUS was more important than ensuring that it returned the latest data. This option was removed in MySQL 5.7.6 due to locking changes in replication administrative statements. As of MySQL 5.7.9 the non-blocking behavior of SHOW SLAVE STATUS is fixed and the option became unnecessary.

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.000002
          Read_Master_Log_Pos: 1307
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 1508
        Relay_Master_Log_File: master-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: 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:
           Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
            Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_name: 
1 row in set (0.00 sec)

As of MySQL 5.7.2, the Performance Schema provides tables that expose replication information. This is similar to the information available from the SHOW SLAVE STATUS statement, but represented in table form. For details, see Section 23.9.11, “Performance Schema Replication Tables”.

The following list describes the fields returned by SHOW SLAVE STATUS. For additional information about interpreting their meanings, see Section 18.1.7.1, “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. For a listing of possible states, see Section 9.14.5, “Replication Slave I/O Thread States”.

  • 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 VersionSlave_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)No
      5.1 (5.1.46 and later); 5.5; 5.6Connecting
    • 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 Section 14.4.2.5, “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.

    Inconsistencies in the sequence of transactions from the relay log which have been executed can cause this value to be a low-water mark. In other words, transactions appearing before the position are guaranteed to have committed, but transactions after the position may have committed or not. If these gaps need to be corrected, use START SLAVE UNTIL SQL_AFTER_MTS_GAPS. See Section 18.4.1.34, “Replication and Transaction Inconsistencies” for more information.

  • 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

    • SQL_BEFORE_GTIDS if the slave 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 slave 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 multi-threaded slave'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 14.4.2.6, “START SLAVE Syntax”.

  • 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 Section 14.4.2.1, “CHANGE MASTER TO Syntax”.

  • Seconds_Behind_Master

    This field is an indication of how late the slave is:

    • When the slave is actively processing updates, this field shows the difference between the current timestamp on the slave and the original timestamp logged on the master for the event currently being processed on the slave.

    • When no event is currently being processed on the slave, this value is 0.

    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.

    In MySQL 5.7, this field is NULL (undefined or unknown) if the slave SQL thread is not running, or if the SQL thread has consumed all of the relay log and the slave I/O thread is not running. (In older versions of MySQL, this field was NULL if the slave SQL thread or the slave I/O thread was not running or was not connected to the master.) 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 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.

    When using a multi-threaded slave, 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 slave'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_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 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 slave's error log.

    If the slave is multi-threaded, the SQL thread is the coordinator for worker threads. In this case, as of MySQL 5.7.2, the Last_SQL_Error field shows exactly what the Last_Error_Message column in the Performance Schema replication_applier_status_by_coordinator table shows. The field value is modified to suggest that there may be more failures in the other worker threads which can be seen in the replication_applier_status_by_worker table that shows each worker thread's status. If that table is not available, the slave error log can be used. The log or the replication_applier_status_by_worker table should also be used to learn more about the failure shown by SHOW SLAVE STATUS or the coordinator table.

    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.

    In MySQL 5.7, all error codes and messages displayed in the Last_SQL_Errno and Last_SQL_Error columns correspond to error values listed in Section B.3, “Server Error Codes and Messages”. This was not always true in previous versions. (Bug #11760365, Bug #52768)

  • Replicate_Ignore_Server_Ids

    In MySQL 5.7, you set a slave to ignore events from 0 or more masters 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-master 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
    
    Note

    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 slave to ignore masters having the server ID 2, 6, or 9, that information appears as shown here:

    	Ignored_server_ids: 3, 2, 6, 9
    

    The first number (in this case 3) shows the 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 master.

  • Master_UUID

    The server_uuid value from the master.

  • Master_Info_File

    The location of the master.info file.

  • SQL_Delay

    The number of seconds that the slave must lag the master.

  • 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 9.14.6, “Replication Slave SQL Thread States”, provides a listing of possible states

  • 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 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 slave 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 last SQL error occurred.

  • Retrieved_Gtid_Set

    The set of global transaction IDs corresponding to all transactions received by this slave. Empty if GTIDs are not in use. See GTID Sets for more information.

    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.

    Prior to MySQL 5.7.1, this value was printed using uppercase. In MySQL 5.7.1 and later, it is always printed using lowercase. (Bug #15869441)

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

    Prior to MySQL 5.7.1, this value was printed using uppercase. In MySQL 5.7.1 and later, it is always printed using lowercase. (Bug #15869441)

  • Auto_Position

    1 if autopositioning is in use; otherwise 0.

    This column was added in MySQL 5.7.1. (Bug #15992220)

  • Replicate_Rewrite_DB

    Beginning with MySQL 5.7.3, the Replicate_Rewrite_DB value displays any replication filtering rules that were specified. For example, if the following replication filter rule was set:

    CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((db1,db2), (db3,db4));

    the Replicate_Rewrite_DB value displays:

    Replicate_Rewrite_DB: (db1,db2),(db3,db4)

    For more information, see Section 14.4.2.2, “CHANGE REPLICATION FILTER Syntax”.

  • Channel_name

    The replication channel which is being displayed. There is always a default replication channel, and more replication channels can be added. See Section 18.2.3, “Replication Channels” for more information.


User Comments
  Posted by Alexey Zilber on August 16, 2011
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);
?>

Sign Up Login You must be logged in to post a comment.