Automating mysqlbinlog Replay with Stop-Position Checkpoints #

The failure signature is a four-hour replay that dies at hour three on a transient target error, and the operator’s only option is to start over from the base backup because the replay kept no state. A point-in-time recovery that cannot resume turns every mid-replay hiccup into a full restart, and on a large chain that can blow the recovery time objective (RTO) by hours. This page builds a resumable replay: it converts the target GTID into an exact --stop-position within the segment that carries it, checkpoints after each segment is durably applied, and on restart resumes from the next unapplied segment. Over-application is made safe, under-application is made impossible, and a failure costs one segment of rework instead of the whole chain.

Context & Prerequisites #

This is the checkpointing layer on top of the replay driver defined in mysqlbinlog Replay Scripting for Point-in-Time Recovery Automation; read that first for the exit-code-gated subprocess pattern this extends. You need MySQL 8.0.22+ client tools, Python 3.10+, an isolated recovery target restored per Point-in-Time Recovery Workflow Automation, and archived segments whose manifest carries per-segment GTID intervals from Automated Binlog Archiving to Object Storage. Because mysqlbinlog has no --stop-gtid, the core trick is resolving a GTID target to a byte position.

Step-by-Step Implementation #

1. Identify the segment that carries the target GTID #

Use the manifest’s GTID intervals to find the single segment whose range contains the target transaction; every earlier segment replays in full, and this is the only one that needs a stop bound.

# Python 3.10+
def target_segment(segments, target_gtid_txn: int, uuid: str):
    # segments carry gtid like "UUID:400001-450000"; find the one containing the txn.
    for seg in segments:
        lo, hi = _interval_bounds(seg.gtid, uuid)
        if lo <= target_gtid_txn <= hi:
            return seg
    raise ValueError(f"{uuid}:{target_gtid_txn} not covered by the archive")

PITR relevance: narrowing the stop to one segment means the bound is applied exactly once, at the right place, and the arithmetic that proves the target is even present doubles as a gap check.

2. Convert the target GTID to a byte position #

Scan just the target segment with mysqlbinlog and read the end_log_pos of the Gtid event for the target transaction; that offset is the --stop-position.

# MySQL 8.0.22+  — find the end_log_pos just past the target GTID
mysqlbinlog --verify-binlog-checksum mysql-bin.000123 \
  | awk '/Gtid/{g=$0} /end_log_pos/{print $0}' | grep -n "UUID:450000"

PITR relevance: the position is exact and idempotent — replaying to that offset lands on the transaction boundary regardless of clock skew, which a --stop-datetime cannot guarantee when many transactions share a commit second.

3. Model the checkpoint as crash-safe state #

Persist the last applied segment index and name atomically, so a crash during a checkpoint write can never corrupt it.

# Python 3.10+
import json, os
from dataclasses import dataclass, asdict
from pathlib import Path

@dataclass(slots=True)
class ReplayCheckpoint:
    path: Path
    last_index: int = -1
    last_segment: str = ""

    def advance(self, index: int, name: str) -> None:
        self.last_index, self.last_segment = index, name
        tmp = self.path.with_suffix(".tmp")
        tmp.write_text(json.dumps(asdict(self) | {"path": str(self.path)}))
        os.replace(tmp, self.path)      # atomic on POSIX

PITR relevance: the checkpoint is the resume point. Because it advances only after a segment’s SQL is durably applied, a restart re-applies at most the interrupted segment — never skips one.

4. Resume from the checkpoint, idempotently #

On restart, begin at last_index + 1. Set slave_exec_mode=IDEMPOTENT on the target for the resumed run so a partially-applied interrupted segment re-applies cleanly.

# Python 3.10+
def resume(segments, stop_flags, apply_sql, ckpt: ReplayCheckpoint) -> None:
    for i in range(ckpt.last_index + 1, len(segments)):
        is_last = i == len(segments) - 1
        sql = decode_segment(segments[i], stop_flags if is_last else [])
        apply_sql(sql)
        ckpt.advance(i, segments[i].name)

PITR relevance: idempotent apply makes over-delivery safe; the checkpoint makes under-delivery impossible. Together they turn a fragile long replay into a restartable one.

Configuration Snippet & Reference Table #

# recovery target session — MySQL 8.0.22+
SET SESSION slave_exec_mode = 'IDEMPOTENT';   -- tolerate re-applied rows on resume
SET GLOBAL  innodb_flush_log_at_trx_commit = 2; -- disposable target: relax durability
SET GLOBAL  sync_binlog = 0;

Verification Checklist #

• The target GTID maps to exactly one segment, and every earlier segment replays in full.
• The resolved --stop-position equals the end_log_pos immediately after the target transaction.
• The checkpoint advances only after a segment’s SQL is durably applied.
• Killing the replay mid-segment and restarting resumes at that segment with no gap.
• The target’s final gtid_executed ends exactly at the target GTID, not past it.
• A resumed run with IDEMPOTENT mode produces no duplicate-key errors.

Gotchas & Version-Specific Caveats #

--stop-datetime is approximate. Two transactions can share a commit second, so a time bound may stop one transaction early or late. When you can name the transaction, resolve it to a position as above; use time only when you have not yet located the exact transaction, then narrow it — the trade-off is mapped in Timestamp Targeting Strategies.

Stop flags on a non-final segment truncate the replay. A --stop-position passed to an earlier segment stops there permanently; attach stop flags only to the final segment.

IDEMPOTENT mode masks divergence. It is correct for a resumed recovery replay, but it hides genuine data mismatches, so never leave it on for normal operation or apply it to a production primary.

MySQL 8.4 renamed replica variables. slave_exec_mode is replica_exec_mode on 8.4; pin the spelling to your target version to avoid an unknown-variable error.

Frequently Asked Questions #

Related #

• mysqlbinlog Replay Scripting — the exit-code-gated replay driver this checkpointing extends.
• Filtering mysqlbinlog Replay by Database and Table — narrowing a resumable replay to one schema.
• Timestamp Targeting Strategies — when to bound by time versus position versus GTID.

Back to mysqlbinlog Replay Scripting.