MySQL Multi Source Master Slave Replication using GTID

We are using GTID-based master-slave replication. In this replication, both master and slave should have the same GTID. This blog post aims to provide a step-by-step guide to help you set up MySQL Replication using GTIDs and help you replicate your MySQL data with ease.

Prerequisite

Master Source 1

1. Channel name: DEVELOPMENT
2. Database: mydb1
3. Enable Replication For Tables
1. testing1
2. testing2
3. testing3
4. Mysql Server Running: 192.168.0.201:3307

Master Source 2

1. Channel name: PRODUCTION
2. Database: mydb1
3. Enable Replication For Tables
1. testing4
2. testing5
3. testing6
4. Mysql Server Running: 192.168.0.201:3308

Slave

1. Mysql Server Running: 192.168.0.209:3306

Configuration of  masters(192.168.0.201:3307/192.168.0.201:3308) and slave (192.168.0.209:3306)

my.cnf config for master (192.168.0.201:3307)

my.cnf config for master (192.168.0.201:3308)

my.cnf config for slave (192.168.0.209:3306)

Steps for taking a dump and restoring it

Run the following commands in both masters 192.168.0.201:3307 and 192.168.0.201:3308

1. Dump the database on the master: mysqldump --set-gtid-purged=OFF -u root -p mydb1 > backup.sql
2. Restore the database on the slave: 

CREATE DATABASE mydb1;
mysql -u root -p mydb1 < backup.sql

Step to enable replication on the master

Run the following commands in both masters 192.168.0.201:3307 and 192.168.0.201:3308

1. Check GTID is ON: SHOW VARIABLES LIKE 'gtid_mode';
2. Create a replication user on the master: CREATE USER 'replication_user'@'192.168.0.209' IDENTIFIED WITH mysql_native_password BY 'password';
3. Grant replication privileges to the user: GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.0.209'; FLUSH PRIVILEGES;
4. Show the master status: SHOW MASTER STATUS\G;
5. Disable WRITE operation on master till we enable replication: SET @@GLOBAL.read_only = ON;

Steps to enable replication on the slave

Run the following commands in the slave 192.168.0.209:3306 server node.

1. Check GTID is ON: SHOW VARIABLES LIKE 'gtid_mode';
2. Stop and reset the slave on the slave server: 
   STOP REPLICA FOR CHANNEL "DEVELOPMENT"; 
   RESET REPLICA FOR CHANNEL "DEVELOPMENT";
3. Add GTID on the slave for channel DEVELOPMENT: SET GLOBAL gtid_purged='ccb0d141-dc54-11ee-923c-0242c0a8b005:1-33'; <!--- Should be same as master 192.168.0.201:3307 --->
4. Change the master settings on the slave for channel DEVELOPMENT: CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.201', SOURCE_PORT=3307, SOURCE_USER='replication_user', SOURCE_PASSWORD='password', SOURCE_AUTO_POSITION=1 FOR CHANNEL "DEVELOPMENT";
5. Create table filters for replication for DEVELOPMENT channel: CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (mydb1.testing1, mydb1.testing2, mydb1.testing3) FOR CHANNEL "DEVELOPMENT";
6. Start the slave for the specified channel: START REPLICA FOR CHANNEL "DEVELOPMENT";
7. Show the slave status for the specified channel: SHOW REPLICA STATUS FOR CHANNEL "DEVELOPMENT"\G;
8. Add GTID on the slave for channel PRODUCTION: SET GLOBAL gtid_purged='ccb0d141-dc54-11ee-923c-0242c0a8b005:1-33,180c0583-e1e0-11ee-84fa-0242c0a84003:1-107'; <!--- Add another GTID with existing and should be same as master 192.168.0.201:3308 --->
9. Change the master settings on the slave for channel PRODUCTION: CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.201', SOURCE_PORT=3308, SOURCE_USER='replication_user', SOURCE_PASSWORD='password', SOURCE_AUTO_POSITION=1 FOR CHANNEL "PRODUCTION";
10. Create table filters for replication for the PRODUCTION channel: CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (mydb1.testing4, mydb1.testing5, mydb1.testing6) FOR CHANNEL "PRODUCTION";
11. Start the slave for the specified channel: START REPLICA FOR CHANNEL "PRODUCTION";
12. Show the slave status for the specified channel: SHOW REPLICA STATUS FOR CHANNEL "PRODUCTION"\G;
13. Disable Read Only permission on both master: SET @@GLOBAL.read_only = OFF;

To verify replication working

1. Check masters GTID present in slave: show global variables like 'gtid_executed'; <!---Should be same as master--->
2. Get detailed information about the replication applier status, including GTID-related information.
   SELECT * FROM performance_schema.replication_applier_status_by_worker;
3. Check replication_user is present in the master: SELECT User, Host FROM mysql.user WHERE User = 'replication_user';

Thank you! 😀 Hope you like my bog post.