Configuring active-active clusters for RDS for MySQL - Amazon Relational Database Service
Use casesConsiderations and best practicesPrerequisites for a cross-VPC active-active clusterRequired parameter settingsConverting a DB instance to an active-active clusterSetting up an active-active cluster with new DB instancesAdding a DB instanceMonitoring active-active clustersStopping Group Replication on a DB instanceRenaming a DB instance in an active-active clusterRemoving a DB instance from an active-active clusterLimitations for active-active clusters

Configuring active-active clusters for RDS for MySQL

You can set up an active-active cluster for RDS for MySQL by using the MySQL Group Replication plugin. The Group Replication plugin is supported for RDS for MySQL DB instances running version 8.0.35 and higher minor versions.

For information about MySQL Group Replication, see Group Replication in the MySQL documentation. The MySQL documentation contains detailed information about this feature, while this topic describes how to configure and manage the plugin on your RDS for MySQL DB instances.

Note

For the sake of brevity, all mentions of "active-active" cluster in this topic refer to active-active clusters using the MySQL Group Replication plugin.

Use cases for active-active clusters

The following cases are good candidates for using active-active clusters:

  • Applications that need all of the DB instances in the cluster to support write operations. The Group Replication plugin keeps the data consistent on each DB instance in the active-active cluster. For more information about how this works, see Group Replication in the MySQL documentation.

  • Applications that require continuous availability of the database. With an active-active cluster, the data is retained on the all of the DB instances in the cluster. If one DB instance fails, the application can reroute traffic to another DB instance in the cluster.

  • Applications that might need to split read and write operations among different DB instances in the cluster for load balancing purposes. With an active-active cluster, your applications can send read traffic to specific DB instances and write traffic to others. You can also switch which DB instances to send reads or writes to at any time.

Considerations and best practices for active-active clusters

Before you use RDS for MySQL active-active clusters, review the following considerations and best practices:

  • Active-active clusters can't have more than nine DB instances.

  • With the Group Replication plugin, you can control the transaction consistency guarantees of the active-active cluster. For more information, see Transaction Consistency Guarantees in the MySQL documentation.

  • Conflicts are possible when different DB instances update the same row in an active-active cluster. For information about conflicts and conflict resolution, see Group Replication in the MySQL documentation.

  • For fault tolerance, include at least three DB instances in your active-active cluster. It is possible to configure an active-active cluster with only one or two DB instances, but the cluster won't be fault tolerant. For information about fault tolerance, see Fault-tolerance in the MySQL documentation.

  • When a DB instance joins an existing active-active cluster and is running the same engine version as the lowest engine version in the cluster, the DB instance joins in read-write mode.

  • When a DB instance joins an existing active-active cluster and is running a higher engine version than the lowest engine version in the cluster, the DB instance must remain in read-only mode.

  • If you enable Group Replication for a DB instance by setting its rds.group_replication_enabled parameter to 1 in the DB parameter group, but replication hasn't started or has failed to start, the DB instance is placed in super-read-only mode to prevent data inconsistencies. For information about super-read-only mode, see the MySQL documentation.

  • You can upgrade a DB instance in an active-active cluster, but the DB instance is read-only until all of the other DB instances in the active-active cluster are upgraded to same engine version or a higher engine version. When you upgrade a DB instance, the DB instance automatically joins the same active-active cluster when the upgrade completes. To avoid an unintended switch to read-only mode for a DB instance, disable automatic minor version upgrades for it. For information about upgrading a MySQL DB instance, see Upgrading the MySQL DB engine.

  • You can add a DB instance in a Multi-AZ DB instance deployment to an existing active-active cluster. You can also convert a Single-AZ DB instance in an active-active cluster to a Multi-AZ DB instance deployment. If a primary DB instance in a Multi-AZ deployment fails, that primary instance fails over to the standby instance. The new primary DB instance automatically joins the same cluster after failover completes. For more information about Multi-AZ DB instance deployments, see Multi-AZ DB instance deployments.

  • We recommend that the DB instances in an active-active cluster have different time ranges for their maintenance windows. This practice avoids multiple DB instances in the cluster going offline for maintenance at the same time. For more information, see The Amazon RDS maintenance window.

  • Active-active clusters can use SSL for connections between DB instances. To configure SSL connections, set the group_replication_recovery_use_ssl and group_replication_ssl_mode parameters. The values for these parameters must match for all DB instances in the active-active cluster.

    Currently, active-active clusters don't support certificate authority (CA) verification for connections between AWS Regions. So, the group_replication_ssl_mode parameter must be set to DISABLED (the default) or REQUIRED for cross-Region clusters.

  • An RDS for MySQL active-active cluster runs in multi-primary mode. The default value of the group_replication_enforce_update_everywhere_checks is ON and the parameter is static. When this parameter is set to ON, applications can't insert into a table that has cascading foreign key constraints.

  • An RDS for MySQL active-active cluster uses the MySQL communication stack for connection security instead of XCOM. For more information, see Communication Stack for Connection Security Management in the MySQL documentation.

  • When a DB parameter group is associated with a DB instance in an active-active cluster, we recommend only associating this DB parameter group with other DB instances that are in the cluster.

  • Active-active clusters only support RDS for MySQL DB instances. These DB instances must be running supported versions of the DB engine.

  • When a DB instance in an active-active cluster has an unexpected failure, RDS starts recovery of the DB instance automatically. If the DB instance doesn't recover, we recommend replacing it with a new DB instance by performing a point-in-time recovery with a healthy DB instance in the cluster. For instructions, see Adding a DB instance to an active-active cluster using point-in-time recovery.

  • You can delete a DB instance in an active-active cluster without affecting the other DB instances in the cluster. For information about deleting a DB instance, see Deleting a DB instance.

