Monitoring MySQL Replica Lag for RPO

Building on the parent guide, Continuous Replication Validation, this page implements a MySQL-specific lag validator that refuses to trust Seconds_Behind_Source and instead derives true lag from a heartbeat table while cross-checking GTID sets for gaps. The budget it enforces comes straight from your RTO/RPO mapping; the GTID-gap check applies the same “caught up is not the same as correct” scrutiny that a checksum validation pipeline brings to backup artifacts, and every verdict maps onto the shared error categorization tiers so a broken replica and a merely slow one are triaged differently.

Architecture and Execution Model

Figure. The validator combines a heartbeat timestamp for true lag with a GTID-set comparison for correctness, then gates the RPO budget into an exit code.

The reason this validator does not simply read Seconds_Behind_Source is that the field is structurally unreliable. It is computed on the replica as the difference between the SQL thread’s clock and the timestamp of the event currently being executed — which means it measures how far behind the replayed event is, not how far behind the replica is from the source right now. When the I/O thread stalls but the SQL thread has drained its relay log, the field reports 0 even though no new data is arriving; when the replica is idle it also reports 0; and after a network hiccup it can spike to enormous values that reflect an old event timestamp rather than current lag. A heartbeat table sidesteps all of this: a process on the source writes the current timestamp into a small table on a fixed interval, that row replicates like any other write, and the replica computes lag as its own clock minus the replicated timestamp — a direct, end-to-end measurement of the full pipeline.

The GTID check adds the correctness dimension. With gtid_mode=ON, every committed transaction carries a globally unique identifier, and SHOW REPLICA STATUS exposes two sets: Retrieved_Gtid_Set (what the I/O thread has pulled into the relay log) and Executed_Gtid_Set (what the SQL thread has actually committed). A healthy replica has executed nearly everything it retrieved; a persistent gap between the two means transactions are arriving but not applying. Worse, a hole inside the executed set — a missing transaction number in an otherwise contiguous range — signals that a transaction was skipped, which is silent data loss that no lag number will ever surface.

The two GTID signals map cleanly onto the transport-versus-apply distinction the parent guide draws. The difference between the retrieved and executed set counts is apply backlog measured in transactions: the I/O thread has already fetched these into the relay log, so the network is fine, but the SQL thread has not yet committed them. That backlog is the leading indicator that predicts a heartbeat-lag breach before the timestamp delta grows large enough to cross the budget, because relay-log accumulation precedes the visible lag it eventually causes. The internal hole, by contrast, is not a backlog at all — it is a permanent break in the transaction history that no amount of waiting will heal, which is exactly why the validator escalates it to an unconditional breach rather than a threshold comparison. Reading both from the same SHOW REPLICA STATUS snapshot keeps the transport, apply, and correctness signals consistent with one another.

Prerequisites

• Python 3.8+ and the pure-Python MySQL driver:

  pip install PyMySQL
  

• A heartbeat table on the source, updated on a fixed cadence. The canonical tool is pt-heartbeat from Percona Toolkit, but any process that runs REPLACE INTO heartbeat.heartbeat (server_id, ts) VALUES (@@server_id, NOW(6)) every second works; the table replicates to the standby automatically.

• gtid_mode=ON on the source and replica, so Retrieved_Gtid_Set and Executed_Gtid_Set are populated.

• A least-privilege validator account on the replica. Reading replica status needs REPLICATION CLIENT; reading the heartbeat row needs SELECT on that one table:

  CREATE USER 'dr_replmon'@'10.%' IDENTIFIED BY 'from-vault';
  GRANT REPLICATION CLIENT ON *.* TO 'dr_replmon'@'10.%';
  GRANT SELECT ON `heartbeat`.`heartbeat` TO 'dr_replmon'@'10.%';
  

Production Implementation

The validator connects only to the replica. It issues SHOW REPLICA STATUS for thread state and GTID sets, selects the heartbeat row to compute true lag entirely with the replica’s own NOW(6) (so the automation host’s clock is irrelevant), and parses the two GTID sets to detect both a retrieved-versus-executed backlog and internal holes.

