Lag Diagnosis, Broken Replication, Data Drift, Re-Synchronization, and Promotion Runbooks

Learning outcomes

A replica alarm that says “lag = 120 seconds” is the start of an investigation, not a diagnosis. The receiver may be disconnected, the applier may be stopped on an error, parallel workers may be retrying transactions, the server may be resource-saturated, or the data may have drifted even while both threads report healthy. This final lesson builds a repeatable evidence tree and a promotion runbook.

A practical diagnosis tree

Always timestamp evidence. Replication state changes while you investigate, so a screenshot of one field without time/correlation can mislead.

Controlled failure 1: stop the applier, not the receiver

STOP REPLICA SQL_THREAD;SELECT CHANNEL_NAME, SERVICE_STATE, LAST_QUEUED_TRANSACTIONFROM performance_schema.replication_connection_status;SELECT CHANNEL_NAME, SERVICE_STATE, REMAINING_DELAYFROM performance_schema.replication_applier_status;

INSERT INTO servicehub_repl_lab.work_orders(site_code,status,summary,priority)VALUES ('BAKU-01','OPEN','Lag drill 1',3), ('BAKU-01','OPEN','Lag drill 2',3), ('BAKU-02','OPEN','Lag drill 3',3);

SELECT CHANNEL_NAME, SERVICE_STATE, SOURCE_UUID,       LAST_QUEUED_TRANSACTION, QUEUEING_TRANSACTIONFROM performance_schema.replication_connection_status;SELECT CHANNEL_NAME, SERVICE_STATE, REMAINING_DELAY,       COUNT_TRANSACTIONS_RETRIESFROM performance_schema.replication_applier_status;SELECT CHANNEL_NAME, WORKER_ID, SERVICE_STATE,       LAST_APPLIED_TRANSACTION,       LAST_ERROR_NUMBER, LAST_ERROR_MESSAGEFROM performance_schema.replication_applier_status_by_workerORDER BY CHANNEL_NAME, WORKER_ID;

The receiver can continue queueing while the applier is OFF. The repair is obvious because we created the failure: verify there is no underlying SQL error, restart the applier, then prove catch-up. In a real incident, never start/skip blindly before capturing the stopping error and deciding whether the source transaction is valid.

START REPLICA SQL_THREAD;SHOW REPLICA STATUS\GSELECT COUNT(*) AS lag_drill_rowsFROM servicehub_repl_lab.work_ordersWHERE summary LIKE 'Lag drill %';

Controlled failure 2: separate broken replication from drift

Replication can be running while old data is already wrong—for example, after a historical manual change, a bad provisioning snapshot, or a filter that was later removed. Thread health does not repair that past divergence. Build small stable invariants into operational checks.

SELECT COUNT(*) AS total_orders,       SUM(status='OPEN') AS open_orders,       MIN(work_order_id) AS min_id,       MAX(work_order_id) AS max_idFROM servicehub_repl_lab.work_orders;SELECT COUNT(*) AS baseline_markerFROM servicehub_repl_lab.replication_markersWHERE marker_name='CH14_SOURCE_BASELINE';CHECK TABLE servicehub_repl_lab.work_orders,            servicehub_repl_lab.replication_markers;

Matching aggregate counts are useful but not proof of byte-for-byte equality. Real drift verification may use checksums, application-level hashes, or rebuild-and-compare workflows appropriate to data size. Avoid running unbounded checksum scans on production during peak load without understanding their cost.

When is rebuilding safer than skipping?

Prefer reprovision/reclone when the replica's history is not trustworthy, errors span many transactions, filters produced substantial incompleteness, or a clean source snapshot is cheaper to reason about than handcrafted repair. Manual repair can be appropriate for a small understood discrepancy with explicit business ownership and verification, but it should be documented as a data correction—not hidden inside replication control commands.

Promotion is a correctness procedure

Suppose the source fails and the replica is reachable. The fastest command is not necessarily the safest action. Promotion should have explicit gates:

