Using GTID for Multi‑Source Replication in MySQL

The author, a certified MySQL and PostgreSQL professional, describes a scenario where data from four regional factories must be synchronized to an IDC. An earlier solution using Alibaba's Otter caused hundreds of idle connections and network congestion, prompting a switch to MySQL's native replication.

GTID (Global Transaction Identifier) replication is chosen because it simplifies single‑source setups, skips already executed transactions, and avoids inconsistencies caused by inaccurate position settings.

1. Export data

cd /data/backup
mysqldump -uroot -p -h192.168.100.1 --master-data=2 --single-transaction db01 >db01.sql
mysqldump -uroot -p -h192.168.100.2 --master-data=2 --single-transaction db02 >db02.sql
mysqldump -uroot -p -h192.168.100.3 --master-data=2 --single-transaction db03 >db03.sql
mysqldump -uroot -p -h192.168.100.4 --master-data=2 --single-transaction db04 >db04.sql

2. Import data and reset replication

use db01;
source /data/backup/db01.sql;
use db03;
source /data/backup/db03.sql;
use db04;
source /data/backup/db04.sql;
stop slave;
reset slave all;
reset master;  # clears local binlog, use with caution
show master status \G

3. Set GTID values

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '39e42c4d-876f-11ea-8229-286ed488e793:1-31,8a426026-b5e7-11ea-8816-0050568399c4:1-62183,f3d8b026-ba76-11ea-985d-000c29b82d1f:1-36244,39e42c4d-876f-11ea-8229-286ed488e793:1-31,5d7ef438-f249-11ea-a518-0894ef181fcf:1-5980,8a426026-b5e7-11ea-8816-0050568399c4:1-56548';

4. Configure master channels

change master to master_host='192.168.100.1', master_user='repl', master_password='123456', master_auto_position=1 for channel 'ims_guangzhou';
change master to master_host='192.168.100.3', master_user='repl', master_password='123456', master_auto_position=1 for channel 'ims_tianjin';
change master to master_host='192.168.100.4', master_user='repl', master_password='123456', master_auto_position=1 for channel 'ims_kunshan';
start slave;
show slave status \G

A test table created on the Guangzhou master (100.1) replicates to the IDC (100.5), confirming successful synchronization.

5. Add a new slave (Chongqing)

First obtain the latest GTID from the master, stop the slave, and set additional GTID values.

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'a97612c1-b947-11ea-bcf9-005056812835:1-19065,39e42c4d-876f-11ea-8229-286ed488e793:1-31,5d7ef438-f249-11ea-a518-0894ef181fcf:1-6107,8a426026-b5e7-11ea-8816-0050568399c4:1-62290,f3d8b026-ba76-11ea-985d-000c29b82d1f:1-36379';

change master to master_host='192.168.100.2', master_user='repl', master_password='123456', master_auto_position=1 for channel 'ims_chongqing';
start slave;

To verify, a table dropped on the master disappears on the IDC, confirming replication.

6. Summary and precautions

Stop all slaves before resetting GTID.

Run reset master to clear local GTID, then set GTID_PURGED as needed.

When using cascading replication, ensure downstream slaves are not lagging; backup binlogs before resetting master and manually copy any unsynchronized binlogs after adding new channels.

These steps ensure reliable GTID‑based multi‑source replication while avoiding common pitfalls such as binlog loss and replication breakage.