Lag, Breakage, Data Drift, Rebuilds, Promotion, and Replication Incident Runbooks

Learning outcomes

Replication incidents are rarely “replication is down.” One system may be unable to fetch new binlogs; another may have all events locally but be blocked applying DDL; another may be green but silently drifted after an operator skipped an error. Recovery starts by locating the failure stage and preserving evidence before changing state.

1. Triage by stage, not by one lag number

On 11.6+, Master_last_event_time, Slave_last_event_time, and Master_Slave_time_diff offer additional timing evidence. They complement GTID and thread state; they do not replace application-level correctness checks.

2. Independent incident lab: manufacture a duplicate-key stop safely

services:  primary:    image: mariadb:12.3.2    container_name: mdb14-primary    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --server-id=141      - --log-bin=mariadb-bin      - --binlog-format=ROW      - --gtid-domain-id=14      - --gtid-strict-mode=ON    ports:      - "33141:3306"  replica:    image: mariadb:12.3.2    container_name: mdb14-replica    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --server-id=142      - --log-bin=replica-bin      - --log-slave-updates=ON      - --relay-log=relay-bin      - --gtid-domain-id=14      - --gtid-strict-mode=ON      - --read-only=ON    ports:      - "33142:3306"

docker compose down -vdocker compose up -d

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'lab-repl';GRANT REPLICATION REPLICA ON *.* TO 'repl'@'%';-- Disposable lab only: discard initialization-era binlogs before application data.RESET MASTER;SHOW MASTER STATUS;SELECT @@server_id, @@gtid_domain_id, @@global.gtid_binlog_pos;

STOP REPLICA;RESET REPLICA ALL;SET GLOBAL gtid_slave_pos='';CHANGE MASTER TO  MASTER_HOST='primary',  MASTER_PORT=3306,  MASTER_USER='repl',  MASTER_PASSWORD='lab-repl',  MASTER_USE_GTID=slave_pos;START REPLICA;SHOW REPLICA STATUS\G

CREATE DATABASE IF NOT EXISTS servicehub_replication_lab;CREATE TABLE IF NOT EXISTS servicehub_replication_lab.tickets (  ticket_id BIGINT PRIMARY KEY,  customer_id BIGINT NOT NULL,  state VARCHAR(20) NOT NULL,  updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)             ON UPDATE CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;INSERT INTO servicehub_replication_lab.tickets(ticket_id,customer_id,state)VALUES (1,101,'open'),(2,102,'open')ON DUPLICATE KEY UPDATE state=VALUES(state);

Wait until the replica has both rows. Now inject drift on the replica without writing a new GTID: a privileged lab session disables binary logging locally, temporarily turns off read_only, and inserts a row that the primary will later create legitimately.

SET SESSION sql_log_bin=0;SET GLOBAL read_only=OFF;INSERT INTO servicehub_replication_lab.tickets(ticket_id,customer_id,state)VALUES (777,777,'rogue-local');SET GLOBAL read_only=ON;SELECT * FROM servicehub_replication_lab.tickets WHERE ticket_id=777;

INSERT INTO servicehub_replication_lab.tickets(ticket_id,customer_id,state)VALUES (777,777,'authoritative');

SHOW REPLICA STATUS\GSELECT @@global.gtid_slave_pos;SELECT * FROM servicehub_replication_lab.tickets WHERE ticket_id=777;

3. Wrong repair: skip until the dashboard is green

sql_slave_skip_counter exists, but skipping an event changes history. If the failed event was the authoritative INSERT and you skip it, the replica retains the rogue value and continues with permanent drift. A green thread state would then be cosmetically better and logically worse.

In this deliberately controlled lab, we know the local row is rogue and the primary row is authoritative. Remove only the rogue local row with binary logging still disabled, then restart SQL apply.

SET SESSION sql_log_bin=0;SET GLOBAL read_only=OFF;DELETE FROM servicehub_replication_lab.tickets WHERE ticket_id=777;SET GLOBAL read_only=ON;START REPLICA SQL_THREAD;SHOW REPLICA STATUS\GSELECT * FROM servicehub_replication_lab.tickets WHERE ticket_id=777;

