MySQL replication lag shows up first as a support ticket: a user saves a change, reloads the page and the change is gone. The read went to a replica that hadn’t applied the write yet. The usual alert on Seconds_Behind_Master misses several ways this happens, including the one where replication stops and the metric disappears. The rules below cover each of them.
What you miss today
The metric name changed. MySQL 8.0.22 renamed the replication terms. SHOW REPLICA STATUS replaced SHOW SLAVE STATUS, and its columns now read Seconds_Behind_Source, Replica_IO_Running and Replica_SQL_Running. MySQL 8.4 removed the old statement. MariaDB still uses Seconds_Behind_Master. A dashboard built on the old names goes blank after an upgrade, and the alert behind it never fires again.
Lag goes NULL when replication stops. If the applier thread stops on an error, Seconds_Behind_Source becomes NULL. mysqld_exporter skips NULL values, so the lag series vanishes. An alert on lag > 30 sees no data and stays green while the replica falls further behind every second.
Zero doesn’t always mean caught up. The value compares the replica’s clock with the timestamp of the event it’s applying. If the receiver thread falls behind on a slow network, the applier catches up with what it has and reports 0. MySQL’s own docs say the column is only useful on fast networks.
Big transactions make lag jump. A single DELETE of millions of rows runs for minutes on the source. The replica gets it only after the commit, then needs the same minutes to apply it. Lag sits at 0, then jumps to the full run time in one step.
Common causes of lag to check when an alert fires:
- One applier thread can’t keep up with many writers on the source. From MySQL 8.0.27,
replica_parallel_workersdefaults to 4. On older versions or MariaDB, set it yourself. - Tables without a primary key. With row-based replication, the replica may scan the whole table for each changed row.
- A long query on the replica holding a metadata lock that the applier needs.
- A replica on a smaller instance or slower disk than the source.
- Large batch jobs that write in one transaction. Chunk them into small commits.
The exporter and metrics
Run mysqld_exporter against every replica, with a user that can read replication status:
CREATE USER 'exporter'@'%' IDENTIFIED BY 'change-me' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
On MariaDB 10.5 and later, also grant SLAVE MONITOR. Then start the exporter with the heartbeat collector turned on:
docker run -d --name mysqld-exporter -p 9104:9104 \
-v /etc/mysqld-exporter/.my.cnf:/.my.cnf:ro \
prom/mysqld-exporter:v0.21.0 \
--config.my-cnf=/.my.cnf \
--mysqld.address=mysql-replica-1.internal:3306 \
--collect.heartbeat \
--collect.heartbeat.database=heartbeat \
--collect.heartbeat.table=heartbeat
The .my.cnf file holds the [client] user and password. The slave_status collector is on by default. In your Prometheus scrape config, add a role="replica" label to replica targets, which one of the rules below needs:
scrape_configs:
- job_name: mysql
static_configs:
- targets: ["mysql-replica-1.internal:9104", "mysql-replica-2.internal:9104"]
labels:
role: replica
- targets: ["mysql-primary.internal:9104"]
labels:
role: primary
The exporter turns each column of the status output into a metric, so the names follow your server’s column names:
| What it measures | MySQL 8.0.22 and later | MySQL before 8.0.22, MariaDB |
|---|---|---|
| Lag in seconds | mysql_slave_status_seconds_behind_source | mysql_slave_status_seconds_behind_master |
| Receiver thread running | mysql_slave_status_replica_io_running | mysql_slave_status_slave_io_running |
| Applier thread running | mysql_slave_status_replica_sql_running | mysql_slave_status_slave_sql_running |
| Configured delay | mysql_slave_status_sql_delay | mysql_slave_status_sql_delay |
The thread metrics read 1 for Yes and 0 for No or Connecting. Each series carries master_host, master_uuid and channel_name labels. The prefix stays mysql_slave_status_ on every version, and the rules below match both column names with or.
pt-heartbeat for true lag. pt-heartbeat from Percona Toolkit writes a timestamp into a table on the source every second. The row replicates like any other write. On the replica, the exporter compares it with the current time, which measures lag end to end, the network included. Run it on the source:
pt-heartbeat --update --create-table --database heartbeat \
--host mysql-primary.internal --user heartbeat --ask-pass --daemonize --utc
Because of --utc, also pass --collect.heartbeat.utc to the exporter. Then lag is mysql_heartbeat_now_timestamp_seconds - mysql_heartbeat_stored_timestamp_seconds. I trust the heartbeat over Seconds_Behind_Source whenever the two disagree, because it doesn’t depend on which thread is behind.
Managed MySQL. On Amazon RDS for MySQL, read replicas use binlog replication. CloudWatch reports ReplicaLag in seconds, and mysqld_exporter works as above with the admin user’s grants. Aurora MySQL replicas share the cluster’s storage and don’t run SHOW REPLICA STATUS. Use CloudWatch’s AuroraReplicaLag, in milliseconds, or the exporter’s --collect.info_schema.replica_host collector and mysql_info_schema_replica_host_lag_seconds. Azure Database for MySQL shows replication lag in seconds as a portal metric for read replicas. Cloud SQL publishes database/replication/replica_lag. OCI MySQL HeatWave read replicas use async replication too, so the same stale-read risk applies. On every provider, pt-heartbeat works wherever you can create a database and run it from a VM.
The alert rules
groups:
- name: mysql-replication
rules:
- alert: MySQLReplicaLagging
expr: |
max by (instance, channel_name) (
mysql_slave_status_seconds_behind_source
or mysql_slave_status_seconds_behind_master
) > 30
for: 5m
labels:
severity: warning
annotations:
summary: "Replica {{ $labels.instance }} is {{ $value }} seconds behind its source"
description: "Reads from this replica are stale. Run SHOW REPLICA STATUS and SHOW PROCESSLIST on it; look for a large transaction in progress, a metadata lock or a busy disk."
- alert: MySQLReplicaLagCritical
expr: |
max by (instance, channel_name) (
mysql_slave_status_seconds_behind_source
or mysql_slave_status_seconds_behind_master
) > 300
for: 5m
labels:
severity: critical
annotations:
summary: "Replica {{ $labels.instance }} is over 5 minutes behind"
description: "Take it out of the read pool now, through ProxySQL's max_replication_lag or your load balancer. Then find the cause with SHOW REPLICA STATUS."
- alert: MySQLReplicationSQLThreadStopped
expr: |
(mysql_slave_status_replica_sql_running or mysql_slave_status_slave_sql_running) == 0
for: 1m
labels:
severity: critical
annotations:
summary: "The applier thread on {{ $labels.instance }} has stopped"
description: "Replication is broken and lag grows without limit. Read Last_SQL_Error in SHOW REPLICA STATUS. Fix the row or schema it names before you restart it with START REPLICA."
- alert: MySQLReplicationIOThreadStopped
expr: |
(mysql_slave_status_replica_io_running or mysql_slave_status_slave_io_running) == 0
for: 2m
labels:
severity: critical
annotations:
summary: "The receiver thread on {{ $labels.instance }} isn't connected to its source"
description: "Check Last_IO_Error in SHOW REPLICA STATUS. Common causes are network rules, a changed replication password and binlogs the source already purged."
- alert: MySQLReplicaStatusMissing
expr: |
mysql_up{role="replica"} == 1
unless on (instance)
(mysql_slave_status_seconds_behind_source or mysql_slave_status_seconds_behind_master)
for: 5m
labels:
severity: critical
annotations:
summary: "Replica {{ $labels.instance }} reports no lag value"
description: "Seconds_Behind_Source is NULL, so replication has stopped or was never configured. Run SHOW REPLICA STATUS and check both thread columns and both error fields."
- alert: MySQLHeartbeatLagging
expr: (mysql_heartbeat_now_timestamp_seconds - mysql_heartbeat_stored_timestamp_seconds) > 30
for: 5m
labels:
severity: warning
annotations:
summary: "Heartbeat on {{ $labels.instance }} is over 30 seconds old"
description: "Either the replica is behind or pt-heartbeat stopped on the source. Check the pt-heartbeat process first, then SHOW REPLICA STATUS."
MySQLReplicaStatusMissing is the rule most setups lack. It needs the role label from the scrape config, so it can tell a replica with NULL lag from a primary that never had any. A replica you’ve stopped on purpose fires it too, which is the point: someone should know.
Alerts tell a person. To protect users before that person acts, let the router act on lag too. ProxySQL’s mysql_servers.max_replication_lag takes a replica out of its hostgroup once lag passes the value in seconds, and returns it once lag drops. The MySQL too many connections recipe covers the ProxySQL setup. Some reads must see the user’s own write. Send those to the source, or wait on the replica with WAIT_FOR_EXECUTED_GTID_SET() and the write’s GTID.
Test the alerts
Use a staging source and replica with the same exporter version and rules as production. Put a steady write load on the source, for example sysbench’s oltp_write_only at a fixed --rate. Then cause each failure, watch the alert fire in Prometheus and confirm it clears after you undo it.
Lag. Delay the applier on the replica:
STOP REPLICA SQL_THREAD;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 400;
START REPLICA SQL_THREAD;
-- after the alerts fire
STOP REPLICA SQL_THREAD;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 0;
START REPLICA SQL_THREAD;
MySQLReplicaLagging fires after the lag passes 30 seconds for five minutes, then MySQLReplicaLagCritical, and MySQLHeartbeatLagging alongside. On MySQL before 8.0.23 and on MariaDB, use CHANGE MASTER TO MASTER_DELAY = 400 and the SLAVE keywords.
Stopped applier. Run STOP REPLICA SQL_THREAD; and wait. MySQLReplicationSQLThreadStopped fires, and so does MySQLReplicaStatusMissing, since lag goes NULL. That second alert proves the gap from the first section is closed. Run START REPLICA SQL_THREAD; afterwards.
Stopped receiver. Run STOP REPLICA IO_THREAD; for three minutes. MySQLReplicationIOThreadStopped fires. Start it again with START REPLICA IO_THREAD;.
Dead heartbeat. Stop pt-heartbeat on the source for six minutes. Only MySQLHeartbeatLagging should fire, since replication itself is fine. The description tells on-call to check the process first.
A large transaction. On staging only, delete a few million rows in one statement on the source. Watch the lag graph jump once the commit reaches the replica. Then run the same delete in batches of a few thousand rows. The lag should stay flat. That’s the change to make in your batch jobs.
It passes when each alert fires for its own failure, reaches the channel your on-call reads, and clears once you undo it.
Tune the noise
Start the thresholds from your app, then check them against a week of history:
max_over_time((mysql_slave_status_seconds_behind_source or mysql_slave_status_seconds_behind_master)[7d:1m])
quantile_over_time(0.99, (mysql_heartbeat_now_timestamp_seconds - mysql_heartbeat_stored_timestamp_seconds)[7d:1m])
- The threshold is a product decision. If users read from replicas right after they write, a few seconds of lag already breaks their experience. If replicas only serve reports, minutes may be fine. Ask what the reads on each replica power, and set the threshold from the answer.
- Batch windows. If the weekly max comes from one nightly job, fix the job by chunking its writes. Raising the threshold for everyone hides the next real incident.
- Delayed replicas. A replica with
SOURCE_DELAYreports its configured delay as lag. Subtract it in the expression with- on (instance, channel_name) mysql_slave_status_sql_delay, or label it and exclude it. - Backup and analytics replicas. Give them a different
rolelabel and their own, looser rules, so a long report doesn’t page anyone. fordurations. Five minutes on lag rides out the short spikes from a single large commit. Keep the thread rules short, because a stopped thread doesn’t recover on its own.
If the heartbeat and Seconds_Behind_Source disagree for long stretches, check network throughput between source and replica. The receiver is the slow side, and only the heartbeat sees it.
More recipes
- Fix MySQL ERROR 1040 Too Many Connections
Size mysql max_connections from memory and put ProxySQL in front, so app pods share a bounded pool and peak traffic stops hitting the connection limit.
- Improve Cache Hit Ratio to Protect Your Origin at Peak
Trim the cache key, send cache headers from the origin and serve stale while revalidating, so the CDN absorbs peak traffic instead of passing it to your origin.
- Fix ALB 502 Bad Gateway and 504 Gateway Timeout
Set each target's keep-alive above the ALB idle timeout and keep slow requests under it, so the load balancer stops answering peak traffic with 502 and 504.
- Fix Lambda TooManyRequestsException Rate Exceeded
Size Lambda concurrency from peak traffic and split it with reserved and provisioned concurrency, so critical functions stop throttling when traffic spikes.