As organizations increasingly prioritize high availability and reliability in their database systems, MySQL Group Replication emerges as a powerful solution. With the recent release of version 8.0.35, Amazon RDS users can now leverage the advanced features of Group Replication to ensure seamless data replication, consistency, and availability across a distributed MySQL environment.

In this guide, I'll walk you through the essential steps, from upgrading existing servers and taking manual snapshots to launching instances and configuring Group Replication parameters. This guide aims to provide clear insights and actionable steps for setting up a robust and highly available MySQL cluster on Amazon RDS.

Understanding Group Replication

The MySQL Group Replication plugin was introduced in version 5.7.16, released in December 2016. This plugin empowers you to create a highly available distributed MySQL service across a group of MySQL server instances. It achieves this by utilizing a powerful new group communication service, which incorporates an implementation of the popular Paxos algorithm. Through this, the group of MySQL Server instances automatically coordinates on data replication, consistency, and membership. These functionalities collectively provide all the built-in mechanisms necessary for ensuring the high availability of your MySQL databases.

Resources by Mydbops

'll keep it brief as Mydbops has extensively covered MySQL Group Replication (GR) in various insightful blog posts and webinars. For more in-depth knowledge, you can explore the following resources:

1. Presentation: Group Replication in MySQL 8.0
2. Adding a New Node to MySQL Group Replication from a Backup - A Step-by-Step Guide
3. Mastering MySQL Group Replication: Primary Promotion Techniques
4. MySQL 8.0 Flow Control in Group Replication
5. MySQL Group Replication and Its Memory Consumption - Troubleshooting
6. MySQL Replication: Native Replication vs. InnoDB Cluster

Now, let's focus on the core of this blog post, which is the implementation of GR replication with RDS MySQL 8.0.35

Implementing GR Replication with RDS 8.0.35

Let's explore the process of enabling Group Replication (GR) for a server with existing data. If you are currently using a previous version of MySQL, consider planning an upgrade to the latest version, 8.0.35, after conducting thorough testing.

For those on version 5.7, you can leverage the latest snapshot upgrade feature. This involves transitioning from 5.7 to 8.0.34 through a snapshot and then performing a minor patch upgrade to reach version 8.0.35.

Refer to the detailed guide on MySQL RDS snapshot upgrades at MySQL Latest AWS RDS Snapshot Upgrade.

Manual Snapshot for Consistency

As part of the process, take a 'Manual Snapshot.' It's crucial to note that snapshots capture data at a specific point in time. Therefore, ensure the backup's consistency by configuring sync_binlog=1 and innodb_flush_log_at_trx_commit=1. If you're performing a backup on a live system, be cautious of potential slight IO disruptions, particularly in single AZ instances.

Launching Instances with Snapshots

Select your instance >>> Actions >> Take snapshot. Here, I now have my instance snapshot available as grsnapshot.

The next step involves launching three instances with the same snapshot, naming them mygr1, mygr2, and mygr3, respectively, as outlined below.

While the instances are being launched, we will concurrently create a separate Parameter Group (PG) with Group Replication (GR) configuration. You can opt to create an individual PG for each of the three nodes or choose a common PG based on your personal preferences.

Creating Parameter Group with GR Configuration

Below are the mandatory parameters for Group replication to work with RDS.

‍

The group replication name, i.e., UUID, can be generated as follows:

 
Select uuid();
	

Applying Parameter Group and Rebooting Instances

Next, proceed to apply the new parameter group, gr-pg-group, in all instances. Reboot them to apply the static changes.

Follow these steps:

Select RDS >>> modify >>> Additional Configuration >>> Database options >>> Choose PG.

After attaching the required parameter group with all the parameters, proceed to reboot all three instances.

Execute the following steps: Select RDS >>> Actions >>> reboot.

Updating Group Replication Seeds

Now that all the instances are in the 'Available' state with the necessary parameters for Group Replication (GR), let's proceed with the configuration.

Update the group_replication_group_seeds in the parameter group with your node list, as shown below:

rd1endpoint:port,rd2endpoint:port,rd3endpoint:port

 
Group_replication_group_seeds = mygr1.cw4ye4iwvr7l.ap-south-1.rds.amazonaws.com:3306,mygr2.cw4ye4iwvr7l.ap-south-1.rds.amazonaws.com:3306,mygr3.cw4ye4iwvr7l.ap-south-1.rds.amazonaws.com:3306
	

This defines the list of group members (nodes) in the cluster, and any newly joining member can refer to and synchronize data with the group.

Login to each node and verify that the seed value has been propagated correctly, as shown below:

 
Mysql > select * from global variables like ‘group_replication_group_seeds’;
	

Binlog Retention for Recovery Window

Increase the binlog retention on all nodes to the maximum possible duration, as it determines your recovery window. Here, I am setting it to the maximum retention of 7 days with RDS, equivalent to 168 hours, on all nodes.

Execute the following command to adjust the binlog retention:

 
call mysql.rds_set_configuration('binlog retention hours', 168);
	

Creating Group Replication User and Recovery Channel

To create the group replication user and group replication recovery channel, execute the following commands on all three instances of the cluster:

 
#call mysql.rds_group_replication_create_user('group_replication_user_password');

#call mysql.rds_group_replication_set_recovery_channel('group_replication_user_password');
	

Starting Group Replication

In the final step to initiate group replication, execute the following stored procedure on the first node, designating it as the bootstrap node by passing the argument as 1. On the remaining nodes, run the stored procedure with an argument of 0 as outlined below:

Bootstrap node:

node 1: mygr1.cw4ye4iwvr7l.ap-south-1.rds.amazonaws.com

 
call mysql.rds_group_replication_start(1);
	

Other Nodes:

node 2: mygr2.cw4ye4iwvr7l.ap-south-1.rds.amazonaws.com

 
call mysql.rds_group_replication_start(0);
	

node 3: mygr3.cw4ye4iwvr7l.ap-south-1.rds.amazonaws.com

Once completed, our Group Replication (GR) cluster with RDS is up and running. You can verify the status by executing the following query on any of the nodes:

 
mysql> select * from performance_schema.replication_group_members;
	

Flow Diagram

Use Cases

With the introduction of Group Replication (GR) with RDS, a variety of use cases are now covered, including:

• eCommerce: Ensuring the availability of sites is crucial for users to continue shopping.
• Fintech: Maintaining data accuracy and availability is critical in the fintech sector.
• High Write Throughput: GR can handle high write throughput, with multiple primaries managing writes, and can scale better than Aurora in terms of writes.

Scaling Possibilities with GR and RDS

With the introduction of GR with RDS, you can add up to 9 instances in active-active mode, providing better scaling of writes and improved availability for your data.

It's important to note that RDS GR supports only multi-primary mode. However, RDS-proxy doesn't support GR deployments, and GR-specific metrics need to be configured externally. Additionally, be sure to check other limitations associated with group replication here.

Ready to implement MySQL Group Replication on Amazon RDS for a robust, highly available database cluster?

Optimize your MySQL Group Replication setup with Mydbops' InnoDB Cluster Consulting Services. Our experts can help you streamline your database management and improve performance. Contact us today to discuss your specific needs.

{{cta}}