Aligning XtraBackup Snapshots with gtid_purged #

The recovery fails on the first replayed segment with ERROR 1840 or a gtid_next rejection, and the cause is almost always the same: the restored XtraBackup target’s gtid_purged was never seeded, or was seeded from the wrong coordinate, so the archived binlog chain and the restored state disagree about where history begins. Percona XtraBackup records the exact GTID set it captured, but restoring the files does not automatically apply that coordinate — you must read it from the backup metadata and set gtid_purged explicitly before any replay. This page walks the extraction and seeding precisely, so the archived chain replays from a floor that meets the snapshot with no overlap and no gap.

Context & Prerequisites #

This is the XtraBackup-specific procedure behind the general coordinate model in Snapshot & Binlog Coordination for Consistent PITR Baselines; read that for why the coordinate must be a GTID set. You need Percona XtraBackup compatible with your server, MySQL 8.0.22+ with gtid_mode=ON, Python 3.10+, and the archived chain from Automated Binlog Archiving to Object Storage. The seeded floor is what the replay in mysqlbinlog Replay Scripting begins from.

Step-by-Step Implementation #

1. Locate the captured GTID coordinate #

After preparing the backup, the GTID set lives in xtrabackup_binlog_info (and xtrabackup_info). It is the third tab-separated field alongside the file and position.

# after: xtrabackup --prepare --target-dir=/restore
cat /restore/xtrabackup_binlog_info
# mysql-bin.000123    948    3E11FA47-...:1-400000

PITR relevance: this GTID set is the exact executed state frozen in the snapshot; it is the only correct value for gtid_purged, and any other value creates an overlap or a gap.

2. Parse the coordinate robustly #

Extract the GTID field, tolerating multi-line multi-server_uuid sets that XtraBackup writes comma-separated.

# Python 3.10+
from pathlib import Path

def xtrabackup_gtid(target_dir: str) -> str:
    text = Path(target_dir, "xtrabackup_binlog_info").read_text().strip()
    fields = text.split("\t")
    if len(fields) < 3 or ":" not in fields[2]:
        raise ValueError(f"no GTID set in xtrabackup_binlog_info: {text!r}")
    return fields[2].strip()      # "UUID1:1-400000,UUID2:1-5000"

PITR relevance: a multi-source topology has more than one server_uuid in the set; dropping any of them seeds an incomplete floor and re-applies that source’s transactions during replay.

3. Reset and seed the floor on the restored target #

On the freshly restored, empty target, reset GTID state then set gtid_purged to the captured set — in that order, because gtid_purged can only be set when gtid_executed is empty.

-- MySQL 8.0.22+  — on the restored target, before any replay
RESET BINARY LOGS AND GTID_SET;              -- 8.4 spelling; 8.0: RESET MASTER
SET @@GLOBAL.gtid_purged = '3E11FA47-...:1-400000';
SELECT @@GLOBAL.gtid_executed;               -- must now equal the seeded set

PITR relevance: seeding the floor tells the server “everything up to here is already applied,” so replay of the archived chain starts at the next transaction and the GTID arithmetic lines up.

4. Verify the seam before replaying #

Confirm the archived chain’s first segment begins exactly one transaction after the seeded floor.

-- MySQL 8.0.22+  — must be empty: nothing in the first segment is already present
SELECT GTID_SUBTRACT(:first_segment_gtid, @@GLOBAL.gtid_executed) AS chain_new;   -- non-empty = good, all new
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, :first_segment_gtid) AS overlap;      -- must be empty

PITR relevance: an empty overlap and a contiguous start prove the seam is clean; catching a mismatch here is far cheaper than discovering it mid-replay.

Configuration Snippet & Reference Table #

Verification Checklist #

• The GTID set is read from xtrabackup_binlog_info, not guessed or copied from another host.
• All server_uuid intervals in a multi-source set are preserved in the seeded value.
• The target’s gtid_executed is empty before gtid_purged is set.
• After seeding, gtid_executed equals the captured coordinate exactly.
• GTID_SUBTRACT(gtid_executed, first_segment_gtid) is empty (no overlap).
• The first replayed segment applies with no gtid_next or duplicate-key error.

Gotchas & Version-Specific Caveats #

RESET MASTER was renamed. On MySQL 8.4 use RESET BINARY LOGS AND GTID_SET; RESET MASTER still works on 8.0 but is not the forward-compatible spelling.

Setting gtid_purged on a non-empty target errors. ERROR 1840 / ERROR 3546 mean gtid_executed was not empty — reset first, and ensure no replay or write touched the target before seeding.

Multi-source sets are comma-separated and may wrap. Parse the whole third field, not just the first UUID:range; a topology with several sources loses coverage otherwise.

Prepare before reading the coordinate. The GTID metadata is finalized by xtrabackup --prepare; reading it from an unprepared backup can yield a stale or partial value.

Frequently Asked Questions #

Related #

• Snapshot & Binlog Coordination for Consistent PITR Baselines — the general coordinate model this implements for XtraBackup.
• Validating GTID Overlap Between a Base Backup and the Binlog Chain — the seam-validation check in depth.
• mysqlbinlog Replay Scripting — replaying the chain from the seeded floor.

Back to Snapshot & Binlog Coordination.