Replicating data between two MySQL Group Replication sets using “regular” asynchronous replication with Global Transaction Identifiers (GTID’s)
March 29, 2019 4 Comments
MySQL introduced Group Replication (GR) in version 5.7, and GR is part of the InnoDB Cluster high-availability solution. InnoDB Cluster consists of Group Replication, MySQL Shell and MySQL Router.
I am not going to explain InnoDB Cluster or Group Replication in this post. So, if you aren’t familiar with either one, I have some previous posts in which I have explained how to work with both. See:
MySQL 8.0 Group Replication – Three-server installation
MySQL 8.0 InnoDB Cluster – Creating a sandbox and testing MySQL Shell, Router and Group Replication
Adding a replicated MySQL database instance using a Group Replication server as the source
A customer wanted to know how to replicate data between two separate three-node Group Replication groups. They have two data centers in different states, and they want to use each GR group for a different application. And, they wanted to replicate the data between the two (for various reasons).
For Group Replication (GR), you must have a minimum of three nodes in the group, and you can have a maximum of nine nodes. The GR group can be either in single-primary or multiple-primary mode. A five-node Group Replication Group looks like this:
For this (fictional) example, I will be using two three-node Group Replication groups. One group is in Atlanta, Georgia, and the other is in Tampa, Florida. Besides the standard group replication requirements, there are a couple of items you need to take into consideration if you decide to try this. First, this will probably not work very well if you have a very write-heavy application. Group Replication does support multi-threading, but the replicated databases may not always be up-to-date with the source database. Also, please note that you need will need a very good network connection between the two groups. And, if one GR group completely fails, there is a good chance some of the data might not have had time to replicate over to the other group. Just remember – your replication performancee may vary. For this post, I am using six virtual machines on a single server.
The topology so far…
To replicate the data between two groups, you only need to use use “regular” MySQL asynchronous replication with Global Transaction Identifiers (GTIDs)
Note: To learn more about replicating with GTIDs, I wrote two posts explaining the “how to” – see part one and part two). GTID’s are required when using Group Replication.
For the Atlanta group, I have three instances of MySQL (version 8.0.15) with IP addresses 192.168.1.151, 192.168.1.152 and 192.168.1.153. For the Tampa group, I have three instances of MySQL (version 8.0.15) with IP addresses 192.168.1.161, 192.168.1.162 and 192.168.1.163. The groups look like this:
With our two three-node groups, I will use the primary-write node in the first group to be the source (master) database for the primary-write node (replica/slave) in the second group – and vice-versa.
In the Atlanta group, I will use the primary-write node with the IP address of 192.168.1.151 (MEMBER_HOST MacVM151.local) to be the source (master) database for the primary-write in the Tampa group (which will be the slave with IP address 192.168.1.161).
In the Tampa group, I will use the primary-write node with the IP address of 192.168.1.161 (MEMBER_HOST MacVM161.local) to be the source (master) database for the primary-write in the Tampa group (which will be the slave with IP address 192.168.1.151).
The other nodes in both groups are set to SUPER READ-ONLY, so they can’t be used as a replica (slave) database source, as the replica (slave) needs to be able to perform writes. The topology will looks something like this – with the arrows showing the way the data is replicated (or “flows”):
If that is too confusing, here is a simplified layout: (ATL = Atlanta, TPA = Tampa)
The members of each group
In the Atlanta Group, I have the following members in the Group Replication group:
mysql> SELECT * FROM performance_schema.replication_group_members order by member_host; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local | 3306 | ONLINE | SECONDARY | 8.0.15 | | group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local | 3306 | ONLINE | PRIMARY | 8.0.15 | | group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local | 3306 | ONLINE | SECONDARY | 8.0.15 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
In this group, I want the first node in the list (MEMBER_HOST MacVM151.local) to be the primary, but for some reason, the second node (MacVM152.local) is the primary. To change the PRIMARY to be the first server in the list, I can issue this command:
SELECT group_replication_set_as_primary(‘member_uuid’); (where the member_uuid is equal to the MEMBER_ID of the first node (‘c727957e-4cb6-11e9-abd5-f80a484a9c32’) from the above output.)
mysql> SELECT group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32') |
+--------------------------------------------------------------------------+
| Primary server switched to: c727957e-4cb6-11e9-abd5-f80a484a9c32 |
+--------------------------------------------------------------------------+
1 row in set (0.02 sec)
I can verify this change by checking the members again (notice the MEMBER_ROLE column):
mysql> SELECT * FROM performance_schema.replication_group_members order by member_host; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local | 3306 | ONLINE | PRIMARY | 8.0.15 | | group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local | 3306 | ONLINE | SECONDARY | 8.0.15 | | group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local | 3306 | ONLINE | SECONDARY | 8.0.15 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
The Tampa Group is ready to go – as 192.168.1.161 is the PRIMARY server:
Tampa Group:
mysql> SELECT * FROM performance_schema.replication_group_members order by member_host; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | fdc89b12-50d7-11e9-bfa8-012cdcc95c70 | MacVM161.local | 3306 | ONLINE | PRIMARY | 8.0.15 | | group_replication_applier | 1ef93b16-50d8-11e9-b7da-7e47ebc51826 | MacVM162.local | 3306 | ONLINE | SECONDARY | 8.0.15 | | group_replication_applier | 46012962-50d8-11e9-8dc0-de7edddaaccd | MacVM163.local | 3306 | ONLINE | SECONDARY | 8.0.15 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
(Note: Yes, I could have used the second node with IP of 192.168.1.152, but I wanted to use the first node in each group)
Checking the state of each group
I am starting with a clean install of MySQL on all six instances. You can still do this if you have existing data in your groups, but you will need to get the other group to have a beginning set of data. When you begin replication, it won’t automatically export/backup the data and restore/import it to the other group. You will have to backup and restore the data from each group, so the new replicated server will have a starting point at a particular GTID. (I explain how to do this in this post – search for “What if my Group Replication (GR) group already has data?”).
I can see from the GET MASTER STATUS\G on both groups under the Executed_Gtid_Set that I have executed seven and three transactions on the two GR groups, respectively (these transactions were from creating and modifying the group replication views):
Atlanta Group
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 2217
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)
Tampa Group
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1515
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
1 row in set (0.00 sec)
But, if you want to look at the transactions (which are stored in the binary log mysql-bin.000001 on each server) for each group to verify that you didn’t have any transactions which changed data, you can use the mysqlbinlog tool:
# mysqlbinlog mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 .... SET TIMESTAMP=1553731529/*!*/; COMMIT ....
I checked both binary logs, and all of the transactions had to do with changing the state of the group replication view. All of these transactions were SET TIMESTAMP transactions (“SET TIMESTAMP=1553731529/*!*/;”). I am now ready to start replication.
Let’s get started
As shown in the above topology images, I am going to setup the Atlanta node (MacVM151.local – IP address 192.168.1.151) to be a source (master) for the Tampa node (MacVM161.local – IP address 192.168.1.161). Since I already have a replication user created for Group Replication, I can use the same user for this “regular” asynchronous replication. If you want more details on asynchronous replication using GTID’s – see this post.
Since I already have a replication channel open with the Group Replication, I will need to specify a new channel. I will name the channel “atl_tpa_replication” (for “Atlanta to Tampa replication”), meaning the Atlanta server will be the source (master) and the Tampa server will be the replica (slave). In replication, it is the job of the replica (slave) to contact the source (master) and retrieve all of the transactions (data) that has not been applied to its database. I will open a MySQL prompt on the Tampa instance with the IP address of 192.168.1.161, and issue this CHANGE MASTER TO statement:
On Tampa
CHANGE MASTER TO MASTER_HOST = '192.168.1.151', MASTER_PORT = 3306, MASTER_USER = 'rpl_user', MASTER_PASSWORD = 'R3plic4tion!', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'atl_tpa_replication';
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.1.151',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'rpl_user',
-> MASTER_PASSWORD = 'R3plic4tion!',
-> MASTER_AUTO_POSITION = 1
-> FOR CHANNEL 'atl_tpa_replication';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)
I can now start the slave for the “atl_tpa_replication” replication channel, and take a look at the “slave status”:
mysql> start slave for channel 'atl_tpa_replication';
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status for channel 'ATL_TPA_REPLICATION'\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.151
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 893
Relay_Log_File: MacVM161-relay-bin-atl_tpa_replication.000004
Relay_Log_Pos: 1099
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 893
Relay_Log_Space: 1919
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 151
Master_UUID: c727957e-4cb6-11e9-abd5-f80a484a9c32
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: atl_tpa_replication
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
On the Atlanta group, there were seven transactions which had been applied (GTIDs of 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) – and I can tell this from when I did a “SHOW MASTER STATUS\G” from the Atlanta primary-write server (192.168.1.151):
On Atlanta
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 2568
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)
I can see which GTID’s I have retrieved (Retrieved_Gtid_Set) and executed (Executed_Gtid_Set) from the SHOW SLAVE STATUS\G command, and these seven GTID’s (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) from the server are included:
mysql> show slave status for channel 'atl_tpa_replication'\G
...
Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
...
Since I executed the SHOW SLAVE STATUS\G from the Tampa server, I can also see the existing Tampa GTIDs also fall under the Executed_Gtid_Set section – 160f4761-c55c-422f-8684-d086f6a1db0e:1-3.
Now I can turn on replication to go from Tampa to Atlanta, where the Tampa primary-write server is the source (master) for the Atlanta primary-write server replica (slave). After I run the CHANGE MASTER TO command, I will turn on the replica (slave) with the START SLAVE command. I will then check on the status with SHOW SLAVE STATUS\G.
NOTE: I have changed the CHANNEL name to be tpa_atl_replication (Tampa to Atlanta replication)
On Atlanta
CHANGE MASTER TO MASTER_HOST = '192.168.1.161', MASTER_PORT = 3306, MASTER_USER = 'rpl_user', MASTER_PASSWORD = 'R3plic4tion!', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'tpa_atl_replication';
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.1.161',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'rpl_user',
-> MASTER_PASSWORD = 'R3plic4tion!',
-> MASTER_AUTO_POSITION = 1
-> FOR CHANNEL 'tpa_atl_replication';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 2568
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.1.161',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'rpl_user',
-> MASTER_PASSWORD = 'R3plic4tion!',
-> MASTER_AUTO_POSITION = 1
-> FOR CHANNEL 'tpa_atl_replication';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.161
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 3581
Relay_Log_File: ic1-relay_log-tpa_atl_replication.000002
Relay_Log_Pos: 1413
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3581
Relay_Log_Space: 1631
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 161
Master_UUID: fdc89b12-50d7-11e9-bfa8-012cdcc95c70
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: tpa_atl_replication
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
From the above, I can now see the three GTIDs from the Tampa server (160f4761-c55c-422f-8684-d086f6a1db0e:1-3) have been replicated over to the Atlanta server, and the seven GTIDs (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) from Atlanta also appear in the Executed_Gtid_Set:
Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
Finally, since I am running Group Replication, these transactions which were replicated between the primary-write servers will also be replicated to the other two servers in each group. I execute the SHOW SLAVE STATUS\G on a secondary server (IP of 192.168.1.152) and see all of the GTID’s which have been replicated between the primary servers:
On secondary server (in Atlanta) with IP of 192.168.1.152:
mysql> show slave status\G
...
Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
...
Replicate new transactions
Since both of these servers were new installs of MySQL, each instance only has these four databases:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
I will now create a database on each server, with the GR group location name as the name of the database. These databases will then replicate over to the other groups. And, you will see the executed GTIDs on the source (master) increase from seven (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) to eight (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8).
On Atlanta:
mysql> create database Atlanta;
Query OK, 1 row affected (0.01 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 2097
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Atlanta |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
When I go to any of the group replication instances in Tampa, I can see the Atlanta database has already been replicated over to the group, and each node has executed this transaction. From node three – 192.168.1.163:
On Tampa
mysql> show databases; +--------------------+ | Database | +--------------------+ | Atlanta | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
And the same happens when I create a database named Tampa – it will get replicated over to the Atlanta Group Replication group:
On Tampa:
mysql> create database Tampa; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | Atlanta | | information_schema | | mysql | | performance_schema | | sys | | Tampa | +--------------------+ 6 rows in set (0.00 sec)
On the Tampa Group Replication group, the GTIDs increased from three (160f4761-c55c-422f-8684-d086f6a1db0e:1-3) to four (160f4761-c55c-422f-8684-d086f6a1db0e:1-4). The other GTIDs – 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8 – are from the Atlanta Group Replication group. I can confirm this with a SHOW MASTER\G statement:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 3957
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-4,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
1 row in set (0.00 sec)
Also, I can check to see if the “Tampa” database (which was GTID 160f4761-c55c-422f-8684-d086f6a1db0e:4) has been replicated over to the Atlanta group by running a SHOW SLAVE STATUS\G on any of the Atlanta nodes. I will use the second node (IP address 192.168.1.152):
On Atlanta
mysql> show slave status\G
...
Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:4
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-4,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
...
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Atlanta |
| information_schema |
| mysql |
| performance_schema |
| sys |
| Tampa |
+--------------------+
6 rows in set (0.00 sec)
I now have two different MySQL Group Replication groups replicating data between each other.
WARNING: This should only be done if you can ensure that neither group will be modifying the other group’s data. Since replication is not instant, you could make a change on one group at the same time another person is changing the same row – and this scenario would break the replication between the two groups. Or, you could try to modify data that was modified on another group, and you will be modifying stale data.
Can I change the primary-write server in this scenario?
Since I have replication channels on both of the primary-write nodes, what happens if I want to change the primary node to another server? For example, 192.168.1.151 is the PRIMARY for the Atlanta Group Replication, but I want 192.168.1.152 to be the PRIMARY.
On Atlanta, here are the members of the group replication, with 192.168.1.151 (MacVM151.local) as the PRIMARY: (see the MEMBER_ROLE column)
mysql> SELECT * FROM performance_schema.replication_group_members order by member_host; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local | 3306 | ONLINE | PRIMARY | 8.0.15 | | group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local | 3306 | ONLINE | SECONDARY | 8.0.15 | | group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local | 3306 | ONLINE | SECONDARY | 8.0.15 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
If I try and change the second node (MEMBER_ID 247898e0-4cb7-11e9-97a9-12f28adcadd1) to be the PRIMARY server, I will get an error:
From the current PRIMARY node – which is the first node in the group – 192.168.1.151, I will try and make the second node the PRIMARY:
mysql> SELECT group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1');
ERROR 13223 (HY000): The function 'group_replication_set_as_primary' failed. There is a slave channel running in the group's current primary member.
In order for me to change the PRIMARY to the second node, I will need to stop the asynchronous replication on the first node (192.168.1.151) by issuing the STOP SLAVE command. This stops the replication between 192.168.1.151 and 192.168.1.161.
I can then change the PRIMARY to be the second node (MEMBER_ID 247898e0-4cb7-11e9-97a9-12f28adcadd1 and IP address of 192.168.1.152). Once the second node is the new PRIMARY, then I can start replication on the second node by running the CHANGE MASTER TO command on 192.168.1.152.
On 192.168.1.151 (Atlanta Group): (Note: You can change the PRIMARY from any node in the Group Replication group)
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1') |
+--------------------------------------------------------------------------+
| Primary server switched to: 247898e0-4cb7-11e9-97a9-12f28adcadd1 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
I can see the second node is the new PRIMARY: (see the MEMBER_ROLE column)
mysql> SELECT * FROM performance_schema.replication_group_members order by member_host; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local | 3306 | ONLINE | SECONDARY | 8.0.15 | | group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local | 3306 | ONLINE | PRIMARY | 8.0.15 | | group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local | 3306 | ONLINE | SECONDARY | 8.0.15 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
Now I can start replication on 192.168.1.152, with 192.168.1.161 as the source (master) by using the CHANGE MASTER TO command.
CHANGE MASTER TO MASTER_HOST = '192.168.1.161', MASTER_PORT = 3306, MASTER_USER = 'rpl_user', MASTER_PASSWORD = 'R3plic4tion!', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'TPA_ATL_REPLICATION';
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.1.161',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'rpl_user',
-> MASTER_PASSWORD = 'R3plic4tion!',
-> MASTER_AUTO_POSITION = 1
-> FOR CHANNEL 'TPA_ATL_REPLICATION';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
And, the SHOW SLAVE STATUS confirms the SQL and IO threads are running:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.161
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 3957
Relay_Log_File: ic1-relay_log-tpa_atl_replication.000002
Relay_Log_Pos: 400
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Note: With Group Replication, when the PRIMARY goes down, the group will automatically elect a new PRIMARY server, and the group will keep on processing transactions. If you are doing “regular” asynchronous replication from the PRIMARY and it goes down, there isn’t a way to automatically change the source (master), and replication will be broken. You will need to change the replica (slave) to another server in the Group Replication group. And, when the new PRIMARY comes back online, you will need to issue a STOP SLAVE command if the replication is still active on that server. You don’t want two replicas (slaves) attached at the same time.
 |
Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn. |
 |
Tony is the author of Twenty Forty-Four: The League of Patriots Visit http://2044thebook.com for more information. |
 |
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition Visit https://amzn.to/2oPFLI0 for more information. |
info [at] Scripting
Scripting MySQL 
Thanks for this post!
Can you please let us know if this is applicable to use with Oracle/MySQL Operator for kubernetes?
I don’t see why it wouldn’t work with Kubernetes. (But, I am not a Kubernetes expert)
Thanks for sharing this, Tested and working for our envs.
Can I use Async Replication between two Innodb cluster which is working as a multi-primary mode. I have tried to setup this, It is working but I have to do too many manual operations for maintenance.
I don’t believe this is supported as of today.