mysqlbinlog Replay Scripting for Point-in-Time Recovery Automation #

The mysqlbinlog utility is the canonical way to turn archived binary log segments back into applied SQL, but the naive one-liner that recovery runbooks reach for — piping a directory glob straight into a live mysql client — is exactly the approach that produces silent, partial recoveries. It replays segments in filesystem order rather than rotation order, it applies to whatever host the shell happens to be pointed at, it swallows the utility’s exit code inside a pipe, and it has no stop condition, so it either overshoots the incident or dead-ends on the first corrupt file. This guide defines the replay driver that recovery automation actually needs: a typed Python wrapper that resolves segments in GTID order, streams each one through mysqlbinlog with a precise stop flag, checks the subprocess exit status before advancing, and applies to an isolated target that can be inspected before promotion. It is the execution engine underneath the workflow described in Point-in-Time Recovery Workflow Automation.

Visual Overview #

Core Concept & Prerequisites #

mysqlbinlog is a decoder, not an applier: it reads binary log files and emits their events as a SQL text stream on stdout. Applying that stream is a separate step — historically mysqlbinlog ... | mysql, but in an automation context the two are decoupled so the exit code of the decode can be inspected before any SQL touches the target. The decoder is deterministic only when the segments it reads were written with row images, which is why replay automation asserts binlog_format=ROW upstream; the rationale is in ROW vs STATEMENT vs MIXED Formats.

You need MySQL 8.0.22+ client tools (for --verify-binlog-checksum and stable GTID handling), Python 3.10+, and read access to the archived segments produced by the pipeline under Automated Binlog Archiving to Object Storage. Each segment must carry a resolvable GTID interval in the manifest so the driver can order and bound the replay — the coordinate model is covered in GTID Tracking & Enforcement. The target is an isolated instance, restored from a base backup with gtid_purged already seeded, per Point-in-Time Recovery Workflow Automation.

Three properties define a correct replay: order (segments applied in rotation sequence, never filesystem or lexical order that missorts at the .000999→.001000 boundary), boundedness (a stop condition that lands on the intended transaction), and atomic advance (a checkpoint that moves only after a segment’s SQL is durably applied).

Production-Grade Python Implementation #

The driver wraps mysqlbinlog in subprocess, never in a shell string, so segment paths and stop values cannot be interpreted by a shell. It decodes each segment to a temporary SQL stream, verifies the exit code, and only then feeds the SQL to the target through a fresh connection. A per-segment checkpoint lets a failed replay resume from the last applied segment.

# recovery/replay.py — Python 3.10+
from __future__ import annotations

import logging
import subprocess
from dataclasses import dataclass
from pathlib import Path

log = logging.getLogger("recovery.replay")


class ReplayError(RuntimeError):
    """Raised when a segment fails to decode or apply; aborts the run."""


@dataclass(slots=True, frozen=True)
class StopCondition:
    """Exactly one of these is set; renders to a mysqlbinlog flag."""
    datetime: str | None = None      # "2026-07-17 09:17:30"
    position: int | None = None      # byte offset within the final segment
    gtid: str | None = None          # "UUID:1-450000"

    def flags(self, is_last: bool) -> list[str]:
        # Stop flags apply only to the final segment; earlier ones replay whole.
        if not is_last:
            return []
        match self:
            case StopCondition(datetime=dt) if dt:
                return [f"--stop-datetime={dt}"]
            case StopCondition(position=pos) if pos is not None:
                return [f"--stop-position={pos}"]
            case StopCondition(gtid=g) if g:
                return [f"--exclude-gtids=", f"--stop-never=0"]  # see note below
        return []


def decode_segment(path: Path, stop: list[str]) -> bytes:
    """Run mysqlbinlog on one segment and return its SQL stream, or raise."""
    cmd = ["mysqlbinlog", "--verify-binlog-checksum", "--base64-output=DECODE-ROWS=0", *stop, str(path)]
    proc = subprocess.run(cmd, capture_output=True, check=False)
    if proc.returncode != 0:
        raise ReplayError(f"decode failed for {path.name}: {proc.stderr.decode()[:400]}")
    return proc.stdout