Prerequisites for a cross-VPC active-active cluster

You can configure an active-active cluster with DB instances in more than one VPC. The VPCs can be in the same AWS Region or different AWS Regions.

Note

Sending traffic between multiple AWS Regions might incur additional costs. For more information, see Overview of Data Transfer Costs for Common Architectures.

If you are configuring an active-active cluster in a single VPC, you can skip these steps and move on to Setting up an active-active cluster with new DB instances.

To prepare for an active-active cluster with DB instances in more than one VPC

  1. Make sure the IPv4 address ranges in the CIDR blocks meet the following requirements:

    • The IPv4 address ranges in the CIDR blocks of the VPCs can't overlap.

    • All of the IPv4 address ranges in the CIDR blocks either must be lower than 128.0.0.0/subnet_mask or higher than 128.0.0.0/subnet_mask.

    The following ranges illustrate these requirements:

    • 10.1.0.0/16 in one VPC and 10.2.0.0/16 in the other VPC is supported.

    • 172.1.0.0/16 in one VPC and 172.2.0.0/16 in the other VPC is supported.

    • 10.1.0.0/16 in one VPC and 10.1.0.0/16 in the other VPC is not supported because the ranges overlap.

    • 10.1.0.0/16 in one VPC and 172.1.0.0/16 in the other VPC is not supported because one is below 128.0.0.0/subnet_mask and the other is above 128.0.0.0/subnet_mask.

    For information about CIDR blocks, see VPC CIDR blocks in the Amazon VPC User Guide.

  2. In each VPC, make sure DNS resolution and DNS hostnames are both enabled.

    For instructions, see View and update DNS attributes for your VPC in the Amazon VPC User Guide.

  3. Configure the VPCs so that you can route traffic between them in one of the following ways:

    • Create a VPC peering connection between the VPCs.

      For instructions, see Create a VPC peering connection in the Amazon VPC Peering Guide. In each VPC, make sure there are inbound rules for your security groups that reference security groups in the peered VPC. Doing so allows traffic to flow to and from instances that are associated with the referenced security group in the peered VPC. For instructions, see Update your security groups to reference peer security groups in the Amazon VPC Peering Guide.

    • Create a transit gateway between the VPCs.

      For instructions, see Getting started with transit gateways in Amazon VPC Transit Gateways. In each VPC, make sure there are inbound rules for your security groups that allow traffic from the other VPC, such as inbound rules that specify the CIDR of the other VPC. Doing so allows traffic to flow to and from instances that are associated with the referenced security group in the active-active cluster. For more information, see Control traffic to your AWS resources using security groups in the Amazon VPC User Guide.

Required parameter settings for active-active clusters

The following parameter settings are required when you are setting up an RDS for MySQL active-active cluster.

Parameter Description Required setting

binlog_format

Sets the binary logging format. The default value for RDS for MySQL is MIXED. For more information, see the MySQL documentation.

ROW

enforce_gtid_consistency

Enforces GTID consistency for statement execution. The default value for RDS for MySQL is OFF. For more information, see the MySQL documentation.

ON

group_replication_group_name

Sets the Group Replication name to a UUID. The UUID format is 11111111-2222-3333-4444-555555555555. You can generate a MySQL UUID by connecting to a MySQL DB instance and running SELECT UUID(). The value must be the same for all of the DB instances in the active-active cluster. For more information, see the MySQL documentation.

