MySQL GTID Sets: Compare Histories Before Rejoining

Context

A global transaction identifier, or GTID, labels a transaction within a MySQL replication history. Comparing GTID sets can show which recorded transactions one server has that another lacks. This is valuable after a disconnected replica or a topology change, but GTIDs are not a substitute for understanding who accepted writes and which backup seeded the server.

Before comparing servers

Collect the exact server identities, replication channels, MySQL versions, backup origin, and whether local writes were possible. This guide uses MySQL 8.4 terminology. Obtain read access to relevant status and variables. Capture timestamps with every set because active servers continue committing transactions while you inspect them. Keep incident evidence outside the database you are investigating.

Build the comparison

1. Read @@GLOBAL.gtid_executed and @@GLOBAL.gtid_purged from each relevant server. The first describes recorded executed history; the second helps identify history no longer present in binary logs. Record the values without editing internal GTID tables. Also preserve SHOW REPLICA STATUS and current channel errors.
2. Compare the sets in both directions. GTID_SUBTRACT(source_set, replica_set) identifies source history absent from the replica. Reversing the arguments identifies replica history absent from the source. For a read-only demonstration, use SELECT GTID_SUBTRACT('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-20','aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-18');. These UUIDs are illustrative placeholders, not values to inject into server configuration.
3. Investigate any unexpected replica-only transactions. They can reflect an earlier writer role, another legitimate replication channel, or accidental local changes. Compare change records, backup provenance, and topology history before calling them errors. A set difference says that histories differ; it does not explain the business meaning of the transactions.
4. Determine whether missing transactions are still available on an eligible source. Retained binary logs, not merely the existence of executed GTIDs, decide whether normal catch-up is possible. If required history is unavailable or divergent writes cannot be reconciled safely, select a supported reseed or rebuild procedure with a verified backup.

Interpret the evidence

Illustrative scenario

An old primary returns after a failover. It contains transactions absent from the current writer, while the current writer contains newer transactions from the replacement period. The operations team exports both sets and preserves the old server as evidence. It chooses a rebuild after business owners reconcile the isolated writes. Resetting GTID state would conceal the history without resolving those records.

Correction and verification

Never use an empty transaction, a forced GTID assignment, or a reset command merely to satisfy a set comparison. Those actions can change the bookkeeping while leaving data different. After the approved repair, confirm healthy replication, no new unexplained GTID differences, and application checks for the affected records. Large datasets may require targeted consistency checks beyond position matching.

Ongoing controls

Alert on writable replicas and persistent replication errors. Keep backup metadata, source identities, and retention requirements with the rejoin SOP. Recheck the procedure whenever channels are added or failover tooling changes. The useful operational result is an explained transaction history and a verifiable rejoin decision, not simply two similar-looking strings.

Explore Clouvenix database administration for implementation and operational handover.

Technical reference

• dev.mysql.com documentation
• dev.mysql.com documentation