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
to using the channel_name directly after SHOW SLAVE.

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

+---------------------+------------------------------------------------------+
| Name                | Description                                          |
+---------------------+------------------------------------------------------+
| Connection_name     | Name of the primary connection. Returned with SHOW   |
|                     | ALL SLAVES STATUS (or SHOW ALL REPLICAS STATUS from  |
|                     | MariaDB 10.5.1) only.                                |
+---------------------+------------------------------------------------------+
| Slave_SQL_State     | State of SQL thread. Returned with SHOW ALL SLAVES   |
|                     | STATUS (or SHOW ALL REPLICAS STATUS from MariaDB     |
|                     | 10.5.1) only. See Slave SQL Thread States.           |
+---------------------+------------------------------------------------------+
| Slave_IO_State      | 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_File     | Name of the primary binary log file that the I/O     |
|                     | thread is currently reading from.                    |
+---------------------+------------------------------------------------------+
| Read_Master_Log_Pos | Position up to which the I/O thread has read in the  |
|                     | current primary binary log file.                     |
+---------------------+------------------------------------------------------+
| Relay_Log_File      | 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_Log_Fi | Name of the primary binary log file that contains    |
| e                   | the most recent event executed by the SQL thread.    |
+---------------------+------------------------------------------------------+
| Slave_IO_Running    | Whether the replica I/O thread is running and        |
|                     | connected (Yes), running but not connected to a      |
|                     | primary (Connecting) or not running (No).            |
+---------------------+------------------------------------------------------+
| Slave_SQL_Running   | Whether or not the SQL thread is running.            |
+---------------------+------------------------------------------------------+
| Replicate_Rewrite_D | Databases specified for replicating  and rewriting   |
|                     | with the replicate_rewrite_db option. Added in       |
|                     | MariaDB 10.11                                        |
+---------------------+------------------------------------------------------+
| Replicate_Do_DB     | Databases specified for replicating with the         |
|                     | replicate_do_db option.                              |
+---------------------+------------------------------------------------------+
| Replicate_Ignore_DB | Databases specified for ignoring with the            |
|                     | replicate_ignore_db option.                          |
+---------------------+------------------------------------------------------+
| Replicate_Do_Table  | Tables specified for replicating with the            |
|                     | replicate_do_table option.                           |
+---------------------+------------------------------------------------------+
| Replicate_Ignore_Ta | Tables specified for ignoring with the               |
| le                  | replicate_ignore_table option.                       |
+---------------------+------------------------------------------------------+
| Replicate_Wild_Do_T | Tables specified for replicating with the            |
| ble                 | replicate_wild_do_table option.                      |
+---------------------+------------------------------------------------------+
| Replicate_Wild_Igno | Tables specified for ignoring with the               |
| e_Table             | 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_Log_Pos | Position up to which the SQL thread 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_Space     | Total size of all relay log files combined.          |
+---------------------+------------------------------------------------------+
| Until_Condition     |                                                      |
+---------------------+------------------------------------------------------+
| Until_Log_File      | 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_Allowed  | Whether an SSL connection is permitted (Yes), not    |
|                     | permitted (No) or permitted but without the replica  |
|                     | having SSL support enabled (Ignored)                 |
+---------------------+------------------------------------------------------+
| Master_SSL_CA_File  | The MASTER_SSL_CA option of the CHANGE MASTER TO     |
|                     | statement.                                           |
+---------------------+------------------------------------------------------+
| Master_SSL_CA_Path  | The MASTER_SSL_CAPATH option of the CHANGE MASTER    |
|                     | TO statement.                                        |
+---------------------+------------------------------------------------------+
| Master_SSL_Cert     | The MASTER_SSL_CERT option of the CHANGE MASTER TO   |
|                     | statement.                                           |
+---------------------+------------------------------------------------------+
| Master_SSL_Cipher   | The MASTER_SSL_CIPHER option of the CHANGE MASTER    |
|                     | TO statement.                                        |
+---------------------+------------------------------------------------------+
| Master_SSL_Key      | The MASTER_SSL_KEY option of the CHANGE MASTER TO    |
|                     | statement.                                           |
+---------------------+------------------------------------------------------+
| Seconds_Behind_Mast | Difference between the timestamp logged on the       |
| r                   | 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 serial replication,        |
|                     | seconds_behind_master is updated when the SQL        |
|                     | thread begins executing a transaction. With          |
|                     | parallel replication, seconds_behind_master is       |
|                     | updated only after transactions commit. Starting in  |
|                     | MariaDB 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.8.7,  |
|                     | 10.9.5, 10.10.3, and 10.11.2, an exception is drawn  |
|                     | on the parallel replica to additionally update       |
|                     | seconds_behind_master when the first transaction     |
|                     | received after idling is queued to a worker for      |
|                     | execution, to provide a reliable initial value for   |
|                     | the duration until a transaction commits.            |
|                     | Additional behaviors to be aware of are as follows:  |
|                     | 1) Seconds_Behind_Master will update for ignored     |
|                     | events, e.g. those skipped due to                    |
|                     | sql_slave_skip_counter. 2) On the serial replica,    |
|                     | transactions with prior timestamps can update        |
|                     | Seconds_Behind_Master such that it can go            |
|                     | backwards, though this is not true for the parallel  |
|                     | replica. 3) When configured with MASTER_DELAY, as a  |
|                     | replicated transaction begins executing (i.e. on a   |
|                     | serial or post-idle parallel replica),               |
|                     | Seconds_Behind_Master will update before delaying,   |
|                     | and while delaying occurs will grow to encompass     |
|                     | the configured value. 4) There is a known issue,     |
|                     | tracked by MDEV-17516, such that                     |
|                     | Seconds_Behind_Master will initially present as 0    |
|                     | on replica restart until a replicated transaction    |
|                     | begins executing, even if the last replica session   |
|                     | was lagging behind when stopped.                     |
+---------------------+------------------------------------------------------+
| Master_SSL_Verify_S | The MASTER_SSL_VERIFY_SERVER_CERT option of the      |
| rver_Cert           | CHANGE MASTER TO 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_Errno      | 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_Error      | 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_Ignore_Se | List of server_ids that are currently being ignored  |
| ver_Ids             | 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_Id    | The master's server_id value.                        |
+---------------------+------------------------------------------------------+
| Master_SSL_Crl      | The MASTER_SSL_CRL option of the CHANGE MASTER TO    |
|                     | statement.                                           |
+---------------------+------------------------------------------------------+
| Master_SSL_Crlpath  | The MASTER_SSL_CRLPATH option of the 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_transaction | Number of retried transactions for this connection.  |
|                     | Returned with SHOW ALL SLAVES STATUS only.           |
+---------------------+------------------------------------------------------+
| Max_relay_log_size  | Max relay log size for this connection. Returned     |
|                     | with SHOW ALL SLAVES STATUS only.                    |
+---------------------+------------------------------------------------------+
| Executed_log_entrie | How many log entries the replica has executed.       |
|                     | Returned with SHOW ALL SLAVES STATUS only.           |
+---------------------+------------------------------------------------------+
| Slave_received_hear | How many heartbeats we have got from the master.     |
| beats               | Returned with SHOW ALL SLAVES STATUS only.           |
+---------------------+------------------------------------------------------+
| Slave_heartbeat_per | How often to request a heartbeat packet from the     |
| od                  | master (in seconds). Returned with SHOW ALL SLAVES   |
|                     | STATUS only.                                         |
+---------------------+------------------------------------------------------+
| Gtid_Slave_Pos      | 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 CHANGE MASTER     |
|                     | (or 0 if none).                                      |
+---------------------+------------------------------------------------------+
| SQL_Remaining_Delay | When the replica 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.               |
+---------------------+------------------------------------------------------+
| Slave_SQL_Running_S | The state of the SQL driver threads, same as in      |
| ate                 | 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_Groups    | This status variable counts the occurrence of DDL    |
|                     | statements.  This is a replica-side counter for      |
|                     | optimistic parallel replication.                     |
+---------------------+------------------------------------------------------+
| Slave_Non_Transacti | This status variable counts the occurrence of        |
| nal_Groups          | non-transactional event groups.  This is a           |
|                     | replica-side counter for optimistic parallel         |
|                     | replication.                                         |
+---------------------+------------------------------------------------------+
| Slave_Transactional | This status variable counts the occurrence of        |
| Groups              | 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 mariadb 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/