A MySQL UUID

gtid-mode

Controls GTID-based logging. The default value for RDS for MySQL is OFF_PERMISSIVE. For more information, see the MySQL documentation.

ON

rds.custom_dns_resolution

Specifies whether to allow DNS resolution from the Amazon DNS server in your VPC. DNS resolution must be enabled when Group Replication is enabled with the rds.group_replication_enabled parameter. DNS resolution can't be enabled when Group Replication is disabled with the rds.group_replication_enabled parameter. For more information, see Amazon DNS server in the Amazon VPC User Guide.

1

rds.group_replication_enabled

Specifies whether Group Replication is enabled for a DB instance. Group Replication must be enabled on a DB instance in an active-active cluster.

1

slave_preserve_commit_order

Controls the order that transactions are committed on a replica. The default value for RDS for MySQL is ON. For more information, see the MySQL documentation.

ON

Converting an existing DB instance to an active-active cluster

The DB engine version of the DB instance you want to migrate to an active-active cluster must be MySQL 8.0.35 or higher. If you need to upgrade the engine version, see Upgrading the MySQL DB engine.

If you are setting up an active-active cluster with DB instances in more than one VPC, make sure you complete the prerequisites in Prerequisites for a cross-VPC active-active cluster.

Complete the following steps to migrate an existing DB instance to an active-active cluster for RDS for MySQL.

Step 1: Set the active-active cluster parameters in one or more custom parameter groups

The RDS for MySQL DB instances in an active-active cluster must be associated with a custom parameter group that has the correct setting for required parameters. For information about the parameters and the required setting for each one, see Required parameter settings for active-active clusters.

You can set these parameters in new parameter groups or in existing parameter groups. However, to avoid accidentally affecting DB instances that aren't part of the active-active cluster, we strongly recommend that you create a new custom parameter group. The DB instances in an active-active cluster can be associated with the same DB parameter group or with different DB parameter groups.

You can use the AWS Management Console or the AWS CLI to create a new custom parameter group. For more information, see Creating a DB parameter group. The following example runs the create-db-parameter-group AWS CLI command to create a custom DB parameter group named myactivepg:

For Linux, macOS, or Unix:

aws rds create-db-parameter-group \
  --db-parameter-group-name myactivepg \
  --db-parameter-group-family mysql8.0 \
  --description "Parameter group for active-active clusters"

For Windows:

aws rds create-db-parameter-group ^
  --db-parameter-group-name myactivepg ^
  --db-parameter-group-family mysql8.0 ^
  --description "Parameter group for active-active clusters"

You can also use the AWS Management Console or the AWS CLI to set the parameters in the custom parameter group. For more information, see Modifying parameters in a DB parameter group.

The following example runs the modify-db-parameter-group AWS CLI command to set the parameters:

For Linux, macOS, or Unix:

aws rds modify-db-parameter-group \
  --db-parameter-group-name myactivepg \
  --parameters "ParameterName='rds.group_replication_enabled',ParameterValue='1',ApplyMethod=pending-reboot" \
               "ParameterName='rds.custom_dns_resolution',ParameterValue='1',ApplyMethod=pending-reboot" \
               "ParameterName='enforce_gtid_consistency',ParameterValue='ON',ApplyMethod=pending-reboot" \
               "ParameterName='gtid-mode',ParameterValue='ON',ApplyMethod=pending-reboot" \
               "ParameterName='binlog_format',ParameterValue='ROW',ApplyMethod=immediate" \
               "ParameterName='slave_preserve_commit_order',ParameterValue='ON',ApplyMethod=immediate" \
               "ParameterName='group_replication_group_name',ParameterValue='11111111-2222-3333-4444-555555555555',ApplyMethod=pending-reboot"

For Windows:

aws rds modify-db-parameter-group ^
  --db-parameter-group-name myactivepg ^
  --parameters "ParameterName='rds.group_replication_enabled',ParameterValue='1',ApplyMethod=pending-reboot" ^
               "ParameterName='rds.custom_dns_resolution',ParameterValue='1',ApplyMethod=pending-reboot" ^
               "ParameterName='enforce_gtid_consistency',ParameterValue='ON',ApplyMethod=pending-reboot" ^
               "ParameterName='gtid-mode',ParameterValue='ON',ApplyMethod=pending-reboot" ^
               "ParameterName='binlog_format',ParameterValue='ROW',ApplyMethod=immediate" ^
               "ParameterName='slave_preserve_commit_order',ParameterValue='ON',ApplyMethod=immediate" ^
               "ParameterName='group_replication_group_name',ParameterValue='11111111-2222-3333-4444-555555555555',ApplyMethod=pending-reboot"

