Recovery Validation Gates: Proving a PITR Chain Before You Need It #

The worst moment to discover that a binary log is corrupt, a segment is missing, or the GTID chain has a hole is during a recovery, when the outage clock is already running. Every one of those defects is detectable in advance by a cheap check that reads the archive without applying anything, yet most recovery programs run no such check and treat a green upload dashboard as proof of recoverability. It is not: an upload confirms bytes landed in a bucket, not that those bytes form a replayable, contiguous chain that a real mysqlbinlog will accept. This guide defines the validation gates that close that gap — checksum verification, GTID contiguity diffing, dry-run replay, and manifest reconciliation — and the pattern of running them continuously so a break is an alert on a quiet Tuesday rather than a surprise during an incident. These gates are the standing guarantee behind the abort path in Point-in-Time Recovery Workflow Automation, and they are what the drills in RTO/RPO Recovery Drills exercise end to end.

Visual Overview #

Core Concept & Prerequisites #

The gates form a ladder of increasing cost and increasing confidence, and running them in order fails cheap defects fast. Checksum verification is nearly free — it reads each object and compares its SHA-256 to the manifest, catching bit-rot and truncated uploads. GTID contiguity is pure arithmetic over the manifest intervals — it proves the set is gap-free from the base coordinate to the tail without reading a single event body. Dry-run replay actually decodes the segments with mysqlbinlog --verify-binlog-checksum but discards the output, proving the events parse and the CRC32s hold without mutating any target. Manifest reconciliation closes the loop by confirming every object the manifest references still exists in the bucket and nothing has been silently lifecycle-expired. Only the full drill in RTO/RPO Recovery Drills goes further, actually applying the stream — the gates here are the cheap continuous layer beneath that periodic heavy check.

You need MySQL 8.0.22+ client tools, Python 3.10+, the segment manifest with per-object GTID intervals and checksums from Automated Binlog Archiving to Object Storage, and read access to the archive bucket. The contiguity gate depends on a gap-free upstream GTID pipeline, per GTID Tracking & Enforcement.

Production-Grade Python Implementation #

The gate runner executes the ladder in order, short-circuiting on the first failure so an expensive dry-run is never wasted on a chain that already failed the free checks. Each gate returns a structured result so the alert names the exact gate and segment that failed.

# validation/gates.py — Python 3.10+
from __future__ import annotations

import subprocess
from dataclasses import dataclass


@dataclass(slots=True, frozen=True)
class GateResult:
    gate: str
    ok: bool
    detail: str = ""


def gate_checksums(segments, store) -> GateResult:
    for seg in segments:
        if store.sha256(store.get(seg.key)) != seg.sha256:
            return GateResult("checksum", False, f"mismatch on {seg.key}")
    return GateResult("checksum", True)


def gate_contiguity(base_gtid: str, segments, conn) -> GateResult:
    """Pure GTID arithmetic: prove no gap from base floor to the last segment."""
    cur = conn.cursor()
    covered = base_gtid
    for seg in segments:
        cur.execute("SELECT GTID_SUBTRACT(%s, %s)", (seg.gtid, covered))
        new = cur.fetchone()[0] or ""
        # Each segment must extend `covered` contiguously; a jump leaves a hole.
        if _leaves_gap(covered, seg.gtid):
            cur.close()
            return GateResult("contiguity", False, f"gap before {seg.key}")
        covered = _union(covered, seg.gtid)
    cur.close()
    return GateResult("contiguity", True, covered)


def gate_dry_run(segments, store) -> GateResult:
    for seg in segments:
        proc = subprocess.run(
            ["mysqlbinlog", "--verify-binlog-checksum", "-"],
            input=store.get(seg.key), capture_output=True, check=False,
        )
        if proc.returncode != 0:
            return GateResult("dry_run", False, f"{seg.key}: {proc.stderr.decode()[:200]}")
    return GateResult("dry_run", True)


def run_gates(base_gtid, segments, store, conn) -> list[GateResult]:
    results: list[GateResult] = []
    for gate in (
        lambda: gate_checksums(segments, store),
        lambda: gate_contiguity(base_gtid, segments, conn),
        lambda: gate_dry_run(segments, store),
    ):
        r = gate()
        results.append(r)
        if not r.ok:
            break                      # short-circuit: fail cheap defects fast
    return results

The contiguity gate is the highest-value, lowest-cost check of the four, and it deserves its own focused treatment — including how to compute the interval subtraction robustly across multi-server_uuid sets — in Detecting GTID Gaps Before PITR Replay. The manifest-reconciliation gate overlaps with the archiver’s own weekly reconciliation described in Automated Binlog Archiving to Object Storage, run here from the recovery side.

Configuration Reference #

Validation & Verification Gates #

• Every archived object’s SHA-256 matches its manifest entry.
• The manifest GTID intervals are contiguous from the base-backup coordinate to the live tail.
• mysqlbinlog --verify-binlog-checksum decodes every segment with exit 0 and no CRC error.
• Every object the manifest references still exists in the bucket (no silent lifecycle expiry).
• The gate runner short-circuits on the first failure and names the failing gate and segment.
• A deliberately corrupted test object is caught by the checksum gate before the dry-run runs.
• Gate results are emitted as metrics, and any failure pages within the alert window.

Error Handling & Failure Modes #

A checksum mismatch is non-retryable: re-reading a corrupt object does not repair it. The gate marks the chain unrecoverable at that segment and pages; remediation is re-archiving from the local file if it still exists, per the deferral and dead-letter logic in Building a Dead-Letter Queue for Failed Binlog Uploads.

A contiguity gap means a segment is missing from the manifest — either never archived or lifecycle-expired. This is the single most dangerous defect because replay of a gapped chain can succeed while silently skipping transactions; only the arithmetic gate catches it before an incident.

A dry-run decode failure with read_log_event(): 'read error' points to a segment captured as an active tail, an archiving-side bug covered in mysqlbinlog Replay Scripting. A CRC failure points to storage corruption between upload and now.

A reconciliation miss — a manifest entry with no backing object — usually means an object-storage lifecycle rule expired a segment still inside the recovery window; the fix is tightening those rules per Object Storage Lifecycle & Cost Management.

Observability & Alerting #

Publish a single boolean per chain — recoverable — derived from the conjunction of all gates, plus a per-gate pass/fail time series so a regression is attributable. The alert that matters most is contiguity broke, because it is both the most dangerous and the cheapest to detect, and it should page immediately rather than wait for a daily rollup. Track time-since-last-green-gate-run; a validation program that silently stops running is indistinguishable from one that always passes until the day you need it. These signals sit alongside the drill metrics in RTO/RPO Recovery Drills and feed the honest RPO reporting in Fallback Routing Strategies.

Frequently Asked Questions #

Related #

• Detecting GTID Gaps Before PITR Replay — the focused contiguity gate and its interval arithmetic.
• RTO/RPO Recovery Drills — the heavier periodic check that applies the stream and measures RTO.
• mysqlbinlog Replay Scripting — the replay engine the dry-run gate exercises.
• Base Backup Integration for PITR — the base coordinate the contiguity gate measures from.

Back to Point-in-Time Recovery Workflow Automation.