How to Monitor MySQL Replication Lag

Monitoring replication lag between a MySQL source and its replica is crucial for ensuring data consistency, high availability, and timely backups. This article provides a straightforward guide on how database administrators can measure and monitor this lag using built-in MySQL commands, the Performance Schema, heartbeat mechanisms, and external monitoring tools.

1. Using the SHOW REPLICA STATUS Command

The most common and immediate way to check replication lag is by querying the replica directly.

For MySQL 8.0.22 and newer, log into the replica database and run:

SHOW REPLICA STATUS\G

(Note: For older MySQL versions, use SHOW SLAVE STATUS\G).

Look for the following key fields in the output:

• Seconds_Behind_Source: This is the primary metric representing the lag in seconds. A value of 0 indicates the replica is caught up. A positive integer shows how many seconds the replica is behind the source. NULL indicates that replication is stopped or broken.
• Replica_IO_Running and Replica_SQL_Running: Both must display Yes for replication to be active. If either is No, replication has halted, and the Seconds_Behind_Source metric will be unreliable.

Limitations of Seconds_Behind_Source

While convenient, Seconds_Behind_Source can be misleading. It calculates lag by comparing the timestamp of the last transaction executed on the replica with the timestamp when that transaction was recorded on the source. If the network is slow or replication is completely blocked, the metric may temporarily report 0 even if the replica is not up to date.

2. Precision Monitoring with Percona’s pt-heartbeat

To overcome the limitations of Seconds_Behind_Source, administrators often use pt-heartbeat, a tool from the open-source Percona Toolkit.

This tool works by constantly updating a timestamp on a dedicated table on the source database. The replica replicates this table, and pt-heartbeat compares the replicated timestamp with the replica’s system clock.

• Benefit: It measures lag down to the millisecond, bypassing the limitations of MySQL’s built-in seconds counter.

• Command example to check lag:

  pt-heartbeat --database=test --monitor --host=replica_host

3. Querying the Performance Schema

For modern MySQL deployments, the Performance Schema provides detailed replication tables that offer more granular insights than SHOW REPLICA STATUS.

To check the replication status and delay, you can query the replication_connection_status and replication_applier_status_by_worker tables. For example:

SELECT 
    channel_name, 
    last_queued_transaction, 
    last_queued_transaction_original_commit_timestamp,
    last_queued_transaction_immediate_commit_timestamp
FROM performance_schema.replication_connection_status;

By comparing the commit timestamps in these tables to the current system time, you can programmatically calculate highly accurate lag metrics.

4. Setting Up Automated Alerts and Dashboards

Manual checks are insufficient for production environments. Database administrators should integrate MySQL replication metrics into centralized monitoring systems:

• Prometheus and Grafana: Utilize the mysqld_exporter to scrape replication metrics (such as mysql_slave_status_seconds_behind_master) and visualize them on a Grafana dashboard.
• Percona Monitoring and Management (PMM): A free, specialized tool for MySQL that provides out-of-the-box dashboards for replication lag, replication throughput, and binary log space.
• Datadog / New Relic: Standard enterprise APM tools have native MySQL integrations that can trigger instant alerts (via Slack, PagerDuty, or email) if the replication lag exceeds a predefined threshold (e.g., more than 30 seconds).