Step 2: Associate the DB instance with a DB parameter group that has the required Group Replication parameters set

Associate the DB instance with a parameter group you created or modified in the previous step. For instructions, see Associating a DB parameter group with a DB instance.

Reboot the DB instance for the new parameter settings to take effect. For instructions, see Rebooting a DB instance.

Step 3: Create the active-active cluster

In the DB parameter group associated with the DB instance, set the group_replication_group_seeds parameter to the endpoint of the DB instance you are converting.

You can use the AWS Management Console or the AWS CLI to set the parameter. You don't need to reboot the DB instance after setting this parameter. For more information about setting parameters, see Modifying parameters in a DB parameter group.

The following example runs the modify-db-parameter-group AWS CLI command to set the parameters:

For Linux, macOS, or Unix:

aws rds modify-db-parameter-group \
  --db-parameter-group-name myactivepg \
  --parameters "ParameterName='group_replication_group_seeds',ParameterValue='myactivedb1.123456789012.us-east-1.rds.amazonaws.com:3306',ApplyMethod=immediate"

For Windows:

aws rds modify-db-parameter-group ^
  --db-parameter-group-name myactivepg ^
  --parameters "ParameterName='group_replication_group_seeds',ParameterValue='myactivedb1.123456789012.us-east-1.rds.amazonaws.com:3306',ApplyMethod=immediate"

Step 4: Create additional RDS for MySQL DB instances for the active-active cluster

To create additional DB instances for the active-active cluster, perform point-in-time recovery on the DB instance you are converting. For instructions, see Adding a DB instance to an active-active cluster using point-in-time recovery.

An active-active cluster can have up to nine DB instances. Perform point-in-time recovery on the DB instance until you have the number of DB instances you want for the cluster. When you perform point-in-recovery, make sure you associate the DB instance you are adding with a DB parameter group that has rds.group_replication_enabled set to 1. Otherwise, Group Replication won't start on the newly added DB instance.

Step 5: Initialize the group on the DB instance you are converting

Initialize the group and start replication:

  1. Connect to that DB instance you are converting in a SQL client. For more information about connecting to an RDS for MySQL DB instance, see Connecting to a DB instance running the MySQL database engine.

  2. In the SQL client, run the following stored procedures and replace group_replication_user_password with the password for the rdsgrprepladmin user. The rdsgrprepladmin user is reserved for Group Replication connections in an active-active cluster. The password for this user must be the same on all of the DB instances in an active-active cluster.

    call mysql.rds_set_configuration('binlog retention hours', 168); -- 7 days binlog
call mysql.rds_group_replication_create_user('group_replication_user_password');
call mysql.rds_group_replication_set_recovery_channel('group_replication_user_password');
call mysql.rds_group_replication_start(1);

    This example sets the binlog retention hours value to 168, which means that binary log files are retained for seven days on the DB instance. You can adjust this value to meet your requirements.

    This example specifies 1 in the mysql.rds_group_replication_start stored procedure to initialize a new group with the current DB instance.

    For more information about the stored procedures called in the example, see Managing active-active clusters.

Step 6: Start replication on the other DB instances in the active-active cluster

For each of the DB instances in the active-active cluster, use a SQL client to connect to the instance, and run the following stored procedures. Replace group_replication_user_password with the password for the rdsgrprepladmin user.

call mysql.rds_set_configuration('binlog retention hours', 168); -- 7 days binlog
call mysql.rds_group_replication_create_user('group_replication_user_password');
call mysql.rds_group_replication_set_recovery_channel('group_replication_user_password');
call mysql.rds_group_replication_start(0);

This example sets the binlog retention hours value to 168, which means that binary log files are retained for seven days on each DB instance. You can adjust this value to meet your requirements.

This example specifies 0 in the mysql.rds_group_replication_start stored procedure to join the current DB instance to an existing group.

Tip

Make sure you run these stored procedures on all of the other DB instances in the active-active cluster.

Step 7: (Recommended) Check the status of the active-active cluster

To make sure each member of the cluster is configured correctly, check the status of the cluster by connecting to a DB instance in the active-active cluster, and running the following SQL command:

SELECT * FROM performance_schema.replication_group_members;

Your output should show ONLINE for the MEMBER_STATE of each DB instance, as in the following sample output:

