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* [email protected]:/u01/backup
[email protected]'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.