MySQL Replication Monitoring: Complete Guide to SHOW SLAVE STATUS Parameters

When managing MySQL replication setups, monitoring slave performance and status becomes crucial for maintaining data consistency and identifying issues. The SHOW SLAVE STATUS\G command provides comprehensive insights into the replication process.

After establishing a MySQL master-slave configuration, administrators typically execute this command on the replica server to assess replication health:

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001822
          Read_Master_Log_Pos: 290072815
               Relay_Log_File: mysqld-relay-bin.005201
                Relay_Log_Pos: 256529594
        Relay_Master_Log_File: mysql-bin.001821
             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: 256529431
              Relay_Log_Space: 709504534
              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: 2923
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: 1
                  Master_UUID: 13ee75bb-99e2-11e6-be4d-b499baa80e6e
             Master_Info_File: /home/data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.02 sec)

Detailed Parameter Analysis

Slave_IO_State

This field displays the current state of the slave I/O thread, indicating the connection status between slave and master. The status matches information shown by SHOW PROCESSLIST | grep "system user", which reveals both slave I/O and SQL threads.

Common I/O thread states include:

Waiting for master update: Precedes connecting to master state
Connecting to master: I/O thread attempting to esttablish connection
Checking master version: Brief state after master connection establishment
Registering slave on master: Brief registration phase after connection
Requesting binlog dump: Requesting binary logs from specified position
Waiting to reconnect after failed binlog dump: Sleep period before retry attempt
Reconnecting after failed binlog dump: Active reconnection attempt
Waiting for master to send event: Successfully connected, awaiting events
Queueing master event to relay log: Event copied to relay log for SQL thread
Waiting for slave mutex on exit: Final state during thread shutdown

Master_Host: 192.168.1.100

The IP adress of the MySQL primary database server.

Master_User: repl_user

Credentials for the replication user account created on the master server with REPLICATION SLAVE privileges.

Master_Port: 3306

Standard MySQL service port number on the master server.

Connect_Retry: 60

Time interval in seconds between reconnection attempts after connection failure. Default is 60 seconds.

Master Log Information

Master_Log_File: mysql-bin.001822

Name of the binary log file currently being read by the I/O thread on the master server.

Read_Master_Log_Pos: 290072815

Current position within the binary log file that the I/O thread is reading.

Relay Log Information

Relay_Log_File: mysqld-relay-bin.005201

Name of the relay log file currently being processed by the SQL thread.

Relay_Log_Pos: 256529594

Current position within the relay log file being processed by the SQL thread.

Relay_Master_Log_File: mysql-bin.001821

Indicates which master binary log file corresponds to the current relay log events being executed by the SQL thread.

Thread Status Information

Slave_IO_Running: Yes

Indicates whether the I/O thread has started successfully and established connection to the master.

Slave_SQL_Running: Yes

Shows whether the SQL thread is active and processing events.

Filtering Parameters

These parameters control which databases and tables participate in replication:

Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table

Use these carefully as cross-database operations may cause issues. Wildcard table filtering is generally recommended.

Last_Errno: 0

Contains error codes from the SQL thread's execution. Zero indicates no errors occurred.

Skip_Counter: 0

Current value of SQL_SLAVE_SKIP_COUNTER, used to skip specific SQL statements during replication.

Exec_Master_Log_Pos: 256529431

Position in the master's binary log corresponding to the current event being executed by the SQL thread.

Relay_Log_Space: 709504534

Total size of all existing relay log files combined.

Until_Condition: None

Used with START SLAVE UNTIL clauses. Possible values are:

None: No UNTIL condition specified
Master: Processing until reaching specified master binary log position
Relay: Processing until reaching specified relay log position

Master_SSL_Allowed: No

Indicates SSL connection capabilities:

Yes: SSL connections permitted
No: SSL connections not allowed
Ignored: SSL supported but not enabled

Seconds_Behind_Master: 2923

Calculated difference between master event timestamp and current slave time, indicating replication lag in seconds.

Error Tracking Fields

These fields track the most recent errors:

Last_IO_Errno/Last_IO_Error: I/O thread error information
Last_SQL_Errno/Last_SQL_Error: SQL thread error information

Replicate_Ignore_Server_Ids

Lists server IDs that should be ignored during multi-source replication scenarios.

Master_Server_Id: 1

Unique identifier of the master server in the replication topology.

SQL_Delay: 0

Number of seconds the slave should lag behind the master, allowing for delayed replication.

SQL_Remaining_Delay: NULL

Shows remaining delay seconds when the SQL thread is waiting due to configured delay settings.

Slave_SQL_Running_State

Current state of the SQL thread:

Reading event from relay log: Processing relay log events
Has read all relay log: Waiting for new events from I/O thread
Waiting for slave mutex on exit: Thread shutdown state

Master_Retry_Count: 86400

Maximum number of reconnection attempts after master connection failures.

GTID Related Fields

Retrieved_Gtid_Set: GTIDs received by the I/O thread
Executed_Gtid_Set: GTIDs processed by the SQL thread
Auto_Position: 0 indicates traditional position-based replication

Reset Commands Overview

RESET MASTER

Deletes all binary log files and resets the index file, starting with a new sequence beginning at 000001. Should not be used when slaves are actively replicating.

RESET SLAVE

Clears replication position information, deletes relay log files, and starts with fresh relay logs. Does not modify CHANGE MASTER TO configuration parameters stored in memory.

RESET SLAVE ALL

Completely removes all replication configuration parameters from memory in addition to performing the functions of RESET SLAVE.

Tags: MySQL Replication slave-status monitoring Database-Administration

Posted on Fri, 09 Oct 2026 16:37:06 +0000 by barryflood22

Freaks City