#!/usr/bin/env python3
"""MySQL replica lag validator using a heartbeat table and GTID gap detection.

Exit codes (consumed by the DR failover gate):
    0  true lag within RPO and no GTID gap  -> promotable
    1  RPO breach, stalled thread, or GTID hole -> not promotable
    2  usage / configuration / connection error  -> abort the check
"""
import json
import logging
import sys

import pymysql

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | mysql_repl_lag | %(message)s",
)
logger = logging.getLogger("mysql_repl_lag")


def parse_gtid_set(gtid_set: str) -> dict:
    """Map each source UUID to its list of (start, end) executed intervals."""
    ranges: dict = {}
    if not gtid_set:
        return ranges
    for chunk in gtid_set.replace("\n", "").split(","):
        parts = chunk.split(":")
        uuid = parts[0]
        intervals = []
        for interval in parts[1:]:
            if "-" in interval:
                lo, hi = interval.split("-")
                intervals.append((int(lo), int(hi)))
            else:
                intervals.append((int(interval), int(interval)))
        ranges[uuid] = sorted(intervals)
    return ranges


def count_transactions(ranges: dict) -> int:
    """Total number of transactions represented across every UUID interval."""
    return sum(hi - lo + 1 for intervals in ranges.values() for lo, hi in intervals)


def has_internal_hole(executed: dict) -> bool:
    """True if any UUID's executed intervals are non-contiguous (a skipped GTID)."""
    for intervals in executed.values():
        for (_, prev_hi), (next_lo, _) in zip(intervals, intervals[1:]):
            if next_lo > prev_hi + 1:
                return True
    return False


def read_replica(conn) -> dict:
    with conn.cursor(pymysql.cursors.DictCursor) as cur:
        cur.execute("SHOW REPLICA STATUS")
        status = cur.fetchone()
        if status is None:
            raise RuntimeError("SHOW REPLICA STATUS returned no rows; not a replica")
        cur.execute(
            "SELECT TIMESTAMPDIFF(MICROSECOND, ts, NOW(6)) / 1000000 AS lag_s "
            "FROM heartbeat.heartbeat ORDER BY ts DESC LIMIT 1"
        )
        hb = cur.fetchone()
    return {"status": status, "heartbeat_lag": hb["lag_s"] if hb else None}


def evaluate(conn, rpo_seconds: float) -> int:
    data = read_replica(conn)
    status = data["status"]
    io_ok = status.get("Replica_IO_Running") == "Yes"
    sql_ok = status.get("Replica_SQL_Running") == "Yes"

    retrieved = parse_gtid_set(status.get("Retrieved_Gtid_Set", ""))
    executed = parse_gtid_set(status.get("Executed_Gtid_Set", ""))
    gtid_backlog = max(0, count_transactions(retrieved) - count_transactions(executed))
    hole = has_internal_hole(executed)
    true_lag = float(data["heartbeat_lag"]) if data["heartbeat_lag"] is not None else None

    metric = {
        "mysql_true_lag_seconds": true_lag,
        "mysql_gtid_backlog_txns": gtid_backlog,
        "mysql_io_running": io_ok,
        "mysql_sql_running": sql_ok,
    }
    logger.info("metrics %s", json.dumps(metric))

    if not (io_ok and sql_ok):
        logger.critical("BREACH replication thread stopped (io=%s sql=%s)", io_ok, sql_ok)
        return 1
    if hole:
        logger.critical("BREACH executed GTID set has an internal hole; transaction skipped")
        return 1
    if true_lag is None:
        logger.warning("no heartbeat row found; treating as breach")
        return 1
    if true_lag > rpo_seconds:
        logger.critical("BREACH true lag %.2fs exceeds RPO %.2fs (backlog %d txns)",
                        true_lag, rpo_seconds, gtid_backlog)
        return 1
    logger.info("OK true lag %.2fs within RPO %.2fs (backlog %d txns)",
                true_lag, rpo_seconds, gtid_backlog)
    return 0


