+1 (541) 238-9429
QueryKings

Turning on GTIDs without downtime: the online migration path and what it costs

A surprising number of production MySQL topologies still replicate on file and position:

SHOW REPLICA STATUS\G
-- Source_Log_File: mysql-bin.004417
-- Read_Source_Log_Pos: 884512093
-- Auto_Position: 0

That works. It has worked for fifteen years. The cost shows up on the days you cannot afford it: promoting a replica means hand-computing a position on the new source, repointing every other replica, and hoping nobody typed a coordinate wrong at 3am. Orchestration tooling, RDS and Aurora inbound replication, Blue/Green Deployments, group replication, and CLONE all assume global transaction identifiers. If GTIDs are off, every one of those paths gets longer.

The good news is that enabling GTIDs on a running system is an online operation — has been since 5.7.6 — and it needs no restart and no write pause. The sequence is fussy rather than risky. Below is the order we run it in, and the three things that actually bite.

What a GTID buys you

A GTID is a source_uuid:transaction_id pair stamped on each transaction when it commits, and carried unchanged through every replica that applies it. The replica therefore knows, by identity rather than by byte offset, exactly which transactions it already has. Consequences worth the migration:

  • Repointing is one statement. CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1 and the replica negotiates its own starting point. No coordinates.
  • Failover becomes checkable. You can compare @@GLOBAL.gtid_executed across candidates and know who is ahead, instead of comparing log positions from different binlog files that are not comparable at all.
  • Divergence becomes visible. A transaction that exists on one server and nowhere else shows up as a GTID set difference. On file-and-position, the same divergence is silent until the data is wrong.

The cost is a small amount of extra binlog volume, a permanent restriction on a few statement shapes, and a migration you have to sequence correctly.

The restrictions you must clear first

enforce_gtid_consistency is the gate, and it is the part that needs application work rather than DBA work. With it on, MySQL refuses:

  • CREATE TABLE ... SELECT (it is one statement that logs as two transactions). Split it into CREATE TABLE LIKE plus INSERT ... SELECT.
  • CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE inside a transaction, when binlog_format is STATEMENT or MIXED. Move them outside the transaction, or run ROW.
  • Transactions that mix transactional and non-transactional tables — in practice, a leftover MyISAM or MEMORY table written in the same transaction as an InnoDB one.

Find out before you commit to a date. Set the warning mode on every server and let it run through a full business cycle, including the monthly batch jobs:

SET @@GLOBAL.enforce_gtid_consistency = WARN;

Violations land in the error log as Statement violates GTID consistency. A week of silence across weekday traffic, the nightly ETL, and the month-end reports is the evidence you need. A day of silence is not.

The state machine

gtid_mode moves one step at a time, and every server in the topology must reach a step before any server moves to the next. The intermediate states exist so that anonymous (non-GTID) transactions already in flight can drain.

OFF  ->  OFF_PERMISSIVE  ->  ON_PERMISSIVE  ->  ON
  • OFF — generates anonymous, accepts anonymous only.
  • OFF_PERMISSIVE — generates anonymous, accepts both.
  • ON_PERMISSIVE — generates GTIDs, accepts both.
  • ON — generates GTIDs, accepts GTIDs only.

The ordering rule is that no server may accept a transaction type that another server might still be sending it. That is why the whole fleet advances in lockstep.

Sequence

  1. On every server — source and all replicas — set consistency to enforcing once WARN has been clean:

    SET @@GLOBAL.enforce_gtid_consistency = ON;

  2. On every server, SET @@GLOBAL.gtid_mode = OFF_PERMISSIVE;

  3. On every server, SET @@GLOBAL.gtid_mode = ON_PERMISSIVE; From this moment new transactions carry GTIDs.

  4. Wait for the anonymous transactions to drain. This is the step people skip. On each server:

    SELECT @@GLOBAL.gtid_mode,
       VARIABLE_VALUE AS ongoing_anonymous
  FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

    Wait until it reads zero on every server, and then wait until every replica has applied everything that was generated before step 3 — the simplest proof is that each replica's Replica_SQL_Running_State is idle and Seconds_Behind_Source is 0 with no pending relay log. Only then has the last anonymous transaction finished its journey.

  5. On every server, SET @@GLOBAL.gtid_mode = ON;

  6. Switch each replica to auto-positioning, which is the only step that briefly stops replication:

    STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1;
START REPLICA;

  7. Persist it. The SET @@GLOBAL changes are runtime only; a restart reverts them. Write gtid_mode=ON and enforce_gtid_consistency=ON to the configuration file, or use SET PERSIST on 8.0 and later, and confirm with a rolling restart of one replica.

Total write pause: none. Total replication pause: the few seconds of step 6, per replica, one at a time.

On RDS and Aurora the same state machine is exposed as the gtid_mode and enforce_gtid_consistency parameters in a parameter group, which means the moves are applied by group modification rather than by SET, and the ordering discipline is yours to maintain by hand. Budget more wall-clock time there; the states still have to be reached in the same order.

Errant transactions, the trap at the end

Once GTIDs are on, anything written directly to a replica is stamped with that replica's UUID and exists nowhere else. That is an errant transaction, and it is harmless right up until that replica is promoted: every other replica then asks the new source for a GTID it has never heard of, and replication stops.

Find them before they find you:

-- on a replica
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, 'SOURCE-UUID:1-99999999') AS errant;

The usual culprits are a monitoring agent writing a heartbeat table, a schema change applied locally during an incident, or super_read_only not being set. Set super_read_only = ON on every replica and keep it there; read_only alone does not stop a user with SUPER, and in practice the account doing the damage always has SUPER.

Cleaning up an errant transaction means either replaying it on the source so it stops being unique, or injecting an empty transaction with that GTID on the servers that lack it so they agree to skip it. Decide which, deliberately, rather than reaching for RESET MASTER.

One more consequence

sql_slave_skip_counter stops working under GTIDs. Skipping a bad transaction is now done by setting gtid_next to the offending GTID, committing an empty transaction, and resetting gtid_next to AUTOMATIC. This is better, because the skip is explicit and recorded, but it is different enough that anyone who might be on call for a replication error should have run it once on a test host before they need it at night.

Is it worth it

If your topology is one source and one replica, and you have never failed over, GTIDs buy you little today and cost you an afternoon. If you expect to upgrade to 8.4, move to RDS or Aurora, run a replication-based cutover, or promote a replica under pressure, GTIDs are the precondition for every tool you will want to use, and the afternoon is cheaper now than during the cutover.

The migration itself is low-risk. The work that decides whether it goes well is the week of enforce_gtid_consistency = WARN beforehand, and the super_read_only discipline afterwards.

If you want a second pair of eyes on the sequence against your own topology — or you are staring at an errant transaction right now — get in touch. We work read-only first, and we will tell you if the answer is to leave it alone.