MySQL 8 GTID Replication: Setup and Troubleshooting

GTID-based replication replaces file-and-position bookkeeping with a global transaction identifier, which makes failover and topology changes far less error-prone: a replica simply asks for every transaction it has not executed. The flip side is that the failure modes are different from classic replication, and the rules around filters and skipping transactions surprise people who learned on MySQL 5.5. This guide sets up GTID replication on MySQL 8 and covers the errors you will actually hit.

How GTIDs change the model

• Every transaction gets a unique identifier of the form uuid:sequence_number, and each server tracks the set of identifiers it has executed or purged.
• SOURCE_AUTO_POSITION=1 replaces file/position: the replica requests missing transactions automatically, which survives topology changes that would break position-based replication.
• enforce_gtid_consistency restricts statements that cannot be replicated safely in GTID mode — this is the setting that produces most "my application query stopped replicating" incidents.
• Transactions without GTIDs cannot be used on a GTID-enabled server, which means old binary logs and old backups are not usable once you enable it.

Cold-start setup on both servers

# my.cnf on source and replica
[mysqld]
server_id             = 1                # unique per server
gtid_mode             = ON
enforce_gtid_consistency = ON
log_bin               = ON
log_replica_updates   = ON               # needed if this node becomes a source later
binlog_format         = ROW

# Create a dedicated replication user on the source
CREATE USER 'replicator'@'10.0.0.20' IDENTIFIED BY 'a_strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.0.20';
FLUSH PRIVILEGES;

SELECT @@gtid_mode, @@enforce_gtid_consistency, @@server_id;

Enable GTID mode with the servers stopped (or in a controlled sequence, since changing it on a live server requires the documented online procedure). Backups taken before GTIDs were enabled cannot be used afterwards, so take a fresh backup as soon as the change is in place.

Seed and point the replica

# On the replica: restore a fresh backup, then
mysql> STOP REPLICA;
mysql> RESET REPLICA ALL;

mysql> CHANGE REPLICATION SOURCE TO
    ->   SOURCE_HOST='10.0.0.10',
    ->   SOURCE_PORT=3306,
    ->   SOURCE_USER='replicator',
    ->   SOURCE_PASSWORD='a_strong_password',
    ->   SOURCE_AUTO_POSITION=1,
    ->   GET_SOURCE_PUBLIC_KEY=1;

mysql> START REPLICA;
mysql> SHOW REPLICA STATUS\G

On MySQL 8.0.22 and earlier the statement is CHANGE MASTER TO ... MASTER_AUTO_POSITION=1; both spellings exist in the wild, and reading the version first prevents a syntax error during a failover window.

Verification

-- On the source
SELECT @@gtid_executed\G
SHOW PROCESSLIST;                 -- one Binlog Dump thread per replica
SELECT * FROM performance_schema.replication_connection_status\G

-- On the replica
SELECT @@gtid_executed\G
SHOW REPLICA STATUS\G
-- Healthy looks like:
--   Replica_IO_Running: Yes
--   Replica_SQL_Running: Yes
--   Seconds_Behind_Source: 0 (or small and shrinking)
--   Auto_Position: 1
--   Retrieved_Gtid_Set / Executed_Gtid_Set growing

Retrieved_Gtid_Set larger than Executed_Gtid_Set means the SQL thread is behind; equal sets with a growing Seconds_Behind_Source usually points at a large transaction still being applied.

Common GTID errors and their fixes

That last row is worth its own paragraph. Replication filters such as replicate-do-db are evaluated against the default database of the session, not against the database being modified. A statement like USE appdb; CREATE TABLE otherdb.t (...); can therefore slip past a filter and fail on a replica that does not have otherdb — behaviour MySQL documents as intended, and a frequent source of confusion.

# Inspect the filter configuration
SHOW REPLICA STATUS\G | grep -i "Replicate_"
SELECT * FROM performance_schema.replication_applier_filters\G

# Skip a transaction you have decided to discard (verify the GTID first!)
STOP REPLICA;
SET GTID_NEXT='3f0a1c9c-1a2b-11ef-9c3f-0242ac110002:10523';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START REPLICA;

Skipping is a deliberate data-divergence decision — record it, and reconcile the affected table afterwards.

Monitoring the things that break

1. Replica lag: alert on Seconds_Behind_Source and on the growing GTID delta, not just the flag.
2. Binary log growth on the source: a stopped replica with no expiry means the source's disk fills. Set binlog_expire_logs_seconds and monitor it.
3. Replication thread down: alert on Replica_SQL_Running = No immediately — a stopped replica is invisible until failover day.
4. Consistency check after failover: promote with STOP REPLICA; RESET REPLICA ALL; and verify @@gtid_executed on the new source before repointing applications.

GTIDs turn replication from bookkeeping into a set difference. Configured with aligned modes, auto-position and a monitored lag metric, MySQL 8 replication is one of the least dramatic high-availability options available — which is exactly what you want at 3am.

Related Reading on This Site

• PostgreSQL 16 Streaming Replication with pg_basebackup
• DRBD 9 Resource Configuration and Quorum
• Keepalived VRRP and HAProxy Virtual IP Failover

原文链接：https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-howto.html (MySQL 8.0 Reference Manual - Setting Up Replication Using GTIDs)