+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 9854d4a2-5d7f-11ee-b8ec-0ec88c43c251 | ip-10-15-3-137 |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
| group_replication_applier | 9e2e9c28-5d7f-11ee-8039-0e5d58f05fef | ip-10-15-3-225 |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
| group_replication_applier | a6ba332d-5d7f-11ee-a025-0a5c6971197d | ip-10-15-1-83  |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

For information about the possible MEMBER_STATE values, see Group Replication Server States in the MySQL documentation.

Setting up an active-active cluster with new DB instances

Complete the following steps to set up an active-active cluster using new RDS for MySQL DB instances.

If you are setting up an active-active cluster with DB instances in more than one VPC, make sure you complete the prerequisites in Prerequisites for a cross-VPC active-active cluster.

Step 1: Set the active-active cluster parameters in one or more custom parameter groups

The RDS for MySQL DB instances in an active-active cluster must be associated with a custom parameter group that has the correct setting for required parameters. For information about the parameters and the required setting for each one, see Required parameter settings for active-active clusters.

You can set these parameters in new parameter groups or in existing parameter groups. However, to avoid accidentally affecting DB instances that aren't part of the active-active cluster, we strongly recommend that you create a new custom parameter group. The DB instances in an active-active cluster can be associated with the same DB parameter group or with different DB parameter groups.

You can use the AWS Management Console or the AWS CLI to create a new custom parameter group. For more information, see Creating a DB parameter group. The following example runs the create-db-parameter-group AWS CLI command to create a custom DB parameter group named myactivepg:

For Linux, macOS, or Unix:

aws rds create-db-parameter-group \
  --db-parameter-group-name myactivepg \
  --db-parameter-group-family mysql8.0 \
  --description "Parameter group for active-active clusters"

For Windows:

aws rds create-db-parameter-group ^
  --db-parameter-group-name myactivepg ^
  --db-parameter-group-family mysql8.0 ^
  --description "Parameter group for active-active clusters"

You can also use the AWS Management Console or the AWS CLI to set the parameters in the custom parameter group. For more information, see Modifying parameters in a DB parameter group.

The following example runs the modify-db-parameter-group AWS CLI command to set the parameters:

For Linux, macOS, or Unix:

aws rds modify-db-parameter-group \
  --db-parameter-group-name myactivepg \
  --parameters "ParameterName='rds.group_replication_enabled',ParameterValue='1',ApplyMethod=pending-reboot" \
               "ParameterName='rds.custom_dns_resolution',ParameterValue='1',ApplyMethod=pending-reboot" \
               "ParameterName='enforce_gtid_consistency',ParameterValue='ON',ApplyMethod=pending-reboot" \
               "ParameterName='gtid-mode',ParameterValue='ON',ApplyMethod=pending-reboot" \
               "ParameterName='binlog_format',ParameterValue='ROW',ApplyMethod=immediate" \
               "ParameterName='slave_preserve_commit_order',ParameterValue='ON',ApplyMethod=immediate" \
               "ParameterName='group_replication_group_name',ParameterValue='11111111-2222-3333-4444-555555555555',ApplyMethod=pending-reboot"

For Windows:

aws rds modify-db-parameter-group ^
  --db-parameter-group-name myactivepg ^
  --parameters "ParameterName='rds.group_replication_enabled',ParameterValue='1',ApplyMethod=pending-reboot" ^
               "ParameterName='rds.custom_dns_resolution',ParameterValue='1',ApplyMethod=pending-reboot" ^
               "ParameterName='enforce_gtid_consistency',ParameterValue='ON',ApplyMethod=pending-reboot" ^
               "ParameterName='gtid-mode',ParameterValue='ON',ApplyMethod=pending-reboot" ^
               "ParameterName='binlog_format',ParameterValue='ROW',ApplyMethod=immediate" ^
               "ParameterName='slave_preserve_commit_order',ParameterValue='ON',ApplyMethod=immediate" ^
               "ParameterName='group_replication_group_name',ParameterValue='11111111-2222-3333-4444-555555555555',ApplyMethod=pending-reboot"

Step 2: Create new RDS for MySQL DB instances for the active-active cluster

Active-active clusters are supported for version 8.0.35 and higher RDS for MySQL DB instances. You can create up to nine new DB instances for the cluster.

You can use the AWS Management Console or the AWS CLI to create new DB instances. For more information about creating a DB instance, see Creating an Amazon RDS DB instance. When you create the DB instance, associate it with a DB parameter group that you created or modified in the previous step.

Step 4: Specify the DB instances in the active-active cluster

