MySQL 9.3 Reference Manual Including MySQL NDB Cluster 9.3

15.7.7.36 SHOW REPLICA STATUS Statement

SHOW REPLICA STATUS [FOR CHANNEL channel]

This statement provides status information on essential parameters of the replica threads. The statement requires the REPLICATION CLIENT privilege (or the deprecated SUPER privilege).

SHOW REPLICA STATUS is nonblocking. When run concurrently with STOP REPLICA, SHOW REPLICA STATUS returns without waiting for STOP REPLICA to finish shutting down the replication SQL (applier) thread or replication I/O (receiver) thread (or both). This permits use in monitoring and other applications where getting an immediate response from SHOW REPLICA STATUS is more important than ensuring that it returned the latest data.

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 REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 127.0.0.1
                  Source_User: root
                  Source_Port: 13000
                Connect_Retry: 1
              Source_Log_File: master-bin.000001
          Read_Source_Log_Pos: 927
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 1145
        Relay_Source_Log_File: master-bin.000001
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 927
              Relay_Log_Space: 1355
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: 73f86016-978b-11ee-ade5-8d2a2a562feb
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: 73f86016-978b-11ee-ade5-8d2a2a562feb:1-3
            Executed_Gtid_Set: 73f86016-978b-11ee-ade5-8d2a2a562feb:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:

The Performance Schema provides tables that expose replication information. This is similar to the information available from the SHOW REPLICA STATUS statement, but represented in table form. For details, see Section 29.12.11, “Performance Schema Replication Tables”.

You can set the GTID_ONLY option for the CHANGE REPLICATION SOURCE TO statement to stop a replication channel from persisting file names and file positions in the replication metadata repositories. With this setting, file positions for the source binary log file and the relay log file are tracked in memory. The SHOW REPLICA STATUS statement still displays file positions in normal use. However, because the file positions are not being regularly updated in the connection metadata repository and the applier metadata repository except in a few situations, they are likely to be out of date if the server is restarted.

For a replication channel with the GTID_ONLY setting after a server start, the read and applied file positions for the source binary log file (Read_Source_Log_Pos and Exec_Source_Log_Pos) are set to zero, and the file names (Source_Log_File and Relay_Source_Log_File) are set to INVALID. The relay log file name (Relay_Log_File) is set according to the relay_log_recovery setting, either a new file that was created at server start or the first relay log file present. The file position (Relay_Log_Pos) is set to position 4, and GTID auto-skip is used to skip any transactions in the file that were already applied.

When the receiver thread contacts the source and gets valid position information, the read position (Read_Source_Log_Pos) and file name (Source_Log_File) are updated with the correct data and become valid. When the applier thread applies a transaction from the source, or skips an already executed transaction, the executed position (Exec_Source_Log_Pos) and file name (Relay_Source_Log_File) are updated with the correct data and become valid. The relay log file position (Relay_Log_Pos) is also updated at that time.

The following list describes the fields returned by SHOW REPLICA STATUS. For additional information about interpreting their meanings, see Section 19.1.7.1, “Checking Replication Status”.

MySQL Documentation Library