4.10.7.13 SHOW SLAVE STATUS (slave)

Provides status information on essential parameters of the slave threads (Slave). If you type it in the mysql client, you can put a \G instead of a semicolon at the end, to get a vertical, more readable layout:

SLAVE> show slave status\G
*************************** 1. row ***************************
          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
1 row in set (0.00 sec)

• 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 current value of master-connect-retry.
• Master_Log_File the master's binlog in which the I/O thread is currently reading.
• Read_Master_Log_Pos the position which the I/O thread has read up to in this master's binlog.
• Relay_Log_File the relay log which the SQL thread is currently reading and executing.
• Relay_Log_Pos the position which the SQL thread has read and executed up to in this relay log.
• Relay_Master_Log_File the master's binlog which contains the last event executed by the SQL thread.
• Slave_IO_Running tells whether the I/O thread is started or not.
• Slave_SQL_Running tells whether the SQL thread is started or not.
• Replicate_do_db / Replicate_ignore_db the lists of the databases which have been specified with option replicate-do-db / replicate-ignore-db; starting from version 4.1, options replicate_*_table are also displayed in four more columns.
• Last_errno the error number returned by the lastly executed query (should be 0).
• Last_error the error message returned by the lastly executed query (should be empty); if not empty, you will find this message in the slave's error log too. For example:

  Last_errno: 1051
  Last_error: error 'Unknown table 'z'' on query 'drop table z'
  

  Here the table 'z' existed on the master and was dropped there, but it did not exist on the slave (the user had forgotten to copy it to the slave when setting the slave up), so DROP TABLE failed on the slave.
• Skip_counter the last used value for SQL_SLAVE_SKIP_COUNTER.
• Exec_master_log_pos the position in the master's binlog (Relay_Master_Log_File) of the last event executed by the SQL thread. ((Relay_Master_Log_File,Exec_master_log_pos) in the master's binlog corresponds to (Relay_Log_File,Relay_Log_Pos) in the relay log).
• Relay_log_space the total size of all existing relay logs.

User Comments

NOTE on MySQL 4.0.14: In cyclical replication, with log-slave-updates = ON, Exec_master_log_pos tends report spurious values. It will often return a correct value, but almost as often return the current 'Pos' value of its own master log file.

It will return the incorrect value when it sees a BINLOG event in its MASTER that has its own server-id. It silently ignores the binlog event, but still stores the binlog event's Original_log_pos as the Exec_master_log_pos.

Just to warn those who might rely on this value in subsequent CHANGE MASTER commands, it cannot be trusted in this sort of setup.

Add your own comment.