MySQL 8.4 GTID replication: set up replicas and fail over safely

For most applications, the right first step beyond a single MySQL server is one source and one or two replicas using GTID-based replication. Replicas give you read capacity, a warm standby for failover and a safe place to run reports and backups. GTIDs (global transaction identifiers) make the topology easy to change: any replica can be pointed at any server without working out binary log file names and positions.

This article explains the architecture and its trade-offs, then gives a working setup for MySQL 8.4 LTS, including the replication statements that 8.4 removed. Syntax and defaults were checked against the 8.4 manual, and the defaults quoted were confirmed on a MySQL 8.4.11 server.

What replication does and does not give you

In standard asynchronous replication, the source writes each committed transaction to its binary log. Each replica's receiver (I/O) thread copies those events into a local relay log, and its applier (SQL) threads execute them. The source does not wait for replicas.

• It gives you read scaling, a standby for planned and unplanned failover, and an isolated copy for heavy reports and backup jobs.
• It does not give you a backup. A DROP TABLE or bad UPDATE replicates within seconds. Keep separate backups and binary logs for point-in-time recovery.
• It does not give you zero data loss by default. If the source crashes, transactions it committed but had not yet sent are missing on the replicas. Semisynchronous replication, below, narrows that gap.

Choosing a topology

The rest of this article covers the first two, which share the same setup.

How GTIDs work

Every transaction committed on a GTID-enabled server gets an identifier of the form source_id:transaction_id, for example 3E11FA47-71CA-11E1-9E33-C80AA9429562:23. The source_id is normally the originating server's server_uuid; the number is a sequence in commit order. MySQL 8.4 also accepts tagged GTIDs of the form source_id:tag:transaction_id.

Each server tracks the set of GTIDs it has executed in gtid_executed. With SOURCE_AUTO_POSITION = 1, a replica connecting to a source sends the GTIDs it already has, and the source sends everything else. A transaction is never applied twice on the same server. That is what makes failover simple: point a replica at a new source and let auto-positioning work out the difference.

Step 1: configure the servers

Binary logging is enabled by default in MySQL 8.4, but GTIDs are not: both gtid_mode and enforce_gtid_consistency default to OFF. Every server needs a unique server_id.

# Source my.cnf
[mysqld]
server_id                = 1
gtid_mode                = ON
enforce_gtid_consistency = ON

# Each replica my.cnf
[mysqld]
server_id                = 2          # unique per server
gtid_mode                = ON
enforce_gtid_consistency = ON
read_only                = ON
super_read_only          = ON         # also blocks accounts with admin privileges

enforce_gtid_consistency = ON rejects statements that cannot be logged safely with GTIDs: creating or dropping temporary tables inside a transaction, and transactions that update both transactional and non-transactional tables. Check your application and migrations for those before you switch it on.

Step 2: create the replication user

-- On the source
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'use-a-long-random-secret' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';

The privilege is still named REPLICATION SLAVE in 8.4. New accounts use the caching_sha2_password plugin by default, and a replica authenticating with it must either use an encrypted connection (SOURCE_SSL = 1) or request the source's RSA public key (GET_SOURCE_PUBLIC_KEY = 1). Encrypt the connection: replication traffic contains all your data.

Step 3: seed the replica and start replicating

A new replica must start from a consistent copy of the source's data, unless both servers are brand new and empty. Use a recent backup or a dump taken with GTID information, or MySQL's clone plugin. Then, on the replica:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST          = 'db-source.internal',
  SOURCE_PORT          = 3306,
  SOURCE_USER          = 'repl',
  SOURCE_PASSWORD      = 'use-a-long-random-secret',
  SOURCE_SSL           = 1,
  SOURCE_AUTO_POSITION = 1;

START REPLICA;
SHOW REPLICA STATUS\G

In the status output, Replica_IO_Running and Replica_SQL_Running should both be Yes, Last_IO_Error and Last_SQL_Error should be empty, and Retrieved_Gtid_Set and Executed_Gtid_Set show what has been received and applied.

Old statements no longer work in 8.4

