How to Set Up MySQL Multi-Source Replication (GTID) Step‑by‑Step

Purpose of Multi-Source Replication

MySQL 5.7.6 introduced Multi‑Source Replication, allowing a slave to replicate from multiple masters simultaneously, forming a “one‑slave‑multiple‑masters” topology.

Previously only one‑master‑multiple‑slaves was supported; achieving one‑slave‑multiple‑masters required custom solutions.

Use Cases

When databases are sharded across several servers, a reporting system can aggregate data by using multi‑source replication.

When multiple projects have separate MySQL servers, multi‑source replication can back up all of them to a single slave, avoiding the need for separate slaves or complex manual scripts.

Configuration

Multi‑source replication supports two methods: binlog‑position‑based and GTID‑based. The following steps use GTID.

Preparation

Set up three MySQL instances (example version 5.7.13) with IPs:

master1 192.168.31.221

master2 192.168.31.207

slave 192.168.31.158

Verify each instance has a unique server_uuid. If they are identical, edit auto.cnf in the data directory to change the UUID.

mysql> SHOW VARIABLES like '%server_uuid%';

Configure Masters

Add the following to my.cnf on both master1 and master2 under [mysqld]:

[mysqld]
...
log-bin=mysql-bin
server-id=221   # use a unique ID, e.g., last octet of IP
gtid_mode=on
enforce-gtid-consistency=true
...

server-id must be unique for each server.

Restart MySQL, then create a replication user on each master:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'master1'@'%' IDENTIFIED BY '123456';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'master2'@'%' IDENTIFIED BY '123456';

Configure Slave

Add to the slave’s my.cnf under [mysqld]:

server_id=207
log-bin=mysql-bin
read_only=on
gtid_mode=on
enforce-gtid-consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE

Restart MySQL, then register the two masters:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.31.221', MASTER_USER='master1', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-1';
mysql> CHANGE MASTER TO MASTER_HOST='192.168.31.207', MASTER_USER='master2', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-2';

Start Replication

mysql> START SLAVE;

Check the replication status: mysql> SHOW SLAVE STATUS\G; The output shows two channels, each with Slave_IO_Running: Yes and Slave_SQL_Running: Yes, confirming that multi‑source replication is working correctly.