GTID-Based Replication, Auto-Positioning, Failover Readiness, and Errant Transactions

Learning outcomes

File names such as mysql-bin.000123 and byte positions are useful, but they describe storage locations rather than transaction identity. A Global Transaction Identifier (GTID) gives each binary-logged transaction a topology-wide identity. That lets a replica tell a candidate source “these are the transactions I already executed” and request what it is missing. This lesson turns that idea into failover reasoning.

GTID identity versus binary-log location

A GTID looks conceptually like source_uuid:transaction_number. The UUID identifies the server on which the transaction originally committed; the sequence number identifies that transaction within the source's generated sequence. A GTID set compactly represents many such identifiers and ranges. @@GLOBAL.gtid_executed records transactions this server has executed; @@GLOBAL.gtid_purged records executed GTIDs whose binary-log events are no longer present locally.

Observe the current GTID state

SHOW GLOBAL VARIABLES WHERE Variable_name IN  ('gtid_mode','enforce_gtid_consistency','server_uuid','server_id');SELECT @@GLOBAL.gtid_executed AS executed,       @@GLOBAL.gtid_purged   AS purged;SHOW REPLICA STATUS\G

With SOURCE_AUTO_POSITION=1, the replica reconnects using GTID history instead of a manually supplied source log file/position. The source determines which transactions the replica lacks. This is powerful during source changes, but it cannot manufacture missing history: if the replica needs a GTID whose event has already been purged from the candidate source, you need another source or a new provisioning operation.

Use set arithmetic for failover readiness

-- On the replica, compare source-originated transactions received-- with transactions this server has actually executed.SET @received = (  SELECT RECEIVED_TRANSACTION_SET  FROM performance_schema.replication_connection_status  WHERE CHANNEL_NAME='');SELECT GTID_SUBTRACT(@received, @@GLOBAL.gtid_executed) AS received_not_yet_executed,       GTID_SUBTRACT(@@GLOBAL.gtid_executed, @received) AS executed_not_received;-- Also capture @@GLOBAL.gtid_executed on the source in a second session-- when making an actual promotion decision.

For a simple one-source topology with a read-only replica, a normal caught-up replica should contain the source's required executed history and should not carry unexplained local transactions. In richer topologies, sets can legitimately include transactions from multiple origins, so compare against the intended topology—not a simplistic string-equality rule.

Errant transactions: a replica can have history the source never had

An errant transaction is a locally executed GTID on a server that should have been receiving that history from elsewhere. It may be a harmless administrator test, or it may represent real data divergence. Either way, it complicates promotion and rejoin decisions because GTID auto-positioning assumes transaction identities describe meaningful shared history.

STOP REPLICA SQL_THREAD;SET GLOBAL super_read_only = OFF;CREATE DATABASE IF NOT EXISTS servicehub_errant_lab;CREATE TABLE IF NOT EXISTS servicehub_errant_lab.note (  id INT PRIMARY KEY,  note VARCHAR(100) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_errant_lab.note VALUES (1,'intentional replica-only transaction');SET GLOBAL super_read_only = ON;START REPLICA SQL_THREAD;SELECT @@GLOBAL.gtid_executed AS replica_gtids;

Because this lab replica began empty and has received the source history through one channel, its RECEIVED_TRANSACTION_SET is a useful local baseline for detecting the intentionally local transaction:

SET @received = (  SELECT RECEIVED_TRANSACTION_SET  FROM performance_schema.replication_connection_status  WHERE CHANNEL_NAME='');SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, @received)       AS executed_not_received_from_source;

A nonempty result is evidence that the replica has executed GTIDs outside the source set. The corresponding data might or might not overlap ServiceHub data. The operator must investigate both transaction identity and data effects.

The tempting but unsafe “fixes”

For this disposable lab, the safest repair is to destroy and reprovision the replica from a known source state. Because the source still retains the complete small lab history, an empty replacement replica can use GTID auto-positioning. In production, a new backup, MySQL Clone, or another known-good provisioning source may be required if necessary binary logs have expired.

