Setting up MySQL source-replica replication with GTIDs

In asynchronous replication one server, the source, writes every change to its binary log. A replica copies that log over the network (receiver or I/O thread) and applies it (applier or SQL thread). MySQL 8.x uses the terms source and replica; older documentation and some privilege names still say master and slave.

With GTIDs (global transaction identifiers) each transaction gets a unique ID, so the replica can find its own position automatically (SOURCE_AUTO_POSITION = 1) instead of you copying file names and offsets. This guide uses MySQL 8.4 syntax. MariaDB’s GTID implementation is different and not compatible, so use MariaDB’s own documentation for MariaDB.

Example hosts: source db1.example.com (10.0.0.11), replica db2.example.com (10.0.0.12), same MySQL version on both.

1. Configure the source

Every server in a topology needs a unique server_id. Binary logging is on by default in 8.x; naming it explicitly is still good practice. Add to the [mysqld] section and restart.

[mysqld]
server_id = 1
log_bin = binlog
gtid_mode = ON
enforce_gtid_consistency = ON

2. Configure the replica

[mysqld]
server_id = 2
log_bin = binlog
gtid_mode = ON
enforce_gtid_consistency = ON
skip_replica_start = ON
read_only = ON

skip_replica_start stops replication from starting on its own before you have finished the setup. read_only keeps ordinary application accounts from writing to the replica by accident.

3. Create the replication account on the source

CREATE USER 'repl'@'10.0.0.12' IDENTIFIED BY 'CHANGE_ME';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.12';

The account uses caching_sha2_password by default. The manual says such an account must connect over an encrypted connection or use RSA key exchange; otherwise the replica fails to authenticate. Step 5 covers both.

4. Copy the existing data to the replica

The replica must start with the same data the source had at a known GTID set. For small and medium databases, a consistent dump is the simplest way. With GTIDs on, mysqldump writes a SET @@GLOBAL.gtid_purged statement so the replica knows which transactions are already included.

# on the source
mysqldump -u root -p --all-databases --single-transaction \
  --routines --events --triggers --set-gtid-purged=ON > /tmp/full.sql

scp /tmp/full.sql [email protected]:/tmp/

Warning: loading an --all-databases dump overwrites the databases it contains on the target. Load it only into a freshly installed replica, and double-check the host name in your prompt first.

# on the new replica
mysql -u root -p < /tmp/full.sql

For very large databases a physical backup tool is faster than a dump; the replication steps stay the same.

5. Point the replica at the source

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.11',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'CHANGE_ME',
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY = 1;

START REPLICA;

GET_SOURCE_PUBLIC_KEY = 1 lets the replica fetch the source’s RSA public key for the password exchange. The manual notes that a trusted local copy of the key (SOURCE_PUBLIC_KEY_PATH) or an encrypted (TLS) replication connection is more secure; for traffic that leaves a trusted network, set up encrypted replication connections as described in the replication security chapter.

6. Check that it works

SHOW REPLICA STATUS\G

Look for these fields:

• Replica_IO_Running: Yes: the receiver thread is connected and reading the source’s binlog.
• Replica_SQL_Running: Yes: the applier thread is executing events.
• Last_IO_Error and Last_SQL_Error: should be empty. An authentication problem shows up in Last_IO_Error.
• Seconds_Behind_Source: 0 usually means caught up, though the manual notes it can also read 0 briefly when the network link has silently dropped.

On the source, SHOW PROCESSLIST shows a Binlog Dump thread for each connected replica. A quick end-to-end test:

-- on the source
CREATE DATABASE repl_test;

-- on the replica, a moment later
SHOW DATABASES LIKE 'repl_test';

-- on the source, clean up
DROP DATABASE repl_test;

Day-2 notes

• Replication is not a backup: a DROP TABLE on the source is replayed on the replica within seconds. Keep separate backups.
• Monitor Replica_IO_Running, Replica_SQL_Running and lag, and alert when either thread stops.
• Keep source and replica on the same MySQL version where possible; when upgrading, upgrade replicas first.
• Make sure the source keeps binary logs long enough for a replica that was offline to catch up.

Official documentation

• MySQL 8.4: Setting up replication using GTIDs
• MySQL 8.4: Creating a user for replication
• MySQL 8.4: Checking replication status