Syntax
------

SHOW SLAVE ["connection_name"] STATUS [FOR CHANNEL "connection_name"]
SHOW REPLICA ["connection_name"] STATUS -- From MariaDB 10.5.1

or

SHOW ALL SLAVES STATUS
SHOW ALL REPLICAS STATUS -- From MariaDB 10.5.1

Description
-----------

This statement is to be run on a replica and provides status information on
essential parameters of the replica threads.

This statement requires the SUPER privilege, the REPLICATION_CLIENT privilege,
or, from MariaDB 10.5.2, the REPLICATION SLAVE ADMIN privilege, or, from
MariaDB 10.5.9, the REPLICA MONITOR privilege.

Multi-Source
------------

The ALL and "connection_name" options allow you to connect to many primaries
at the same time.

ALL SLAVES (or ALL REPLICAS from MariaDB 10.5.1) gives you a list of all
connections to the primary nodes.

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

MariaDB starting with 10.7.0
----------------------------
The FOR CHANNEL keyword was added for MySQL compatibility. This is identical
as using the channel_name directly after SHOW SLAVE.

Column Descriptions
-------------------

+---------------+---------------------------------------+-------------------+
| Name          | Description                           | Added             |
+---------------+---------------------------------------+-------------------+
| Connection_na | Name of the primary connection.       |                   |
| e             | Returned with SHOW ALL SLAVES STATUS  |                   |
|               | (or SHOW ALL REPLICAS STATUS from     |                   |
|               | MariaDB 10.5.1) only.                 |                   |
+---------------+---------------------------------------+-------------------+
| Slave_SQL_Sta | State of SQL thread. Returned with    |                   |
| e             | SHOW ALL SLAVES STATUS (or SHOW ALL   |                   |
|               | REPLICAS STATUS from MariaDB 10.5.1)  |                   |
|               | only. See Slave SQL Thread States.    |                   |
+---------------+---------------------------------------+-------------------+
| Slave_IO_Stat | State of I/O thread. See Slave I/O    |                   |
|               | Thread States.                        |                   |
+---------------+---------------------------------------+-------------------+
| Master_host   | Master host that the replica is       |                   |
|               | connected to.                         |                   |
+---------------+---------------------------------------+-------------------+
| Master_user   | Account user name being used to       |                   |
|               | connect to the primary.               |                   |
+---------------+---------------------------------------+-------------------+
| Master_port   | The port being used to connect to     |                   |
|               | the primary.                          |                   |
+---------------+---------------------------------------+-------------------+
| Connect_Retry | Time in seconds between retries to    |                   |
|               | connect. The default is 60. The       |                   |
|               | CHANGE MASTER TO statement can set    |                   |
|               | this. The master-retry-count option   |                   |
|               | determines the maximum number of      |                   |
|               | reconnection attempts.                |                   |
+---------------+---------------------------------------+-------------------+
| Master_Log_Fi | Name of the primary binary log file   |                   |
| e             | that the I/O thread is currently      |                   |
|               | reading from.                         |                   |
+---------------+---------------------------------------+-------------------+
| Read_Master_L | Position up to which the I/O thread   |                   |
| g_Pos         | has read in the current primary       |                   |
|               | binary log file.                      |                   |
+---------------+---------------------------------------+-------------------+
| Relay_Log_Fil | Name of the relay log file that the   |                   |
|               | SQL thread is currently processing.   |                   |
+---------------+---------------------------------------+-------------------+
| Relay_Log_Pos | Position up to which the SQL thread   |                   |
|               | has finished processing in the        |                   |
|               | current relay log file.               |                   |
+---------------+---------------------------------------+-------------------+
| Relay_Master_ | Name of the primary binary log file   |                   |
| og_File       | that contains the most recent event   |                   |
|               | executed by the SQL thread.           |                   |
+---------------+---------------------------------------+-------------------+
| Slave_IO_Runn | Whether the replica I/O thread is     |                   |
| ng            | running and connected (Yes), running  |                   |
|               | but not connected to a primary        |                   |
|               | (Connecting) or not running (No).     |                   |
+---------------+---------------------------------------+-------------------+
| Slave_SQL_Run | Whether or not the SQL thread is      |                   |
| ing           | running.                              |                   |
+---------------+---------------------------------------+-------------------+
| Replicate_Do_ | Databases specified for replicating   |                   |
| B             | with the replicate_do_db option.      |                   |
+---------------+---------------------------------------+-------------------+
| Replicate_Ign | Databases specified for ignoring      |                   |
| re_DB         | with the replicate_ignore_db option.  |                   |
+---------------+---------------------------------------+-------------------+
| Replicate_Do_ | Tables specified for replicating      |                   |
| able          | with the replicate_do_table option.   |                   |
+---------------+---------------------------------------+-------------------+
| Replicate_Ign | Tables specified for ignoring with    |                   |
| re_Table      | the replicate_ignore_table option.    |                   |
+---------------+---------------------------------------+-------------------+
| Replicate_Wil | Tables specified for replicating      |                   |
| _Do_Table     | with the replicate_wild_do_table      |                   |
|               | option.                               |                   |
+---------------+---------------------------------------+-------------------+
| Replicate_Wil | Tables specified for ignoring with    |                   |
| _Ignore_Table | the replicate_wild_ignore_table       |                   |
|               | option.                               |                   |
+---------------+---------------------------------------+-------------------+
| Last_Errno    | Alias for Last_SQL_Errno (see below)  |                   |
+---------------+---------------------------------------+-------------------+
| Last Error    | Alias for Last_SQL_Error (see below)  |                   |
+---------------+---------------------------------------+-------------------+
| Skip_Counter  | Number of events that a replica       |                   |
|               | skips from the master, as recorded    |                   |
|               | in the sql_slave_skip_counter system  |                   |
|               | variable.                             |                   |
+---------------+---------------------------------------+-------------------+
| Exec_Master_L | Position up to which the SQL thread   |                   |
| g_Pos         | has processed in the current master   |                   |
|               | binary log file. Can be used to       |                   |
|               | start a new replica from a current    |                   |
|               | replica with the CHANGE MASTER TO     |                   |
|               | ... MASTER_LOG_POS option.            |                   |
+---------------+---------------------------------------+-------------------+
| Relay_Log_Spa | Total size of all relay log files     |                   |
| e             | combined.                             |                   |
+---------------+---------------------------------------+-------------------+
| Until_Conditi |                                       |                   |
| n             |                                       |                   |
+---------------+---------------------------------------+-------------------+
| Until_Log_Fil | The MASTER_LOG_FILE value of the      |                   |
|               | START SLAVE UNTIL condition.          |                   |
+---------------+---------------------------------------+-------------------+
| Until_Log_Pos | The MASTER_LOG_POS value of the       |                   |
|               | START SLAVE UNTIL condition.          |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_Al | Whether an SSL connection is          |                   |
| owed          | permitted (Yes), not permitted (No)   |                   |
|               | or permitted but without the replica  |                   |
|               | having SSL support enabled (Ignored)  |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_CA | The MASTER_SSL_CA option of the       |                   |
| File          | CHANGE MASTER TO statement.           |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_CA | The MASTER_SSL_CAPATH option of the   |                   |
| Path          | CHANGE MASTER TO statement.           |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_Ce | The MASTER_SSL_CERT option of the     |                   |
| t             | CHANGE MASTER TO statement.           |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_Ci | The MASTER_SSL_CIPHER option of the   |                   |
| her           | CHANGE MASTER TO statement.           |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_Ke | The MASTER_SSL_KEY option of the      |                   |
|               | CHANGE MASTER TO statement.           |                   |
+---------------+---------------------------------------+-------------------+
| Seconds_Behin | Difference between the timestamp      |                   |
| _Master       | logged on the master for the event    |                   |
|               | that the replica is currently         |                   |
|               | processing, and the current           |                   |
|               | timestamp on the replica. Zero if     |                   |
|               | the replica is not currently          |                   |
|               | processing an event. With parallel    |                   |
|               | replication, seconds_behind_master    |                   |
|               | is updated only after transactions    |                   |
|               | commit.                               |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_Ve | The MASTER_SSL_VERIFY_SERVER_CERT     |                   |
| ify_Server_Ce | option of the CHANGE MASTER TO        |                   |
| t             | statement.                            |                   |
+---------------+---------------------------------------+-------------------+
| Last_IO_Errno | Error code of the most recent error   |                   |
|               | that caused the I/O thread to stop    |                   |
|               | (also recorded in the replica's       |                   |
|               | error log). 0 means no error. RESET   |                   |
|               | SLAVE or RESET MASTER will reset      |                   |
|               | this value.                           |                   |
+---------------+---------------------------------------+-------------------+
| Last_IO_Error | Error message of the most recent      |                   |
|               | error that caused the I/O thread to   |                   |
|               | stop (also recorded in the replica's  |                   |
|               | error log). An empty string means no  |                   |
|               | error. RESET SLAVE or RESET MASTER    |                   |
|               | will reset this value.                |                   |
+---------------+---------------------------------------+-------------------+
| Last_SQL_Errn | Error code of the most recent error   |                   |
|               | that caused the SQL thread to stop    |                   |
|               | (also recorded in the replica's       |                   |
|               | error log). 0 means no error. RESET   |                   |
|               | SLAVE or RESET MASTER will reset      |                   |
|               | this value.                           |                   |
+---------------+---------------------------------------+-------------------+
| Last_SQL_Erro | Error message of the most recent      |                   |
|               | error that caused the SQL thread to   |                   |
|               | stop (also recorded in the replica's  |                   |
|               | error log). An empty string means no  |                   |
|               | error. RESET SLAVE or RESET MASTER    |                   |
|               | will reset this value.                |                   |
+---------------+---------------------------------------+-------------------+
| Replicate_Ign | List of server_ids that are           |                   |
| re_Server_Ids | 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 | The master's server_id value.         |                   |
| Id            |                                       |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_Cr | The MASTER_SSL_CRL option of the      |                   |
|               | CHANGE MASTER TO statement.           |                   |
+---------------+---------------------------------------+-------------------+
| Master_SSL_Cr | The MASTER_SSL_CRLPATH option of the  |                   |
| path          | CHANGE MASTER TO statement.           |                   |
+---------------+---------------------------------------+-------------------+
| Using_Gtid    | Whether or not global transaction     |                   |
|               | ID's are being used for replication   |                   |
|               | (can be No, Slave_Pos, or             |                   |
|               | Current_Pos).                         |                   |
+---------------+---------------------------------------+-------------------+
| Gtid_IO_Pos   | Current global transaction ID value.  |                   |
+---------------+---------------------------------------+-------------------+
| Retried_trans | Number of retried transactions for    |                   |
| ctions        | this connection. Returned with SHOW   |                   |
|               | ALL SLAVES STATUS only.               |                   |
+---------------+---------------------------------------+-------------------+
| Max_relay_log | Max relay log size for this           |                   |
| size          | connection. Returned with SHOW ALL    |                   |
|               | SLAVES STATUS only.                   |                   |
+---------------+---------------------------------------+-------------------+
| Executed_log_ | How many log entries the replica has  |                   |
| ntries        | executed. Returned with SHOW ALL      |                   |
|               | SLAVES STATUS only.                   |                   |
+---------------+---------------------------------------+-------------------+
| Slave_receive | How many heartbeats we have got from  |                   |
| _heartbeats   | the master. Returned with SHOW ALL    |                   |
|               | SLAVES STATUS only.                   |                   |
+---------------+---------------------------------------+-------------------+
| Slave_heartbe | How often to request a heartbeat      |                   |
| t_period      | packet from the master (in seconds).  |                   |
|               | Returned with SHOW ALL SLAVES STATUS  |                   |
|               | only.                                 |                   |
+---------------+---------------------------------------+-------------------+
| Gtid_Slave_Po | GTID of the last event group          |                   |
|               | replicated on a replica server, for   |                   |
|               | each replication domain, as stored    |                   |
|               | in the gtid_slave_pos system          |                   |
|               | variable. Returned with SHOW ALL      |                   |
|               | SLAVES STATUS only.                   |                   |
+---------------+---------------------------------------+-------------------+
| SQL_Delay     | Value specified by MASTER_DELAY in    | MariaDB 10.2.3    |
|               | CHANGE MASTER (or 0 if none).         |                   |
+---------------+---------------------------------------+-------------------+
| SQL_Remaining | When the replica is delaying the      | MariaDB 10.2.3    |
| Delay         | 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.         |                   |
+---------------+---------------------------------------+-------------------+
| Slave_SQL_Run | The state of the SQL driver threads,  | MariaDB 10.2.3    |
| ing_State     | same as in SHOW PROCESSLIST. When     |                   |
|               | the replica is delaying the           |                   |
|               | execution of an event due to          |                   |
|               | MASTER_DELAY, this field displays:    |                   |
|               | "Waiting until MASTER_DELAY seconds   |                   |
|               | after master executed event".         |                   |
+---------------+---------------------------------------+-------------------+
| Slave_DDL_Gro | This status variable counts the       | MariaDB 10.3.7    |
| ps            | occurrence of DDL statements.  This   |                   |
|               | is a replica-side counter for         |                   |
|               | optimistic parallel replication.      |                   |
+---------------+---------------------------------------+-------------------+
| Slave_Non_Tra | This status variable counts the       | MariaDB 10.3.7    |
| sactional_Gro | occurrence of non-transactional       |                   |
| ps            | event groups.  This is a              |                   |
|               | replica-side counter for optimistic   |                   |
|               | parallel replication.                 |                   |
+---------------+---------------------------------------+-------------------+
| Slave_Transac | This status variable counts the       | MariaDB 10.3.7    |
| ional_Groups  | occurrence of transactional event     |                   |
|               | groups.  This is a replica-side       |                   |
|               | counter for optimistic parallel       |                   |
|               | replication.                          |                   |
+---------------+---------------------------------------+-------------------+

SHOW REPLICA STATUS
-------------------

MariaDB starting with 10.5.1
----------------------------
SHOW REPLICA STATUS is an alias for SHOW SLAVE STATUS from MariaDB 10.5.1.

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:

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:

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

URL: https://mariadb.com/kb/en/show-replica-status/