Syntax

SHOW SLAVE ["connection_name"] STATUS

or

SHOW ALL SLAVES STATUS

Description

This statement provides status information on essential parameters of the replication slave threads.

This statement requires the SUPER or the REPLICATION_CLIENT privilege.

Multi-source

MariaDB 10.0 introduced the FULL and "connection_name" options to allow you to connect to many masters at the same time.

ALL SLAVES gives you a list of all connections to the master.

The rows will be sorted according to Connection_name.

If you specify a connection_name, you only get the information about that connection. If connection_name is not used, then the name set by default_master_connection is used. If the connection name doesn't exist you will get an error: There is no master connection for 'xxx'.

Column descriptions

NameDescriptionAdded
Connection_nameName of the master connection. Returned with SHOW ALL SLAVES STATUS only.MariaDB 10.0
Slave_SQL_StateState of SQL thread. Returned with SHOW ALL SLAVES STATUS only.MariaDB 10.0
Master_hostMaster host that the slave is connected to.
Master_userAccount user name being used to connect to the master.
Master_portThe port being used to connect to the master.
Connect_RetryTime in seconds between retries to connect. The default is 60. The CHANGE MASTER TO statement can set this. The master_retry_count system variable determines the maximum number of reconnection attempts.
Master_Log_FileName of the master binary log file that the I/O thread is currently reading from.
Read_Master_Log_PosPosition up to which the I/O thread has read in the current master binary log file.
Relay_Log_FileName of the relay log file that the SQL thread is currently processing.
Relay_Log_PosPosition up to which the SQL thread has finished processing in the current relay log file.
Relay_Master_Log_FileName of the master binary log file that contains the most recent event executed by the SQL thread.
Slave_IO_RunningWhether the slave I/O thread is running and connected (Yes), running but not connected to a master (Connecting) or not running (No).
Slave_SQL_RunningWhether or not the SQL thread is running.
Replicate_Do_DBDatabases specified for replicating with the replicate_do_db option.
Replicate_Ignore_DBDatabases specified for ignoring with the replicate_ignore_db option.
Replicate_Do_TableTables specified for replicating with the replicate_do_table option.
Replicate_Ignore_TableTables specified for ignoring with the replicate_ignore_table option.
Replicate_Wild_Do_TableTables specified for replicating with the replicate_wild_do_table option.
Replicate_Wild_Ignore_TableTables specified for ignoring with the replicate_wild_ignore_table option.
Last_ErrnoAlias for Last_SQL_Errno (see below)
Last ErrorAlias for Last_SQL_Error (see below)
Skip_CounterNumber of events that a slave skips from the master, as recorded in the sql_slave_skip_counter system variable.
Exec_Master_Log_PosPosition up to which the SQL thread has processed in the current master binary log file. Can be used to start a new slave from a current slave with the CHANGE MASTER TO ... MASTER_LOG_POS option.
Relay_Log_SpaceTotal size of all relay log files combined.
Until_Condition
Until_Log_FileThe MASTER_LOG_FILE value of the START SLAVE UNTIL condition.
Until_Log_PosThe MASTER_LOG_POS value of the START SLAVE UNTIL condition.
Master_SSL_AllowedWhether an SSL connection is permitted (Yes), not permitted (No) or permitted but without the slave having SSL support enabled (Ignored)
Master_SSL_CA_FileThe MASTER_SSL_CA option of the CHANGE MASTER TO statement.
Master_SSL_CA_PathThe MASTER_SSL_CAPATH option of the CHANGE MASTER TO statement.
Master_SSL_CertThe MASTER_SSL_CERT option of the CHANGE MASTER TO statement.
Master_SSL_CipherThe MASTER_SSL_CIPHER option of the CHANGE MASTER TO statement.
Master_SSL_KeyThe MASTER_SSL_KEY option of the CHANGE MASTER TO statement.
Seconds_Behind_MasterDifference between the timestamp logged on the master for the event that the slave is currently processing, and the current timestamp on the slave. Zero if the slave is not currently processing an event. From MariaDB 10.0.23 and MariaDB 10.1.9, with parallel replication, seconds_behind_master is updated only after transactions commit.
Master_SSL_Verify_Server_CertThe MASTER_SSL_VERIFY_SERVER_CERT option of the CHANGE MASTER TO statement.
Last_IO_ErrnoError code of the most recent error that caused the I/O thread to stop (also recorded in the slave's error log). 0 means no error. RESET SLAVE or RESET MASTER will reset this value.
Last_IO_ErrorError message of the most recent error that caused the I/O thread to stop (also recorded in the slave's error log). An empty string means no error. RESET SLAVE or RESET MASTER will reset this value.
Last_SQL_ErrnoError code of the most recent error that caused the SQL thread to stop (also recorded in the slave's error log). 0 means no error. RESET SLAVE or RESET MASTER will reset this value.
Last_SQL_ErrorError message of the most recent error that caused the SQL thread to stop (also recorded in the slave's error log). An empty string means no error. RESET SLAVE or RESET MASTER will reset this value.
Replicate_Ignore_Server_IdsList of server_ids that are currently being ignored for replication purposes, or an empty string for none, as specified in the IGNORE_SERVER_IDS option of the CHANGE MASTER TO statement.
Master_Server_IdThe master's server_id value.
Master_SSL_CrlThe MASTER_SSL_CRL option of the CHANGE MASTER TO statement.MariaDB 10.0
Master_SSL_CrlpathThe MASTER_SSL_CRLPATH option of the CHANGE MASTER TO statement.MariaDB 10.0
Using_GtidWhether or not global transaction ID's are being used for replication (either No or Slave_Pos).MariaDB 10.0.2
Gtid_IO_PosCurrent global transaction ID value.MariaDB 10.0.2
Retried_transactionsNumber of retried transactions for this connection. Returned with SHOW ALL SLAVES STATUS only.MariaDB 10.0
Max_relay_log_sizeMax relay log size for this connection. Returned with SHOW ALL SLAVES STATUS only.MariaDB 10.0
Executed_log_entriesHow many log entries the slave has executed. Returned with SHOW ALL SLAVES STATUS only.MariaDB 10.0
Slave_received_heartbeatsHow many heartbeats we have got from the master. Returned with SHOW ALL SLAVES STATUS only.MariaDB 10.0
Slave_heartbeat_periodHow often to request a heartbeat packet from the master (in seconds). Returned with SHOW ALL SLAVES STATUS only.MariaDB 10.0
Gtid_Slave_PosGTID of the last event group replicated on a slave server, for each replication domain, as stored in the gtid_slave_pos system variable. Returned with SHOW ALL SLAVES STATUS only.MariaDB 10.0
SQL_DelayValue specified by MASTER_DELAY in CHANGE MASTER (or 0 if none).MariaDB 10.2.3
SQL_Remaining_DelayWhen the slave is delaying the execution of an event due to MASTER_DELAY, this is the number of seconds of delay remaining before the event will be applied. Otherwise, the value is NULL.MariaDB 10.2.3
Slave_SQL_Running_StateThe state of the SQL driver threads, same as in SHOW PROCESSLIST. When the slave is delaying the execution of an event due to MASTER_DELAY, this field displays: "Waiting until MASTER_DELAY seconds after master executed event".MariaDB 10.2.3

Examples

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.

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: db01.example.com
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000010
          Read_Master_Log_Pos: 548
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 837
        Relay_Master_Log_File: mariadb-bin.000010
             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: 548
              Relay_Log_Space: 1497
              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: 101
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
MariaDB [(none)]> SHOW ALL SLAVES STATUS\G
*************************** 1. row ***************************
              Connection_name: 
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: db01.example.com
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000010
          Read_Master_Log_Pos: 3608
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 3897
        Relay_Master_Log_File: mariadb-bin.000010
             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: 3608
              Relay_Log_Space: 4557
              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: 101
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos:
         Retried_transactions: 0
           Max_relay_log_size: 104857600
         Executed_log_entries: 40
    Slave_received_heartbeats: 11
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 0-101-2320

You can also access some of the variables directly from status variables:

SET @@default_master_connection="test" ;
show status like "%slave%"

Variable_name   Value
Com_show_slave_hosts    0
Com_show_slave_status   0
Com_start_all_slaves    0
Com_start_slave 0
Com_stop_all_slaves     0
Com_stop_slave  0
Rpl_semi_sync_slave_status      OFF
Slave_connections       0
Slave_heartbeat_period  1800.000
Slave_open_temp_tables  0
Slave_received_heartbeats       0
Slave_retried_transactions      0
Slave_running   OFF
Slaves_connected        0
Slaves_running  1

Comments

Comments loading...