Disposable promotion drill

SHOW REPLICA STATUS\GSELECT @@GLOBAL.gtid_executed AS candidate_gtids;SELECT @@GLOBAL.read_only, @@GLOBAL.super_read_only;SELECT COUNT(*) AS orders,       SUM(status='OPEN') AS open_ordersFROM servicehub_repl_lab.work_orders;

Now simulate loss of the source container. Confirm the old source is fenced (stopped) before making the candidate writable.

docker compose stop mysql-sourcedocker compose ps

STOP REPLICA;SET GLOBAL super_read_only = OFF;SET GLOBAL read_only = OFF;INSERT INTO servicehub_repl_lab.replication_markers(marker_name)VALUES (CONCAT('PROMOTED_WRITE_', UUID()));SELECT marker_name,created_atFROM servicehub_repl_lab.replication_markersORDER BY marker_id DESC LIMIT 3;

Do not immediately reuse the old source as a replica. First compare the old source history when it becomes available. If it accepted transactions that the promoted node never received, you have a reconciliation decision. The safest rejoin path is often to rebuild the old node from the new authoritative source rather than assuming bidirectional convergence.

Lag monitoring that survives topology growth

MySQL 8.4 exposes original and immediate commit timestamps plus per-thread processing timestamps in Performance Schema replication tables. Use those to reason about where time is being spent. Traditional seconds-behind values can still help in simple topologies, but they are not a universal correctness metric and can become misleading in more complex chains.

SELECT CHANNEL_NAME, SERVICE_STATE, SOURCE_UUID,       LAST_QUEUED_TRANSACTION, QUEUEING_TRANSACTIONFROM performance_schema.replication_connection_status;SELECT CHANNEL_NAME, SERVICE_STATE, REMAINING_DELAY,       COUNT_TRANSACTIONS_RETRIESFROM performance_schema.replication_applier_status;SELECT CHANNEL_NAME, WORKER_ID, SERVICE_STATE,       LAST_APPLIED_TRANSACTION,       LAST_ERROR_NUMBER, LAST_ERROR_MESSAGEFROM performance_schema.replication_applier_status_by_workerORDER BY CHANNEL_NAME, WORKER_ID;

Final operational runbook template

INCIDENT / CHANGE ID:Old source:Candidate replica:Time evidence captured:FENCING[ ] Old writer cannot accept application traffic[ ] No alternate writer endpoint remains activeREPLICATION STATE[ ] Receiver state captured[ ] Applier/worker state captured[ ] Last errors/retries captured[ ] Source and candidate GTID sets compared[ ] Filter and SOURCE_DELAY configuration checkedDATA ACCEPTANCE[ ] Critical ServiceHub row/business invariants pass[ ] Accepted RPO documented if candidate is not fully caught upPROMOTION[ ] Candidate made writable[ ] Application endpoint switched[ ] Test write + read-after-write succeedsPOST-PROMOTION[ ] Old source preserved for transaction reconciliation[ ] Other replicas rebuilt/repointed under a reviewed plan[ ] Monitoring/backup jobs now target the promoted source[ ] Incident timeline and corrective action recorded

Production judgment and bridge

Asynchronous replication is operationally useful precisely because it separates source commit from replica application—but that separation creates a measurable data-loss window and requires promotion discipline. Chapter 15 moves to Group Replication and InnoDB Cluster, where quorum, membership, certification, MySQL Shell AdminAPI, and Router change the availability model. The habit from this chapter remains: topology state is evidence, and availability never substitutes for correctness.

Authoritative references

• MySQL 8.4 — Monitoring Replication Main Threads
• MySQL 8.4 — Monitoring Replication Applier Worker Threads
• MySQL 8.4 — Delayed Replication and Lag Monitoring
• MySQL 8.4 — Replication Retries and Timeouts
• MySQL 8.4 — GTIDs for Failover and Scaleout