MySQL Group Replication: Conversion of GR Member to Async Replica (and Back) In the Same Cluster

MySQL Group ReplicationMySQL 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.

Using Percona Server for MySQL 8.0.22.

 

Percona Live ONLINE, the open source database conference, is coming up! Registration is now OPEN… and FREE! 

 

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:

Step 1: 

— Take out the node “gr5” from the cluster.

Current cluster status:

Current “gr5” status:

Step 2:

Update the connection parameters to not allow communication with other Cluster nodes.