Monitor MySQL Replication Lag with Prometheus

Alert on MySQL replica lag, stopped replication threads and missing Seconds_Behind_Master values, so stale reads get caught before users report them.

Published

Applies to MySQL 8.0 and 8.4, MariaDB 10.6 and later; mysqld_exporter 0.16 and later, the first release that reads MySQL 8.4 replicas

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_workers defaults 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 measuresMySQL 8.0.22 and laterMySQL before 8.0.22, MariaDB
Lag in secondsmysql_slave_status_seconds_behind_sourcemysql_slave_status_seconds_behind_master
Receiver thread runningmysql_slave_status_replica_io_runningmysql_slave_status_slave_io_running
Applier thread runningmysql_slave_status_replica_sql_runningmysql_slave_status_slave_sql_running
Configured delaymysql_slave_status_sql_delaymysql_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_DELAY reports 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 role label and their own, looser rules, so a long report doesn’t page anyone.
  • for durations. 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

Further reading