def replay(segments: list[Path], stop: StopCondition, apply_sql, checkpoint) -> None:
    """Apply an ordered segment list to an isolated target, checkpointing each."""
    start = checkpoint.last_applied_index() + 1
    for i in range(start, len(segments)):
        seg = segments[i]
        sql = decode_segment(seg, stop.flags(is_last=(i == len(segments) - 1)))
        apply_sql(sql)                       # into the isolated target, one txn stream
        checkpoint.advance(i, seg.name)      # atomic write only after apply succeeds
        log.info("applied %s (%d/%d)", seg.name, i + 1, len(segments))

Two details are load-bearing. The stop flags are attached only to the final segment, because a --stop-datetime passed to an earlier segment would truncate the replay prematurely; every segment before the last is replayed in full. And GTID-bounded stops are handled by restoring gtid_purged/gtid_executed boundaries on the target rather than a single CLI flag, since mysqlbinlog has no direct --stop-gtid; you either convert the GTID to the position within the segment that contains it, or you let the target’s gtid_executed and a --include-gtids filter bound the applied set. Converting a target GTID to a concrete position is exactly what Automating mysqlbinlog Replay with Stop-Position Checkpoints walks through.

When the incident is confined to a single schema, decoding can be narrowed with --database, which both shrinks apply time and reduces the review surface — with correctness caveats covered in Filtering mysqlbinlog Replay by Database and Table.

Configuration Reference #

Validation & Verification Gates #

A replay is only trustworthy if each of these passes before promotion:

• Every archived segment’s SHA-256 matches the manifest before it is decoded.
• mysqlbinlog --verify-binlog-checksum exits 0 for every segment (no CRC failures).
• Segments were applied in strict rotation order, verified against the manifest’s key sequence.
• The stop condition applied only to the final segment, and the target’s gtid_executed ends exactly at the intended transaction.
• GTID_SUBTRACT(required_interval, @@GLOBAL.gtid_executed) on the target is empty (nothing missing).
• The checkpoint’s last-applied index equals the final segment index on a clean run.
• A deliberately interrupted replay resumes from the checkpoint with no gap and no double-applied transaction.

The GTID-contiguity portion of this list is developed as a standalone gate in Detecting GTID Gaps Before PITR Replay.

Error Handling & Failure Modes #

ERROR 1590 (HY000): The incident LOST_EVENTS occurred on the master appears when a segment contains an Incident event — the primary recorded that events were lost (often after an unclean shutdown). Replay must stop: the chain has a real hole. Recovering the position across that boundary is covered in Crash-Safe Binlog Recovery.

ERROR 1782 / 1785 / 1837 around gtid_next indicate the target’s GTID state disagrees with the stream — typically a missing or wrong gtid_purged floor, which double-applies or skips transactions. Reset and reseed the floor before retrying.

mysqlbinlog: Error in Log_event::read_log_event(): 'read error' almost always means a segment was captured while it was still the active tail, so its terminating event is missing. This is an archiving-side defect; the deferral rule that prevents it is in Building a Python Script to Sync Binlogs to S3 with Boto3.

A non-zero exit swallowed by a shell pipe is the failure mode this driver exists to prevent: mysqlbinlog ... | mysql reports the exit status of mysql, not the decoder, so a mid-stream decode error looks like success. Decoding and applying as separate, exit-checked steps closes that hole.

Observability & Alerting #

Emit one structured log line per segment with its name, GTID interval, byte size, decode duration, and apply duration, plus a run-level summary with the resolved plan and the final gtid_executed. The two metrics worth alerting on are apply lag (wall-clock per segment, trending up signals target I/O saturation) and replay abort rate across drills (any non-zero rate is a recovery-readiness incident). During a real recovery, surface the running count of applied transactions against the required interval so an operator can watch the target converge on the boundary in real time. Wiring these into scheduled measurements is the subject of RTO/RPO Recovery Drills.

Frequently Asked Questions #

Related #

• Automating mysqlbinlog Replay with Stop-Position Checkpoints — resumable replay that converts a GTID target to a precise stop position.
• Filtering mysqlbinlog Replay by Database and Table — narrowing scope safely when an incident is confined to one schema.
• Timestamp Targeting Strategies — choosing between --stop-datetime, --stop-position, and a GTID stop.
• Recovery Validation Gates — proving the replayed chain is contiguous before promotion.

Back to Point-in-Time Recovery Workflow Automation.