In the DB parameter group associated with each DB instance, set the group_replication_group_seeds parameter to the endpoints of the DB instances you want to include in the cluster.

You can use the AWS Management Console or the AWS CLI to set the parameter. You don't need to reboot the DB instance after setting this parameter. For more information about setting parameters, see Modifying parameters in a DB parameter group.

The following example runs the modify-db-parameter-group AWS CLI command to set the parameters:

For Linux, macOS, or Unix:

aws rds modify-db-parameter-group \
  --db-parameter-group-name myactivepg \
  --parameters "ParameterName='group_replication_group_seeds',ParameterValue='myactivedb1.123456789012.us-east-1.rds.amazonaws.com:3306,myactivedb2.123456789012.us-east-1.rds.amazonaws.com:3306,myactivedb3.123456789012.us-east-1.rds.amazonaws.com:3306',ApplyMethod=immediate"

For Windows:

aws rds modify-db-parameter-group ^
  --db-parameter-group-name myactivepg ^
  --parameters "ParameterName='group_replication_group_seeds',ParameterValue='myactivedb1.123456789012.us-east-1.rds.amazonaws.com:3306,myactivedb2.123456789012.us-east-1.rds.amazonaws.com:3306,myactivedb3.123456789012.us-east-1.rds.amazonaws.com:3306',ApplyMethod=immediate"
Tip

Make sure you set the group_replication_group_seeds parameter in each DB parameter group that is associated with a DB instance in the active-active cluster.

Step 5: Initialize the group on a DB instance and start replication

You can choose any new DB to initialize the group and start replication. To do so, complete the following steps:

  1. Choose a DB instance in the active-active cluster, and connect to that DB instance in a SQL client. For more information about connecting to an RDS for MySQL DB instance, see Connecting to a DB instance running the MySQL database engine.

  2. In the SQL client, run the following stored procedures and replace group_replication_user_password with the password for the rdsgrprepladmin user. The rdsgrprepladmin user is reserved for Group Replication connections in an active-active cluster. The password for this user must be the same on all of the DB instances in an active-active cluster.

    call mysql.rds_set_configuration('binlog retention hours', 168); -- 7 days binlog
call mysql.rds_group_replication_create_user('group_replication_user_password');
call mysql.rds_group_replication_set_recovery_channel('group_replication_user_password');
call mysql.rds_group_replication_start(1);

    This example sets the binlog retention hours value to 168, which means that binary log files are retained for seven days on the DB instance. You can adjust this value to meet your requirements.

    This example specifies 1 in the mysql.rds_group_replication_start stored procedure to initialize a new group with the current DB instance.

    For more information about the stored procedures called in the example, see Managing active-active clusters.

Step 6: Start replication on the other DB instances in the active-active cluster

For each of the DB instances in the active-active cluster, use a SQL client to connect to the instance, and run the following stored procedures. Replace group_replication_user_password with the password for the rdsgrprepladmin user.

call mysql.rds_set_configuration('binlog retention hours', 168); -- 7 days binlog
call mysql.rds_group_replication_create_user('group_replication_user_password');
call mysql.rds_group_replication_set_recovery_channel('group_replication_user_password');
call mysql.rds_group_replication_start(0);

This example sets the binlog retention hours value to 168, which means that binary log files are retained for seven days on each DB instance. You can adjust this value to meet your requirements.

This example specifies 0 in the mysql.rds_group_replication_start stored procedure to join the current DB instance to an existing group.

Tip

Make sure you run these stored procedures on all of the other DB instances in the active-active cluster.

Step 7: (Recommended) Check the status of the active-active cluster

To make sure each member of the cluster is configured correctly, check the status of the cluster by connecting to a DB instance in the active-active cluster, and running the following SQL command:

SELECT * FROM performance_schema.replication_group_members;

Your output should show ONLINE for the MEMBER_STATE of each DB instance, as in the following sample output:

+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 9854d4a2-5d7f-11ee-b8ec-0ec88c43c251 | ip-10-15-3-137 |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
| group_replication_applier | 9e2e9c28-5d7f-11ee-8039-0e5d58f05fef | ip-10-15-3-225 |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
| group_replication_applier | a6ba332d-5d7f-11ee-a025-0a5c6971197d | ip-10-15-1-83  |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

For information about the possible MEMBER_STATE values, see Group Replication Server States in the MySQL documentation.

Step 8: (Optional) Import data into a DB instance in the active-active cluster

You can import data from a MySQL database into a DB instance in the active-active cluster. After the data is imported, Group Replication replicates it to the other DB instances in the cluster.