# The simplest safe reset for this fully disposable two-node lab# is to destroy BOTH lab volumes and rebuild from the documented setup.docker compose down -vdocker compose up -d# Re-run the Lesson 1 source schema/account setup, then configure# the replica with SOURCE_AUTO_POSITION=1 and verify GTID/data invariants.

GTID auto-positioning is not provisioning

Auto-positioning chooses which logged transactions to transmit; it does not copy an arbitrary current database image. A brand-new empty replica can catch up only when the source still retains all required transactions and the topology is small enough for that replay to be practical. Real systems commonly provision a baseline with a consistent backup or clone, then use GTIDs for incremental catch-up and future source changes.

What auto-positioning actually negotiates

When a GTID replica connects with SOURCE_AUTO_POSITION=1, it does not ask for “the newest binary log.” It tells the source which GTIDs it already has. The source compares that history with transactions it can still provide and streams the missing ones. This changes failover from location bookkeeping to set reconciliation: the important question becomes which transactions exist on each server, not whether both happen to name a file mysql-bin.000007.

There are two separate failure classes. If the candidate replica is merely behind and the source still retains every missing GTID, auto-positioning can catch it up. If the source has already purged binary-log events for a GTID the replica needs, there is no magic network retry that reconstructs those row changes. The correct response is to find another source that still has the history or provision a new baseline from backup/clone and then resume GTID catch-up.

This is why binary-log retention is a topology parameter as well as a PITR parameter. A seven-day log window may be generous for a replica that is normally seconds behind, but maintenance, network isolation, or a failed disk can leave a node offline longer than expected. The retention policy must match the longest credible outage you want a replica to catch up from without reprovisioning.

Provisioning and replication are different operations

A replica needs both a baseline image and a change stream. In this tiny lab, an empty replica can replay the source's complete retained history because only a small number of transactions exist. Production systems usually provision the current database state first, using a consistent backup or MySQL Clone where appropriate, and only then use replication to transfer changes after that point.

A common operational mistake is to configure SOURCE_AUTO_POSITION=1 on a server whose data was copied from an unknown time and assume GTID negotiation will repair arbitrary differences. It cannot infer which rows were copied incorrectly or whether local writes occurred with binary logging disabled. Provisioning needs its own provenance: backup boundary, GTID metadata, checksums/business validation, tool versions, and a documented restore/clone procedure.

After provisioning, wait for the replica to execute everything it has received before evaluating it as a failover candidate. On the default channel, the following creates a local catch-up barrier without inventing file positions.

SET @received = (  SELECT RECEIVED_TRANSACTION_SET  FROM performance_schema.replication_connection_status  WHERE CHANNEL_NAME='');SELECT WAIT_FOR_EXECUTED_GTID_SET(@received, 10) AS caught_up_to_received;SELECT GTID_SUBTRACT(@received, @@GLOBAL.gtid_executed)       AS still_received_but_not_executed;

Source switch rehearsal: identity does not remove fencing

GTIDs simplify choosing a start point when a replica changes sources, but they do not make failover coordination automatic. Imagine source A is unhealthy and replica B will become the writer. If A can still accept writes while applications are redirected to B, the topology can create two independent transaction histories. GTID uniqueness helps you detect those histories later; it does not prevent the split.

A controlled switch therefore begins with fencing or quiescing the old source, records its final reachable GTID state, confirms B has executed the accepted history, validates business invariants, and only then changes B's write policy. Any remaining replicas can be repointed with auto-positioning after their data/GTID relationship to the new source is understood.

The phrase failover ready should therefore mean more than SOURCE_AUTO_POSITION=1. It includes compatible data, known filter/delay policy, sufficient retained history, working credentials/TLS, no unexplained errant transactions, and a tested procedure for stopping the old writer.

Production judgment

Before failover, capture candidate GTID sets, source/replica worker state, lag evidence, and application invariants. After promotion, preserve the old source until you understand whether it contains transactions absent from the promoted candidate. Rejoining an old primary as a replica without this analysis can overwrite assumptions about which history is authoritative.

Authoritative references

• MySQL 8.4 — Replication with GTIDs
• MySQL 8.4 — GTID Format and Storage
• MySQL 8.4 — GTID Auto-Positioning
• MySQL 8.4 — GTID Functions
• MySQL 8.4 — Cloning for Replication