How to monitor replication in MySQL or MariaDB

Replica lag and stopped replication threads turn read scaling and failover into a gamble, because applications may read stale data or promote an out-of-date server. Monitoring replication surfaces issues early, keeps recovery time predictable, and protects data integrity when the database topology is under load.

Classic asynchronous replication writes changes to the binary log on the source (often called the master) and streams those events to the replica (often called the slave), where an I/O receiver thread stores them in a relay log and an SQL applier thread executes them. Replication health is mostly the state of those threads plus evidence that receive/apply positions (or GTID sets) keep moving forward.

Command names and field labels vary by version: MariaDB and older MySQL commonly use SHOW SLAVE STATUS, while newer MySQL also supports SHOW REPLICA STATUS using source and replica terminology. Run status commands on the replica, expect 0 rows on servers that are not configured for replication, and treat Seconds_Behind_Master as an estimate that can be NULL or misleading during stoppages and large transactions.

1. Connect to the replica using the mysql client with an account permitted to run SHOW SLAVE STATUS.

   $ mysql --user=monitor --password --execute "SHOW SLAVE STATUS\G"

   A dedicated monitoring user limits blast radius compared to logging in as root.

2. Run SHOW SLAVE STATUS in vertical output mode to display replication health details.

   *************************** 1. row ***************************
              Slave_IO_State: Waiting for source to send event
                 Master_Host: 192.0.2.40
                 Master_User: repl
                 Master_Port: 3306
             Master_Log_File: mysql-bin.000001
         Read_Master_Log_Pos: 1716
              Relay_Log_File: relay-bin.000003
               Relay_Log_Pos: 1024
       Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
               Last_IO_Error:
              Last_SQL_Error:
   ##### snipped #####

   MySQL 8.0 may also expose the same status via SHOW REPLICA STATUS\G using source and replica naming.

3. Confirm that Slave_IO_Running is Yes.

   No means the replica is not receiving binlog events from the source, so reads may become stale and failover may promote outdated data.

4. Confirm that Slave_SQL_Running is Yes.

   No means events are not being applied, so lag typically grows and Last_SQL_Error often explains the stop.

5. Check Seconds_Behind_Master to estimate replication lag.

   0 indicates no measured lag at the moment, while NULL is common when threads are stopped or lag cannot be calculated.

6. Inspect Last_IO_Error and Last_SQL_Error for non-empty messages.

   Last_IO_Error usually points at connectivity, authentication, or TLS issues, while Last_SQL_Error usually points at apply failures such as missing rows, duplicate keys, or schema drift.

7. Note Master_Log_File and Read_Master_Log_Pos to confirm receive progress.

   These values advance when the I/O thread pulls new events from the source.

8. Note Relay_Master_Log_File and Exec_Master_Log_Pos to confirm apply progress.

   These values advance when the SQL thread applies relay log events.

9. Wait 10 seconds.

   Replication failures are polite and quiet, so a short pause helps spot stalled positions.

10. Run SHOW SLAVE STATUS\G again.

    *************************** 1. row ***************************
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1989
        Relay_Master_Log_File: mysql-bin.000001
           Exec_Master_Log_Pos: 1989
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
         Seconds_Behind_Master: 0
    ##### snipped #####

11. Confirm that Read_Master_Log_Pos or Exec_Master_Log_Pos increased.

    Unchanged positions plus any No thread state indicates replication is stalled.

12. Capture a status snapshot to a text file for incident notes.

    $ mysql --user=monitor --password --execute "SHOW SLAVE STATUS\G" > replication-status.txt

    Avoid passing a password value on the command line, since it can leak via shell history or process listings.

13. Alert on changes in Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master.

    Polling the status query and triggering on No values or lag above a threshold catches most replication failures quickly, including network drops and apply halts.

14. Exit the mysql client session if you used the interactive shell.

    mysql> exit
    Bye