For information about importing data, see Importing data to an Amazon RDS MariaDB or MySQL database with reduced downtime.

Adding a DB instance to an active-active cluster

You can add a DB instance to an active-active cluster by restoring a DB snapshot or by restoring a DB instance to a point in time. An active-active cluster can include up to nine DB instances.

When you recover a DB instance to a point in time, it usually includes more recent transactions than a DB instance that was restored from a DB snapshot. When the DB instance has more recent transactions, fewer transactions need to be applied when you start replication. So, using point-in-time recovery to add a DB instance to a cluster is usually faster than restoring from a DB snapshot.

Adding a DB instance to an active-active cluster using point-in-time recovery

You can add a DB instance to an active-active cluster by performing point-in-time recovery on a DB instance in the cluster.

For information about recovering a DB instance to a point in time in a different AWS Region, see Replicating automated backups to another AWS Region.

To add a DB instance to an active-active cluster using point-in-time recovery

  1. Create a new DB instance by performing point-in-time recovery on a DB instance in the active-active cluster.

    You can perform point-in-time recovery on any DB instance in the cluster to create the new DB instance. For instructions, see Restoring a DB instance to a specified time.

    Important

    During point-in-time-recovery, associate the new DB instance with a DB parameter group that has the active-active cluster parameters set. Otherwise, Group Replication won't start on the new DB instance. For information about the parameters and the required setting for each one, see Required parameter settings for active-active clusters.

    Tip

    If you take a snapshot of the DB instance before you start point-in-time recovery, you might be able to reduce the amount of time required to apply transactions on the new DB instance.

  2. Add the DB instance to the group_replication_group_seeds parameter in each DB parameter group associated with a DB instance in the active-active cluster, including the DB parameter group that you associated with the new DB instance.

    For more information about setting parameters, see Modifying parameters in a DB parameter group.

  3. In a SQL client, connect to the new DB instance, and call the mysql.rds_group_replication_set_recovery_channel stored procedure. Replace group_replication_user_password with the password for the rdsgrprepladmin user.

    call mysql.rds_group_replication_set_recovery_channel('group_replication_user_password');

  4. Using the SQL client, call the mysql.rds_group_replication_start stored procedure to start replication:

    call mysql.rds_group_replication_start(0);

Adding a DB instance to an active-active cluster using a DB snapshot

You can add a DB instance to an active-active cluster by creating a DB snapshot of a DB instance in the cluster and then restoring the DB snapshot.

For information about copying a snapshot to a different AWS Region, see Cross-Region snapshot copying.

To add a DB instance to an active-active cluster using a DB snapshot

  1. Create a DB snapshot of a DB instance in the active-active cluster.

    You can create a DB snapshot of any DB instance in the cluster. For instructions, see Creating a DB snapshot for a Single-AZ DB instance.

  2. Restore a DB instance from the DB snapshot.

    During the snapshot restore operation, associate the new DB instance with a DB parameter group that has the active-active cluster parameters set. For information about the parameters and the required setting for each one, see Required parameter settings for active-active clusters.

    For information about restoring a DB instance from a DB snapshot, see Restoring from a DB snapshot.

  3. Add the DB instance to the group_replication_group_seeds parameter in each DB parameter group associated with a DB instance in the active-active cluster, including the DB parameter group that you associated with the new DB instance.

    For more information about setting parameters, see Modifying parameters in a DB parameter group.

  4. In a SQL client, connect to the new DB instance, and call the mysql.rds_group_replication_set_recovery_channel stored procedure. Replace group_replication_user_password with the password for the rdsgrprepladmin user.

    call mysql.rds_group_replication_set_recovery_channel('group_replication_user_password');

  5. Using the SQL client, call the mysql.rds_group_replication_start stored procedure to start replication:

    call mysql.rds_group_replication_start(0);

Monitoring active-active clusters

You can monitor your active-active cluster by connecting to a DB instance in the cluster, and running the following SQL command:

SELECT * FROM performance_schema.replication_group_members;

Your output should show ONLINE for the MEMBER_STATE of each DB instance, as in the following sample output:

+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 9854d4a2-5d7f-11ee-b8ec-0ec88c43c251 | ip-10-15-3-137 |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
| group_replication_applier | 9e2e9c28-5d7f-11ee-8039-0e5d58f05fef | ip-10-15-3-225 |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
| group_replication_applier | a6ba332d-5d7f-11ee-a025-0a5c6971197d | ip-10-15-1-83  |        3306 | ONLINE       | PRIMARY     | 8.0.35         | MySQL                      |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

