MySQL Multi-Source Replication – GTID Based Sources
In this blog post, we will see How to setup MySQL Multi-Source Replication – GTID Based Sources. For this demonstration, we will be using the below configuration.
We have already set up 2 mysql instance in test-machine01 server running on port 3306 (Server_ID : 1) & 3307 (Server_ID : 3) , We will replicate repl_test1 schema/DB from Master-1 & repl_test2 schema from Master-2 to Replica. We have also changed the mysql prompt to Master-1, Master-2, and Replica for easy understanding.
| Hostname | IP | Role | Server_ID | Installation Link |
| test-machine01 | 192.168.114.177 | Master-1 | 1 | Click Here |
| test-machine01 | 192.168.114.177 | Master-2 | 3 | Click Here |
| test-machine02 | 192.168.114.176 | Replica | 2 | Click Here |
Master-1: Below are the Master-1 configuration parameters
master1 >prompt mysql master1 >
PROMPT set to 'mysql master1 >'
mysql master1 >
mysql master1 >select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 1
socket=/var/lib/mysql/mysql.sock
pid_file=/var/run/mysqld/mysqld.pid
# Data Storage
datadir=/var/lib/mysql
innodb_directories="/u01/mysql/"
#Logging
log_bin = /var/lib/mysql/binlog
log_error = /var/lib/mysql/mysqld.log
expire_logs_days = 7
relay_log = /var/lib/mysql/relay_bin01
relay_log_index = /var/lib/mysql/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1
Master-2: Below are the Master-2 configuration parameters
mysql> prompt mysql master2 >
PROMPT set to 'mysql master2 >'
mysql master2 >
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
[mysqld@inst2]
#General
user = mysql
port=3307
server_id = 3
socket=/u01/mysql-2/mysql.sock
pid_file=/u01/mysql-2/mysqld.pid
# Data Storage
basedir=/u01/mysql-2
datadir=/u01/mysql-2/data
innodb_directories="/u01/mysql/"
plugin_dir=/usr/lib64/mysql/plugin
#Logging
log_bin = /u01/mysql-2/data/binlog
log_error = /u01/mysql-2/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-2/data/relay_bin01
relay_log_index = /u01/mysql-2/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1
Replica: Below are the Replica configuration parameters
mysql> prompt mysql replica >
PROMPT set to 'mysql replica >'
mysql replica >
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 2
bind-address = 0.0.0.0
socket=/u01/mysql-8/mysql.sock
pid_file=/u01/mysql-8/mysqld.pid
# Data Storage
basedir=/u01/mysql-8
datadir=/u01/mysql-8/data
innodb_directories="/u01/mysql-8;/u01/mysql"
#Logging
log_bin = /u01/mysql-8/data/binlog
log_error = /u01/mysql-8/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-8/data/relay_bin01
relay_log_index = /u01/mysql-8/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates=1
read-only =1
super_read_only =0
Step 1. Create Replication User in Master Servers: Create user repluser in both Master-1 & Master-2, This user will be used by Replica to connect to both the Sources.
mysql master1 >CREATE USER IF NOT EXISTS 'repluser'@'%' IDENTIFIED BY 'Password123#@!';
Query OK, 0 rows affected (0.06 sec)
mysql master1 >GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql master1 >ALTER USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';
Query OK, 0 rows affected (0.00 sec)
mysql master1 >flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql master1 >
mysql master2 >CREATE USER IF NOT EXISTS 'repluser'@'%' IDENTIFIED BY 'Password123#@!';
Query OK, 0 rows affected (0.06 sec)
mysql master2 >GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql master2 >ALTER USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';
Query OK, 0 rows affected (0.00 sec)
mysql master2 >flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql master2 >
Step 2. Take Backup from Master Server: Take the backup of repl_test1 & repl_test2 schema/DB, Please note we are using option –single-transaction to get consistent backup and –set-gtid-purged to get gtid transaction number.
[root@test-machine01 backup]# mysqldump -hlocalhost -uroot -p --single-transaction --triggers --routines --set-gtid-purged=ON --databases repl_test1 > /u01/backup/repl_test1.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@test-machine01 backup]#
[root@test-machine01 backup]# mysqldump -hlocalhost -uroot -S/u01/mysql-2/mysql.sock -p --single-transaction --triggers --routines --set-gtid-purged=ON --databases repl_test2 > /u01/backup/repl_test2.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@test-machine01 backup]#
[root@test-machine01 ~]# cd /u01/backup
[root@test-machine01 backup]# ls -ltr
total 16
-rw-r--r--. 1 root root 1801 Mar 11 11:50 repl_test1.sql
-rw-r--r--. 1 root root 1801 Mar 11 11:51 repl_test2.sql
[root@test-machine01 backup]#
Step 3. Take note of GTID_PURGED from dumpfile: Use the below command to read GTID_PURGED value from the dumpfile and take a note of it. And remove it from the dump file using sed and d command.
[root@test-machine01 backup]# cat /u01/backup/repl_test1.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''
13ecba9c-444a-11eb-a397-000c29f9d9e6:1-4
[root@test-machine01 backup]#
[root@test-machine01 backup]# cat /u01/backup/repl_test2.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''
98411332-6aad-11eb-809a-000c29f9d9e6:1-5
[root@test-machine01 backup]#
[root@test-machine01 backup]# sed '/GTID_PURGED/d' /u01/backup/repl_test1.sql > /u01/backup/repl_test1_nopurge.sql
[root@test-machine01 backup]# sed '/GTID_PURGED/d' /u01/backup/repl_test2.sql > /u01/backup/repl_test2_nopurge.sql
Step 4. Import dumpfiles in Replica: scp dumpfile to replica server and import it.
[root@test-machine01 backup]# scp *nopurge* root@192.168.114.176:/u01/backup
root@192.168.114.176's password:
repl_test1_nopurge.sql 100% 1717 509.9KB/s 00:00
repl_test2_nopurge.sql 100% 1717 555.5KB/s 00:00
[root@test-machine01 backup]#
[root@test-machine01 backup]#
[root@test-machine02 backup]#
[root@test-machine02 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-8/mysql.sock < repl_test1_nopurge.sql
Enter password:
[root@test-machine02 backup]#
[root@test-machine02 backup]# mysql -hlocalhost -uroot -p -S/u01/mysql-8/mysql.sock < repl_test2_nopurge.sql
Enter password:
[root@test-machine02 backup]#
[root@test-machine02 backup]#
mysql replica >show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| repl_test1 |
| repl_test2 |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql replica >
Step 5. Update GTID_PURGED value in Replica: Update GTID_PURGED value we recorded above from both dumpfiles.
mysql replica >RESET MASTER;
Query OK, 0 rows affected (0.07 sec)
mysql replica >SET @@GLOBAL.gtid_purged = "13ecba9c-444a-11eb-a397-000c29f9d9e6:1-4, 98411332-6aad-11eb-809a-000c29f9d9e6:1-5";
Query OK, 0 rows affected (0.00 sec)
mysql replica >select @@GLOBAL.gtid_purged
-> ;
+------------------------------------------------------------------------------------+
| @@GLOBAL.gtid_purged |
+------------------------------------------------------------------------------------+
| 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-4,
98411332-6aad-11eb-809a-000c29f9d9e6:1-5 |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql replica >
Step 6. Update Both Master info in Replica: Use the below command to update Master info in replica and REPLICATE_WILD_DO_TABLE to filter repl_test1 & repl_test2 DB changes and to replicate from master to replica.
mysql replica > CHANGE MASTER TO MASTER_HOST='192.168.114.177',MASTER_USER='repluser',MASTER_PASSWORD='Password123#@!',MASTER_PORT=3306,MASTER_AUTO_POSITION=1 FOR CHANNEL "repltest_1";
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql replica > CHANGE MASTER TO MASTER_HOST='192.168.114.177',MASTER_USER='repluser',MASTER_PASSWORD='Password123#@!',MASTER_PORT=3307,MASTER_AUTO_POSITION=1 FOR CHANNEL "repltest_2";
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql replica > CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('repl_test1.%') FOR CHANNEL "repltest_1";
Query OK, 0 rows affected (0.00 sec)
mysql replica > CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('repl_test2.%') FOR CHANNEL "repltest_2";
Query OK, 0 rows affected (0.00 sec)
mysql replica >
Step 7. Start Replica and check status: Use the below commands to start and check the status of the replica for both channels.
mysql replica >
mysql replica >START REPLICA FOR CHANNEL "repltest_1";
Query OK, 0 rows affected (0.00 sec)
mysql replica >START REPLICA FOR CHANNEL "repltest_2";
Query OK, 0 rows affected (0.00 sec)
mysql replica >
mysql replica >SHOW REPLICA STATUS FOR CHANNEL "repltest_1"\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 192.168.114.177
Source_User: repluser
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000003
Read_Source_Log_Pos: 1042
Relay_Log_File: relay_bin01-repltest_1.000007
Relay_Log_Pos: 409
Relay_Source_Log_File: binlog.000003
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: repl_test1.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 1042
Relay_Log_Space: 625
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 13ecba9c-444a-11eb-a397-000c29f9d9e6
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-5,
98411332-6aad-11eb-809a-000c29f9d9e6:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: repltest_1
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
mysql replica >SHOW REPLICA STATUS FOR CHANNEL "repltest_2" \G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 192.168.114.177
Source_User: repluser
Source_Port: 3307
Connect_Retry: 60
Source_Log_File: binlog.000001
Read_Source_Log_Pos: 3078
Relay_Log_File: relay_bin01-repltest_2.000006
Relay_Log_Pos: 2098
Relay_Source_Log_File: binlog.000001
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: repl_test2.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 3078
Relay_Log_Space: 2314
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 3
Source_UUID: 98411332-6aad-11eb-809a-000c29f9d9e6
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 98411332-6aad-11eb-809a-000c29f9d9e6:6-7
Executed_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-5,
98411332-6aad-11eb-809a-000c29f9d9e6:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: repltest_2
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql replica >
mysql replica >SELECT * FROM performance_schema.replication_connection_status where channel_name='repltest_1' \G;
*************************** 1. row ***************************
CHANNEL_NAME: repltest_1
GROUP_NAME:
SOURCE_UUID: 13ecba9c-444a-11eb-a397-000c29f9d9e6
THREAD_ID: 42
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 19
LAST_HEARTBEAT_TIMESTAMP: 2021-03-11 13:07:41.390848
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)
ERROR:
No query specified
mysql replica >SELECT * FROM performance_schema.replication_connection_status where channel_name='repltest_2' \G;
*************************** 1. row ***************************
CHANNEL_NAME: repltest_2
GROUP_NAME:
SOURCE_UUID: 98411332-6aad-11eb-809a-000c29f9d9e6
THREAD_ID: 44
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 20
LAST_HEARTBEAT_TIMESTAMP: 2021-03-11 13:07:41.390772
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)
ERROR:
No query specified
mysql replica >
Step 8. Test Replication: Perform some transaction on both master Master-1 & Master-2 and check same transactions are replicated in Replica Server.
Master-1
mysql master1 >select database();
+------------+
| database() |
+------------+
| repl_test1 |
+------------+
1 row in set (0.00 sec)
mysql master1 >create table repl_test1 as select * from repl_test.repl_test1;
Query OK, 20 rows affected (0.04 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql master1 >show tables;
+----------------------+
| Tables_in_repl_test1 |
+----------------------+
| repl_test1 |
+----------------------+
2 rows in set (0.00 sec)
mysql master1 >
Master-2
mysql master2 >select database();
+------------+
| database() |
+------------+
| repl_test2 |
+------------+
1 row in set (0.00 sec)
mysql master2 >create table repl_test2 as select * from repl_test.repl_test1;
Query OK, 20 rows affected (0.08 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql master2 >show tables;
+----------------------+
| Tables_in_repl_test2 |
+----------------------+
| repl_test2 |
+----------------------+
2 rows in set (0.00 sec)
mysql master2 >
Replica
mysql replica >select database();
+------------+
| database() |
+------------+
| repl_test1 |
+------------+
1 row in set (0.00 sec)
mysql replica >show tables;
+----------------------+
| Tables_in_repl_test1 |
+----------------------+
| repl_test1 |
+----------------------+
1 row in set (0.00 sec)
mysql replica >
mysql replica >select database();
+------------+
| database() |
+------------+
| repl_test2 |
+------------+
1 row in set (0.00 sec)
mysql replica >show tables;
+----------------------+
| Tables_in_repl_test2 |
+----------------------+
| repl_test2 |
+----------------------+
2 rows in set (0.00 sec)
mysql replica >
Reference: https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.
