How do I create an Amazon RDS MySQL cross-Region replica in another AWS account?

Short description

You can create an Amazon RDS for MySQL replica in another AWS Region and account from the source DB instance for the following use cases:

• Improving disaster recovery
• Scaling out globally
• Migrating between AWS Regions and accounts

Note: There's no direct way to create a cross-Region replica in another AWS account using the Amazon RDS console or AWS Command Line Interface (AWS CLI). The steps outlined in this article set up an external binlog-based replication between two RDS for MySQL instances in different AWS accounts or Regions.

Resolution

To create an Amazon RDS for MySQL cross-Region replica in another AWS account, follow these steps.

Note: Account A contains the primary RDS for MySQL instance in the source Region. Account B contains the replica RDS for MySQL in the target Region.

1.    In Account A (the primary Amazon RDS for MySQL instance), make sure that binary logging is active. By default, automated backups and binary logging are activated in RDS for MySQL. Binary logging is activated whenever automated backups are activated.

Note: An outage occurs if you change the backup retention period from "0" to a non-zero value, or vice versa.

2.    Update your binlog retention period using the following command:

mysql>  CALL mysql.rds_set_configuration(name,value);

Tip: Choose a time period that retains the binary log files on your replication source long enough for changes to be applied before deletion. Amazon RDS retains binlog files on a MySQL instance for up to 168 hours (7 days). For more information, see mysql.rds_set_configuration.

For example, the following syntax sets the binlog retention period to 24 hours:

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

3.    Create a replication user on the primary instance in Account A, and then grant the required privileges:

mysql> CREATE USER 'repl_user'@'<domain_name>' IDENTIFIED BY '<password>';

4.    Grant the (required) REPLICATION CLIENT and REPLICATION SLAVE privileges to the user created in Step 3:

mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO  'repl_user'@'<domain_name>';

5.    Create a cross-Region read replica in the primary account (Account A) with the target Region selected.

6.    Log in to the created replica instance in Account A. Then, confirm that the replica is caught up with the primary DB instance:

mysql> SHOW SLAVE STATUS\G

Note: Check to make sure that the Seconds_Behind_Master value is "0". When the value is "0", the replica is caught up to the primary DB instance. For more information, see Monitoring read replication.

7.    After the read replica is caught up to the primary DB instance, stop replication on the replica instance created in Step 5. To stop replication, use the following syntax:

mysql> call mysql.rds_stop_replication();

8.    Run the SHOW SLAVE STATUS command on the replica, and then record the output values for Relay_Master_Log_File and Exec_Master_Log_Pos. The Relay_Master_Log_File and Exec_Master_Log_Pos values are your binary log coordinates, which are used for setting up external replication in later steps.

9.    Create a DB snapshot of your replica instance in Account A.

(Optional) Or, you can use a native tool that generates logical backups (such as mysqldump) to export data from the replica instance in Account A. The native tool can then be used to import the data to a newly created RDS for MySQL instance of the same version in Account B. With this approach, you don't have to copy and share snapshots or AWS KMS keys between the two accounts. If you decide to use this approach, skip to Step 12 to set up network access and replication between both instances. Before you take this approach, you must import data into the Amazon RDS for MySQL instance in Account B.

10.    Share the DB snapshot with Account B.

Note: If your DB snapshot is encrypted, then the AWS KMS key used to encrypt the snapshot must be shared with the target account. For more information, see Sharing encrypted snapshots.

11.    Restore the DB snapshot in Account B.

Note: A DB instance can't be restored from a shared encrypted snapshot. Instead, make a copy of the DB snapshot and restore the DB instance from the copied version.

12.    Set up network access between Account A (the source account) and Account B (destination account). The network access allows traffic to flow between the source and destination accounts.

13.    Configure the inbound security group rules for Account A's primary DB instance. This configuration allows traffic to flow over the public internet from the newly created RDS for MySQL instance in Account B (the destination account). The security groups protect your Amazon RDS for MySQL instance.

For private replication traffic, a VPC peering connection must be created and accepted between the two AWS accounts.

14.    Set up external replication on your target instance in Account B. Use repl_user within the command as a parameter. Note: The CALL mysql.rds_set_external_master command should be run by a DB user with execute command privileges.

mysql> CALL mysql.rds_set_external_master (
  host_name
  , host_port
  , replication_user_name
  , replication_user_password
  , mysql_binary_log_file_name
  , mysql_binary_log_file_location
  );

For example:

mysql> CALL mysql.rds_set_external_master (mytestinnstance.us-east-1.rds.amazonaws.com', 3306, 'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);

mytestinnstance.us-east-1.rds.amazonaws.com: primary instance endpoint 3306: primary instance port repl_user: replication user name created in Step 3 password: user password created in Step 3 mysql-bin-changelog.000031: binary log file name from the output of Step 8 107: binary log position from the output of Step 8

15.    Start the replication on the restored instance in Account B:

CALL mysql.rds_start_replication;

Here's an example output:

+-------------------------+
| Message                 |
+-------------------------+
| Slave running normally. |
+-------------------------+

16.    Run the following command on Account B instance to check your replication status:

mysql> show replica status \G

Note: For MySQL version 8.0.22 and higher, SHOW SLAVE STATUS is deprecated, and SHOW REPLICA STATUS is available to use instead. For more information, see Checking replication status on the MySQL website.

17.    Delete the replica (which acted as an intermediate instance) created in Step 5. This replica was used to extract binary log coordinates without having to suspend writes on the primary instance in Account A.

Cross-Region replication considerations

Consider the following approaches for cross-Region replication:

• A source DB instance can have cross-Region read replicas in multiple AWS Regions. For more information, see Creating a read replica in a different AWS Region.
• You can expect to see a higher lag time for any read replica that is in a different AWS Region than the source instance. This lag time comes from the longer network channels between regional data centers. For information about replication lag time, see Monitoring read replication.
• The data transferred for cross-Region replication incurs Amazon RDS data transfer charges. For more information about these data transfer charges, see cross-Region replication costs.

---

Related information

Cross-Region read replicas for Amazon RDS for MySQL