def main() -> int:
    if len(sys.argv) != 3:
        print("usage: mysql_repl_lag.py <db_config.json> <rpo_seconds>", file=sys.stderr)
        return 2
    try:
        with open(sys.argv[1], encoding="utf-8") as fh:
            cfg = json.load(fh)
        rpo_seconds = float(sys.argv[2])
        if rpo_seconds <= 0:
            raise ValueError("rpo_seconds must be positive")
    except (OSError, ValueError, json.JSONDecodeError) as exc:
        print(f"config error: {exc}", file=sys.stderr)
        return 2
    try:
        conn = pymysql.connect(connect_timeout=5, **cfg)
    except pymysql.MySQLError as exc:
        logger.error("connection failed: %s", exc)
        return 2
    try:
        return evaluate(conn, rpo_seconds)
    except (pymysql.MySQLError, RuntimeError, KeyError) as exc:
        logger.error("check failed: %s", exc)
        return 2
    finally:
        conn.close()


if __name__ == "__main__":
    sys.exit(main())

The config file carries the replica connection parameters that pymysql.connect accepts directly:

{
  "host": "dr-replica-01.internal",
  "port": 3306,
  "user": "dr_replmon",
  "password": "${VAULT_REPLMON_PASS}",
  "database": "heartbeat"
}

Step-by-Step Execution Walkthrough

1. Start the heartbeat writer on the source — pt-heartbeat --update or an equivalent cron/systemd job — and confirm the row replicates to the standby with a SELECT * FROM heartbeat.heartbeat.

2. Render the config from your secret store, injecting the dr_replmon password at deploy time rather than committing it.

3. Run the validator, passing the RPO budget in seconds:

   python3 mysql_repl_lag.py db_config.json 20; echo "exit=$?"
   

4. Read the metric line. It reports true heartbeat lag, GTID backlog in transactions, and both thread states, so a stalled I/O thread or a growing backlog is visible even when the lag number looks fine.

5. Branch on the exit code. 0 promotes, 1 holds and escalates on a breach, stopped thread, or GTID hole, and 2 aborts on a bad config or connection.

Verification and Expected Output

A healthy replica within budget logs a metric line and an OK line, then exits 0:

2026-07-18 11:02:14 | INFO | mysql_repl_lag | metrics {"mysql_true_lag_seconds": 0.94, "mysql_gtid_backlog_txns": 3, "mysql_io_running": true, "mysql_sql_running": true}
2026-07-18 11:02:14 | INFO | mysql_repl_lag | OK true lag 0.94s within RPO 20.00s (backlog 3 txns)

A replica whose SQL thread has fallen behind the budget logs a CRITICAL line and exits 1:

2026-07-18 11:20:48 | INFO | mysql_repl_lag | metrics {"mysql_true_lag_seconds": 41.60, "mysql_gtid_backlog_txns": 5120, "mysql_io_running": true, "mysql_sql_running": true}
2026-07-18 11:20:48 | CRITICAL | mysql_repl_lag | BREACH true lag 41.60s exceeds RPO 20.00s (backlog 5120 txns)

The exit code is the contract the failover gate reads: 0 promote, 1 hold and escalate, 2 fix the invocation.

Failure Modes and Troubleshooting

Integration Notes

Schedule the validator at the cadence your RPO tier requires and wire its exit code into whichever orchestrator owns the DR loop. Its strict POSIX codes and JSON metric line let it fail an Airflow BashOperator task, raise inside a Celery task for event-driven dispatch, or trip a systemd OnFailure handler with no glue code. Map exit 1 onto the CRITICAL tier of your error categorization — and keep the GTID-hole case distinct from a plain lag breach, because one demands a rebuild while the other clears on its own once apply catches up. How this per-cycle verdict combines with a divergence check into the standing promotion gate is covered in the parent Continuous Replication Validation guide.

Frequently Asked Questions

Related

• Continuous Replication Validation — the parent guide covering lag sampling, divergence checks, and the promotion gate this validator feeds.
• Validating PostgreSQL Streaming Replication Lag — the sibling validator using LSN math and replay timestamps instead of heartbeats and GTIDs.
• RTO/RPO mapping frameworks — where the RPO budget this check enforces is derived.
• Validation telemetry and metrics — where the emitted lag and backlog series are recorded.

This validator is one component of the broader Continuous Replication Validation guide.

For authoritative behavior of the statements used here, see the MySQL replication documentation, the reference on replication GTID concepts, and the Python json documentation.