Snapshot & Binlog Coordination for Consistent PITR Baselines #

A point-in-time recovery is a two-part chain: a base backup that establishes a known coordinate, and a run of binary logs that carries state forward from that coordinate to the target. The chain is only sound if the two parts meet exactly — the binlog replay must begin at precisely the GTID set the snapshot captured, with neither a gap (missing transactions between the snapshot and the first archived segment) nor an overlap (transactions present in both, which double-apply). Naive approaches get this wrong in a specific, dangerous way: they take a snapshot and separately note “the current binlog position,” but if any transaction commits between those two operations, the recorded coordinate no longer matches the snapshot’s contents, and every future recovery built on that backup is off by that delta. This guide defines how to capture a snapshot and its GTID coordinate as a single consistent fact, and how to prove the archived chain begins exactly where the snapshot ends. It is the baseline half of the recovery workflow in Point-in-Time Recovery Workflow Automation and the mirror of the anchoring described in Base Backup Integration for PITR.

Visual Overview #

Core Concept & Prerequisites #

The consistency guarantee hinges on capturing the snapshot and its GTID coordinate atomically with respect to the transaction stream. Physical backup tools do this correctly by design: a consistent snapshot brackets a moment in the redo/undo state and records the exact gtid_executed at that moment in a metadata file — mysqldump --single-transaction --source-data=2 writes the coordinate inside the same consistent read view, and physical tools write it to xtrabackup_binlog_info. The failure mode is doing it by hand: FLUSH TABLES WITH READ LOCK, note the position, snapshot, unlock — which is correct only if nothing slips between the lock acquisition and the position read, and which stalls writes for the snapshot duration.

You need MySQL 8.0.22+ with gtid_mode=ON (so the coordinate is a portable GTID set rather than a host-local file:position), Python 3.10+, a snapshot mechanism (a physical tool such as Percona XtraBackup, an LVM/cloud volume snapshot, or a logical dump), and the archived chain from Automated Binlog Archiving to Object Storage. The coordinate you capture must be expressed as a GTID set, because a file:position coordinate is meaningless on a recovery target with a different server_id — the portability argument is developed in GTID Tracking & Enforcement.

Production-Grade Python Implementation #

The coordinator captures a consistent snapshot coordinate and immediately validates it against the archived chain, so a misaligned backup is caught at creation time rather than at recovery time. It reads the GTID coordinate the backup tool recorded, then asserts the archive’s first segment begins exactly one transaction later.

# baseline/coordinate.py — Python 3.10+
from __future__ import annotations

from dataclasses import dataclass


@dataclass(slots=True, frozen=True)
class Baseline:
    backup_id: str
    base_gtid: str          # gtid_executed captured with the snapshot, e.g. "UUID:1-400000"


def parse_xtrabackup_coord(info_text: str) -> str:
    """Extract the GTID set from an xtrabackup_binlog_info / xtrabackup_info field."""
    for line in info_text.splitlines():
        # xtrabackup_binlog_info: "mysql-bin.000123<TAB>948<TAB>UUID:1-400000"
        parts = line.split("\t")
        if len(parts) == 3 and ":" in parts[2]:
            return parts[2].strip()
    raise ValueError("no GTID coordinate found in backup metadata")


def assert_clean_seam(base_gtid: str, first_segment_gtid: str, conn) -> None:
    """Prove the archive begins exactly where the snapshot ends: no gap, no overlap."""
    cur = conn.cursor()
    # Gap check: everything the snapshot covers must be a subset of base ∪ chain start.
    cur.execute("SELECT GTID_SUBTRACT(%s, %s)", (first_segment_gtid, base_gtid))
    only_in_chain = cur.fetchone()[0] or ""
    cur.execute("SELECT GTID_SUBTRACT(%s, %s)", (base_gtid, first_segment_gtid))
    only_in_base = cur.fetchone()[0] or ""
    cur.close()
    # A clean seam: the chain's first interval starts one past the base's last.
    if _overlaps(base_gtid, first_segment_gtid):
        raise ValueError(f"overlap at seam: transactions in both base and chain")
    if _has_gap(base_gtid, first_segment_gtid):
        raise ValueError(f"gap at seam: missing transactions between base and chain")

The assert_clean_seam call is what makes a backup usable, not merely present. Running it at backup-creation time turns “is this backup recoverable?” from a recovery-day discovery into a creation-day gate. The full standalone check, including how to compute the interval arithmetic robustly, is developed in Validating GTID Overlap Between a Base Backup and the Binlog Chain, and the XtraBackup-specific coordinate handling is in Aligning XtraBackup Snapshots with gtid_purged.

Configuration Reference #

Validation & Verification Gates #

• The backup’s GTID coordinate was captured atomically with the snapshot, not by a separate position read.
• The coordinate is stored as a GTID set (portable), not a file:position pair.
• GTID_SUBTRACT(first_segment_gtid, base_gtid) shows the chain starts immediately after the base — no gap.
• The base and the first segment share no transactions — no overlap that would double-apply.
• The seam segment is retained locally long enough to be archived after the backup completes.
• A test restore + one-segment replay applies cleanly with no gtid_next error.

Error Handling & Failure Modes #

A file:position coordinate on a GTID target cannot be used to seed gtid_purged; you get ERROR 1840/3546 or a silently wrong floor. Always capture and store the GTID set.

Overlap at the seam produces ERROR 1062 (duplicate key) or a gtid_next rejection during replay, because the first archived transaction was already in the snapshot. This comes from recording the coordinate before the snapshot’s consistent point; capture them together.

Gap at the seam is the more dangerous case because it can be silent: replay succeeds but skips transactions that committed between the snapshot and the first archived segment, so the recovered database is missing writes with no error. Only the interval-subtraction check catches it, which is why seam validation must run on every backup.

Observability & Alerting #

Record, per backup, the captured GTID coordinate and the seam-validation result, and alert if any backup lands with a gap or overlap. Track the seam margin — the number of transactions of headroom between the backup coordinate and the oldest still-archived segment — because as that margin shrinks toward zero you are one retention cycle away from an unrecoverable gap. Cross-reference this with the archiving-lag metric from Deriving RPO Targets from Binlog Archiving Lag: a healthy baseline needs both a clean seam and an archive that keeps pace.

Frequently Asked Questions #

Related #

• Aligning XtraBackup Snapshots with gtid_purged — the tool-specific coordinate extraction and floor-seeding procedure.
• Base Backup Integration for PITR — anchoring the archived chain to a verifiable recovery coordinate.
• Validating GTID Overlap Between a Base Backup and the Binlog Chain — the standalone seam-validation check.
• GTID Tracking & Enforcement — why the coordinate must be a portable GTID set.

Back to Point-in-Time Recovery Workflow Automation.