MySQL Group Replication is a plugin that helps to implement highly available fault-tolerant replication topologies. In this blog, I am going to explain the complete steps involved in the below two topics.
- How to convert the group replication member to an asynchronous replica
- How to convert the asynchronous replica to a group replication member
Why Am I Converting From GR Back to Old Async?
Recently I had a requirement from one of our customers running 5 node GR clusters. Once a month they are doing the bulk read job for generating the business reports. When they are doing the job, it affects the overall cluster performance because of the flow control issues. The node which is executing the read job is overloaded and delays the certification and writes apply process. The read job queries can’t be split across the cluster. So, they don’t want that particular node as a part of the cluster during the report generation. So, I recommended this approach. The overall job will take 3-4 hours. During that particular time, the topology will be 4 node clusters and one asynchronous replica. Once the job is completed, the async replica node will be again joined to the GR cluster.
For testing this, I have installed and configured the group replication cluster with 5 nodes ( gr1,gr2,gr3,gr4,gr5 ). The cluster is operating with a single primary mode.
1 2 3 4 5 6 7 8 9 10 11 | mysql> select member_host,member_state,member_role,member_version from performance_schema.replication_group_members; +-------------+--------------+-------------+----------------+ | member_host | member_state | member_role | member_version | +-------------+--------------+-------------+----------------+ | gr5 | ONLINE | SECONDARY | 8.0.22 | | gr4 | ONLINE | SECONDARY | 8.0.22 | | gr3 | ONLINE | SECONDARY | 8.0.22 | | gr2 | ONLINE | SECONDARY | 8.0.22 | | gr1 | ONLINE | PRIMARY | 8.0.22 | +-------------+--------------+-------------+----------------+ 5 rows in set (0.00 sec) |
Using Percona Server for MySQL 8.0.22.
1 2 3 4 5 | mysql> select @@version, @@version_comment\G *************************** 1. row *************************** @@version: 8.0.22-13 @@version_comment: Percona Server (GPL), Release 13, Revision 6f7822f 1 row in set (0.01 sec) |
How to Convert the Group Replication Member to Asynchronous Replica?
To explain this topic,
- I am going to convert the group replication member “gr5” to an asynchronous replica.
- The GR member “gr4” will be the source for “gr5”.
Current status:
1 2 3 4 5 6 7 8 9 | +-------------+--------------+-------------+----------------+ | member_host | member_state | member_role | member_version | +-------------+--------------+-------------+----------------+ | gr5 | ONLINE | SECONDARY | 8.0.22 | | gr4 | ONLINE | SECONDARY | 8.0.22 | | gr3 | ONLINE | SECONDARY | 8.0.22 | | gr2 | ONLINE | SECONDARY | 8.0.22 | | gr1 | ONLINE | PRIMARY | 8.0.22 | +-------------+--------------+-------------+----------------+ |
Step 1:
— Take out the node “gr5” from the cluster.
1 2 | gr5 > stop group_replication; Query OK, 0 rows affected (4.64 sec) |
Current cluster status:
1 2 3 4 5 6 7 8 | +-------------+--------------+-------------+----------------+ | member_host | member_state | member_role | member_version | +-------------+--------------+-------------+----------------+ | gr4 | ONLINE | SECONDARY | 8.0.22 | | gr3 | ONLINE | SECONDARY | 8.0.22 | | gr2 | ONLINE | SECONDARY | 8.0.22 | | gr1 | ONLINE | PRIMARY | 8.0.22 | +-------------+--------------+-------------+----------------+ |
Current “gr5” status:
1 2 3 4 5 6 | +-------------+--------------+-------------+----------------+ | member_host | member_state | member_role | member_version | +-------------+--------------+-------------+----------------+ | gr5 | OFFLINE | | | +-------------+--------------+-------------+----------------+ 1 row in set (0.00 sec) |
Step 2:
Update the connection parameters to not allow communication with other Cluster nodes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | gr5 > select @@group_replication_group_seeds, @@group_replication_ip_whitelist\G *************************** 1. row *************************** @@group_replication_group_seeds: 172.28.128.23:33061,172.28.128.22:33061,172.28.128.21:33061,172.28.128.20:33061,172.28.128.19:33061 @@group_replication_ip_whitelist: 172.28.128.23,172.28.128.22,172.28.128.21,172.28.128.20,172.28.128.19 1 row in set (0.00 sec) gr5 > set global group_replication_group_seeds=''; Query OK, 0 rows affected (0.00 sec) gr5 > set global group_replication_ip_whitelist=''; Query OK, 0 rows affected, 1 warning (0.00 sec) gr5 > select @@group_replication_group_seeds, @@group_replication_ip_whitelist; +---------------------------------+----------------------------------+ | @@group_replication_group_seeds | @@group_replication_ip_whitelist | +---------------------------------+----------------------------------+ | | | +---------------------------------+----------------------------------+ 1 row in set (0.00 sec) |
Step 3:
Remove the group replication channel configurations and the respective physical files. During the group replication configuration, it will create two channels and the respective files (applier/recovery files).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | gr5 > select Channel_name from mysql.slave_master_info; +----------------------------+ | Channel_name | +----------------------------+ | group_replication_applier | | group_replication_recovery | +----------------------------+ 2 rows in set (0.00 sec) [root@gr5 mysql]# ls -lrth | grep -i replication -rw-r-----. 1 mysql mysql 225 Apr 12 19:18 gr5-relay-bin-group_replication_applier.000001 -rw-r-----. 1 mysql mysql 98 Apr 12 19:18 gr5-relay-bin-group_replication_applier.index -rw-r-----. 1 mysql mysql 226 Apr 12 19:18 gr5-relay-bin-group_replication_recovery.000001 -rw-r-----. 1 mysql mysql 273 Apr 12 19:18 gr5-relay-bin-group_replication_recovery.000002 -rw-r-----. 1 mysql mysql 100 Apr 12 19:18 gr5-relay-bin-group_replication_recovery.index -rw-r-----. 1 mysql mysql 660 Apr 12 19:18 gr5-relay-bin-group_replication_applier.000002 |
We can remove them by resetting the replica status.
1 2 3 4 5 6 7 8 | gr5 > reset replica all; Query OK, 0 rows affected (0.02 sec) gr5 > select Channel_name from mysql.slave_master_info; Empty set (0.00 sec) [root@gr5 mysql]# ls -lrth | grep -i replication [root@gr5 mysql]# |
Step 4:
Configure asynchronous replication. To configure, we don’t need to manually update the binlog/gtid positions. Group replication will run based on the GTID. The node was already configured as a member in the same group so it should already have the GTID entries.
1 2 3 4 5 6 7 | gr5 > select @@gtid_executed, @@gtid_purged\G *************************** 1. row *************************** @@gtid_executed: ae2434f6-2be4-4d15-a5dc-fd54919b79b0:1-8, b93e0429-989d-11eb-ad7b-5254004d77d3:1 @@gtid_purged: ae2434f6-2be4-4d15-a5dc-fd54919b79b0:1-2, b93e0429-989d-11eb-ad7b-5254004d77d3:1 1 row in set (0.00 sec) |
Just need to run the CHANGE MASTER command.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | gr5 > change master to master_user='gr_repl',master_password='Repl@321',master_host='gr4',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.05 sec) gr5 > start replica; Query OK, 0 rows affected (0.02 sec) gr5 > pager grep -i 'Master_Host\|Slave_IO_Running\|Slave_SQL_Running\|Seconds_Behind_Master' PAGER set to 'grep -i 'Master_Host\|Slave_IO_Running\|Slave_SQL_Running\|Seconds_Behind_Master'' gr5 > show slave status\G Master_Host: gr4 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 1 row in set, 1 warning (0.00 sec) |
So, finally, the current topology is:
- We have 4 node group replication clusters ( gr1, gr2, gr3, gr4 ).
- The node “gr5” is configured as an async replica under the “gr4”.
How to Convert the Async Replica to Group Replication Member?
To explain this topic:
- I am going to break the asynchronous replication on “gr5”.
- Then, I will join the node “gr5” to the group replication cluster.
Step 1:
Break replication on “gr5” and reset the replica.
1 2 3 4 5 6 7 8 | gr5 > stop replica; Query OK, 0 rows affected (0.00 sec) gr5 > reset replica all; Query OK, 0 rows affected (0.00 sec) gr5 > show replica status\G Empty set (0.00 sec) |
Step 2:
Configure the connection parameters to join to the cluster.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | gr5 > select @@group_replication_group_seeds, @@group_replication_ip_whitelist; +---------------------------------+----------------------------------+ | @@group_replication_group_seeds | @@group_replication_ip_whitelist | +---------------------------------+----------------------------------+ | | | +---------------------------------+----------------------------------+ 1 row in set (0.00 sec) gr5 > set global group_replication_group_seeds='172.28.128.23:33061,172.28.128.22:33061,172.28.128.21:33061,172.28.128.20:33061,172.28.128.19:33061'; Query OK, 0 rows affected (0.00 sec) gr5 > set global group_replication_ip_whitelist='172.28.128.23,172.28.128.22,172.28.128.21,172.28.128.20,172.28.128.19'; Query OK, 0 rows affected, 1 warning (0.00 sec) gr5 > select @@group_replication_group_seeds, @@group_replication_ip_whitelist\G *************************** 1. row *************************** @@group_replication_group_seeds: 172.28.128.23:33061,172.28.128.22:33061,172.28.128.21:33061,172.28.128.20:33061,172.28.128.19:33061 @@group_replication_ip_whitelist: 172.28.128.23,172.28.128.22,172.28.128.21,172.28.128.20,172.28.128.19 1 row in set (0.00 sec) |
Step 3:
Configure the channel for “group_replication_recovery”.
1 2 | gr5 > change master to master_user='gr_repl',master_password='Repl@321' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec) |
Note: No need to configure the GTID parameters before starting the group replication service, because the node was already configured as an async replica in the same group. So, when starting the group replication service, it will automatically start with the last GTID position executed by async replication and start to sync the rest of the data.
Step 4:
Start the group replication service
1 2 | gr5 > start group_replication; Query OK, 0 rows affected, 1 warning (3.02 sec) |
Final status:
1 2 3 4 5 6 7 8 9 10 11 | mysql> select member_host,member_state,member_role,member_version from performance_schema.replication_group_members; +-------------+--------------+-------------+----------------+ | member_host | member_state | member_role | member_version | +-------------+--------------+-------------+----------------+ | gr5 | ONLINE | SECONDARY | 8.0.22 | | gr4 | ONLINE | SECONDARY | 8.0.22 | | gr3 | ONLINE | SECONDARY | 8.0.22 | | gr2 | ONLINE | SECONDARY | 8.0.22 | | gr1 | ONLINE | PRIMARY | 8.0.22 | +-------------+--------------+-------------+----------------+ 5 rows in set (0.00 sec) |
I hope this blog post will be helpful to someone, who is learning or working with MySQL Group replication.
Cheers!
Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
