Validating GTID Overlap Between a Base Backup and the Binlog Chain #

A base backup and an archived binlog chain are each individually healthy and yet, together, unrecoverable — because the chain’s oldest segment begins after the backup’s coordinate, leaving a gap no replay can bridge. This is the most expensive defect to discover on recovery day and the cheapest to prevent at backup time, and the difference is one GTID set subtraction. This page builds the overlap validation: at the moment a base backup completes, it proves that the archived chain covers the interval from the backup’s captured coordinate forward, with no gap at the seam — and fails the backup loudly if it does not. A backup that passes this check is provably recoverable; one that skips it is a hope.

Context & Prerequisites #

This is the standalone seam-validation check that Base Backup Integration for PITR: Anchoring Binary Log Archives to a Verifiable Recovery Coordinate depends on, and it applies the coordinate model from Snapshot & Binlog Coordination. You need MySQL 8.0.22+ with gtid_mode=ON (for GTID_SUBTRACT), Python 3.10+, the base backup’s captured GTID coordinate, and the archived segment manifest from Automated Binlog Archiving to Object Storage.

Step-by-Step Implementation #

1. Capture the base backup’s coordinate #

Read the exact gtid_executed the backup froze — from xtrabackup_binlog_info for physical backups or the SET @@GLOBAL.gtid_purged header for logical dumps.

# Python 3.10+
def base_coordinate(meta_text: str) -> str:
    for line in meta_text.splitlines():
        if line.startswith("SET @@GLOBAL.gtid_purged="):
            return line.split("=", 1)[1].strip().strip("';")
        parts = line.split("\t")
        if len(parts) == 3 and ":" in parts[2]:
            return parts[2].strip()
    raise ValueError("no GTID coordinate in backup metadata")

PITR relevance: this coordinate is the floor a recovery seeds into gtid_purged; the chain must begin exactly one transaction later for a clean seam.

2. Find the archive’s oldest and newest GTIDs #

From the manifest, take the first segment’s interval start and the newest confirmed segment’s interval end.

# Python 3.10+
def archive_bounds(segments) -> tuple[str, str]:
    ordered = sorted(segments, key=lambda s: s.key)
    return ordered[0].gtid, ordered[-1].gtid       # oldest, newest confirmed

PITR relevance: the archive’s oldest boundary is what must reach back to (or before) the base coordinate; if it starts later, there is an irreparable gap between backup and chain.

3. Prove no gap at the seam #

The set of transactions the backup covers, minus what the archive plus base can supply, must be empty going forward from the base.

-- MySQL 8.0.22+  — must be empty: nothing between base and the chain is missing
SELECT GTID_SUBTRACT(
         :archive_oldest_gtid,
         GTID_SUBTRACT(:archive_oldest_gtid, :base_gtid)
       ) AS seam_overlap;      -- non-empty overlap is fine; a gap shows as missing coverage

# Python 3.10+  — the decisive check
def seam_ok(base_gtid: str, archive_oldest: str, conn) -> bool:
    cur = conn.cursor()
    # A clean seam: the archive's first interval is contiguous with, or overlaps, the base floor.
    cur.execute("SELECT GTID_SUBTRACT(%s, %s)", (archive_oldest, base_gtid))
    only_in_chain = (cur.fetchone()[0] or "")
    cur.close()
    return _is_contiguous_or_overlapping(base_gtid, archive_oldest)

PITR relevance: an empty gap and a contiguous (or overlapping) start prove that a recovery seeded at the base floor can replay the chain without hitting a missing transaction.

4. Fail the backup on a gap #

If the seam is not clean, the backup is not recoverable — mark it failed and page, rather than storing a backup that only looks valid.

PITR relevance: failing at creation time means you find the problem while the source segments still exist and can be archived, not on recovery day when they are long purged.

Configuration Snippet & Reference Table #

Verification Checklist #

• The base coordinate is read from backup metadata as a GTID set, not a file:position.
• The archive’s oldest and newest confirmed GTIDs are taken from the manifest.
• The seam check proves the archive’s first interval is contiguous with or overlaps the base floor.
• A gap at the seam fails the backup and pages, rather than storing an unrecoverable backup.
• The check runs at backup completion, while source segments still exist locally.
• A periodic re-validation confirms the seam still holds as retention ages the chain.

Gotchas & Version-Specific Caveats #

Overlap is fine; a gap is fatal. A small overlap (the chain begins a little before the base coordinate) replays harmlessly under idempotent apply; a gap (the chain begins after) is unrecoverable. The check must distinguish the two, not reject both.

Validate at creation, then re-validate over time. A seam that is clean at backup time can break later if retention purges the archive’s oldest segments while the backup lives on. Re-run the check on a schedule against the current manifest.

Multi-source coordinates need every server_uuid. In a multi-source topology, the base coordinate and the archive bounds each span several sources; compare them per source or the arithmetic is wrong.

File:position coordinates cannot be validated this way. Without GTIDs there is no portable set arithmetic; enable gtid_mode=ON, per GTID Tracking & Enforcement.

Frequently Asked Questions #

Related #

• Base Backup Integration for PITR — the parent that this seam check anchors.
• Snapshot & Binlog Coordination — capturing the coordinate atomically so the seam can be clean.
• Detecting GTID Gaps Before PITR Replay — the same arithmetic applied across the whole chain.

Back to Base Backup Integration for PITR.