The repaired row should now contain authoritative. This repair is valid only because the lab's source of truth is known. In an ambiguous production incident, compare data and transaction history before deleting anything.

4. Retention loss: when repair becomes rebuild

If a replica is offline longer than the primary retains required binlogs, the I/O thread cannot fetch the missing history. GTIDs make the missing position obvious but cannot recreate purged events. The correct recovery path is usually to reseed the replica from a validated logical/physical backup that is consistent with an available GTID/binlog position, then resume from there.

Chapter 13's restore drills become the replica rebuild primitive here. Recovery architecture is intentionally cumulative.

5. Promotion is a sequence of safety gates

A planned switchover is the easiest place to learn promotion discipline. The essential principle is one writable authority at a time. Core MariaDB replication does not enforce that across hosts for you.

1. Freeze new writes at the application/proxy/network layer or otherwise establish a maintenance write boundary.
2. Capture the old primary's final GTID position after the last accepted commit.
3. Wait for the candidate to receive and apply through that exact position; verify row/application invariants.
4. Fence the old primary so stale clients cannot continue writing to it.
5. Stop replica threads on the candidate, record its GTID/binlog state, and enable its write role.
6. Move client routing with health checks and rollback criteria.
7. Repoint/rebuild old replicas to the new authoritative primary using GTID state.

SHOW REPLICA STATUS\GSELECT @@global.gtid_slave_pos, @@global.gtid_binlog_pos, @@global.gtid_current_pos;-- Compare with the final GTID captured from the old primary.

STOP REPLICA;-- Keep the replication metadata until rollback/follow-up decisions are complete.SET GLOBAL read_only=OFF;

6. Demoting/repointing the old primary

After the new primary is accepted and the old server is safe to reuse, MariaDB 10.10+ provides MASTER_DEMOTE_TO_SLAVE=1 as a safer role-transition mechanism than blindly using MASTER_USE_GTID=current_pos. It merges the former primary's binlog position into the replica position at change time and forces slave-position semantics.

STOP REPLICA;CHANGE MASTER TO  MASTER_HOST='new-primary',  MASTER_USER='repl',  MASTER_PASSWORD='rotated-secret',  MASTER_DEMOTE_TO_SLAVE=1;START REPLICA;SHOW REPLICA STATUS\G

Do not run this blindly if the old primary contains accepted writes that never reached the promoted server. First decide whether those transactions are lost, must be merged, or invalidate the promotion.

7. Incident evidence packet

SELECT VERSION();SHOW REPLICA STATUS\GSELECT @@global.gtid_binlog_pos, @@global.gtid_slave_pos, @@global.gtid_current_pos;SHOW BINARY LOGS;SHOW VARIABLES WHERE Variable_name IN ('server_id','gtid_domain_id','gtid_strict_mode','read_only','slave_parallel_threads','slave_parallel_mode');SHOW PROCESSLIST;

• Server/OS/container resource state: CPU, memory pressure, disk latency/free space, network reachability.
• Replication error-log lines around the first failure, not only the latest retry.
• Topology/routing evidence: which endpoint applications were actually writing to.
• Recent DDL/deployments/filter changes and any use of skip/reset commands.
• Last validated backup plus binlog/GTID coverage sufficient for rebuild.

8. Async replication incident runbook

9. Production judgment and bridge to Galera

Async replication is excellent for read scaling, offloaded backups, migrations, delayed recovery copies, and geographically decoupled replicas when the application understands staleness. It can also support failover, but the safety properties come from the surrounding operating model: fencing, health assessment, candidate selection, client routing, backup/rebuild, and practiced runbooks.

Chapter 15 introduces Galera Cluster, where writes are replicated as write sets and nodes participate in certification/quorum. That changes the failure model, but it still does not remove the need for client routing, fencing, backup, or workload-aware conflict analysis.

docker compose down -v