For information about the possible MEMBER_STATE values, see Group Replication Server States in the MySQL documentation.

Stopping Group Replication on a DB instance in an active-active cluster

You can stop Group Replication on a DB instance in an active-active cluster. When you stop Group Replication, the DB instance is placed in super-read-only mode until replication is restarted or that DB instance is removed from the active-active cluster. For information about super-read-only mode, see the MySQL documentation.

To stop Group Replication temporarily for an active-active cluster

  1. Connect to a DB instance in the active-active cluster using a SQL client.

    For more information about connecting to an RDS for MySQL DB instance, see Connecting to a DB instance running the MySQL database engine.

  2. In the SQL client, call the mysql.rds_group_replication_stop stored procedure:

    call mysql.rds_group_replication_stop();

Renaming a DB instance in an active-active cluster

You can change the name of a DB instance in an active-active cluster. To rename more than one DB instance in an active-active cluster, do so one DB instance at a time. So, rename one DB instance and rejoin it to the cluster before you rename the next DB instance.

To rename a DB instance in an active-active cluster

  1. Connect to the DB instance in a SQL client, and call the mysql.rds_group_replication_stop stored procedure:

    call mysql.rds_group_replication_stop();

  2. Rename the DB instance by following the instructions in Renaming a DB instance.

  3. Modify the group_replication_group_seeds parameter in each DB parameter group associated with a DB instance in the active-active cluster.

    In the parameter setting, replace the old DB instance endpoint with the new DB instance endpoint. For more information about setting parameters, see Modifying parameters in a DB parameter group.

  4. Connect to the DB instance in a SQL client, and call the mysql.rds_group_replication_start stored procedure:

    call mysql.rds_group_replication_start(0);

Removing a DB instance from an active-active cluster

When you remove a DB instance from an active-active cluster, it reverts to a standalone DB instance.

To remove a DB instance from an active-active cluster

  1. Connect to the DB instance in a SQL client, and call the mysql.rds_group_replication_stop stored procedure:

    call mysql.rds_group_replication_stop();

  2. Modify the group_replication_group_seeds parameter for the DB instances that will remain in the active-active cluster.

    In the group_replication_group_seeds parameter, delete the DB instance that you are removing from the active-active cluster. For more information about setting parameters, see Modifying parameters in a DB parameter group.

  3. Modify the parameters of the DB instance you are removing from the active-active cluster so that it is no longer part of the cluster.

    You can either associate the DB instance with a different parameter group, or modify the parameters in the DB parameter group associated with the DB instance. The parameters to modify include group_replication_group_name, rds.group_replication_enabled, and group_replication_group_seeds. For more information about active-active cluster parameters, see Required parameter settings for active-active clusters.

    If you modify the parameters in a DB parameter group, make sure the DB parameter group isn't associated with other DB instances in the active-active cluster.

  4. Reboot the DB instance you removed from the active-active cluster for the new parameter settings to take effect.

    For instructions, see Rebooting a DB instance.

Limitations for RDS for MySQL active-active clusters

The following limitations apply to active-active clusters for RDS for MySQL:

  • The master user name can't be rdsgrprepladmin for DB instances in an active-active cluster. This user name is reserved for Group Replication connections.

  • For DB instances with read replicas in active-active clusters, a prolonged replication status other than Replicating can cause log files to exceed storage limits. For information about the status of read replicas, see Monitoring read replication.

  • Blue/green deployments aren't supported for DB instances in an active-active cluster. For more information, see Using Amazon RDS Blue/Green Deployments for database updates.

  • Kerberos authentication isn't supported for DB instances in an active-active cluster. For more information, see Using Kerberos authentication for MySQL.

  • The DB instances in a Multi-AZ DB cluster can't be added to an active-active cluster.

    However, the DB instances in a Multi-AZ DB instance deployment can be added to an active-active cluster.

    For more information, see Configuring and managing a Multi-AZ deployment.

  • Tables that don't have a primary key aren't replicated in an active-active cluster because writes are rejected by the Group Replication plugin.

  • Non-InnoDB tables aren't replicated in an active-active cluster.

  • Active-active clusters don't support concurrent DML and DDL statements on different DB instances in the cluster.

  • You can't configure an active-active cluster to use single-primary mode for the group's replication mode. For this configuration, we recommend using a Multi-AZ DB cluster instead. For more information, see Multi-AZ DB cluster deployments.

  • Multi-source replication isn't supported for DB instances in an active-active cluster.

  • A cross-Region active-active cluster can't enforce certificate authority (CA) verification for Group Replication connections.