MySQL 8.4 removed the deprecated MASTER/SLAVE replication statements; using them is now a syntax error. Update runbooks, scripts and monitoring checks:

Turning on GTIDs in an existing topology

If you already replicate by file and position, you can switch to GTIDs without stopping writes. gtid_mode can only move one step at a time, so the manual's online procedure is, on every server at each step:

1. SET @@GLOBAL.enforce_gtid_consistency = WARN; then run normal traffic and fix anything that logs a warning.
2. SET @@GLOBAL.enforce_gtid_consistency = ON;
3. SET @@GLOBAL.gtid_mode = OFF_PERMISSIVE; on all servers before moving on.
4. SET @@GLOBAL.gtid_mode = ON_PERMISSIVE; on all servers.
5. Wait until SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count' is 0, and until those transactions have replicated everywhere.
6. SET @@GLOBAL.gtid_mode = ON;, add both settings to my.cnf, then on each replica run STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1; START REPLICA;

Backups and binary logs taken before the switch contain transactions without GTIDs; take a fresh backup afterwards.

Semisynchronous replication

With semisync, a commit on the source returns only after at least one replica has written the transaction to its relay log and flushed it to disk. If the source then crashes, every transaction it acknowledged exists on at least one replica.

-- On the source
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

-- On each replica
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

Know the defaults. rpl_semi_sync_source_wait_point is AFTER_SYNC, meaning the source waits for the acknowledgment before committing to the storage engine, so no client sees a transaction that is not yet on a replica. rpl_semi_sync_source_wait_for_replica_count is 1. And if no replica acknowledges within rpl_semi_sync_source_timeout (10000 ms), the source silently falls back to asynchronous replication until a replica catches up. Alert on that fallback, or semisync gives you a guarantee you do not actually have.

Failover with GTIDs

A planned switchover looks like this:

1. Stop writes on the old source (SET GLOBAL super_read_only = ON) and wait for the chosen replica's Executed_Gtid_Set to match the source's gtid_executed.
2. On the new source: STOP REPLICA; RESET REPLICA ALL; then SET GLOBAL super_read_only = OFF; SET GLOBAL read_only = OFF;
3. On every other replica, and the old source: STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'new-source', SOURCE_AUTO_POSITION = 1; START REPLICA;
4. Move the application's write endpoint (DNS, proxy or connection string).

If the source has already purged binary logs containing transactions a replica still needs, replication refuses to start with ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS. The default binlog_expire_logs_seconds is 2592000 (30 days); keep binary logs at least as long as your longest expected replica outage. For replicas, MySQL 8.4 can also switch sources automatically: with SOURCE_CONNECTION_AUTO_FAILOVER = 1 (which requires auto-positioning) and a source list, a replica reconnects to an alternative source once its retries are exhausted. In 8.4 the default SOURCE_RETRY_COUNT is 10 with a 60-second SOURCE_CONNECT_RETRY.

Reads from replicas: the application side

• Lag is normal. Seconds_Behind_Source rising steadily means the replica cannot keep up. A value of 0 usually, but not always, means it has caught up.
• Read your own writes from the source. After a user saves something, read it back from the source, or wait for the replica with WAIT_FOR_EXECUTED_GTID_SET() using the GTID of the write.
• Give every table a primary key. Row-based replication finds rows to change by key; tables without one are slow to apply. sql_require_primary_key can enforce this for new tables.
• Use the parallel applier. In 8.4, replica_parallel_workers defaults to 4 and replica_preserve_commit_order is on, so replicas apply in parallel while committing in source order.

Monitoring checklist

• Both replication threads running, errors empty (SHOW REPLICA STATUS or performance_schema.replication_connection_status and replication_applier_status_by_worker).
• Lag below your application's tolerance, alerted on trend as well as value.
• Semisync status, if used, including fallbacks to async.
• Binary log retention longer than your longest plausible replica outage.
• A rehearsed, written failover runbook using the 8.4 statement names.

What to do next

Build the topology in a test environment first, with containers if that is quickest, and practise a switchover until it takes minutes and no guesswork. Then add the replica to production, move reporting queries to it, and only after that start sending application reads, beginning with pages that tolerate a few seconds of lag.