How do I create an Amazon RDS MySQL cross-Region replica in another AWS account?
Last updated: 2021-09-08
I want to create an Amazon Relational Database Service (Amazon RDS) MySQL replica in a different AWS Region and account from the source DB instance. How can I do this?
You can create an Amazon RDS 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 MySQL instances in different AWS accounts or Regions.
To create an Amazon RDS MySQL cross-Region replica in another AWS account, follow these steps.
Note: Account A contains the master RDS MySQL instance in the source Region. Account B contains the replica RDS MySQL in the target Region.
1. In Account A (the primary Amazon RDS MySQL instance), make sure that binary logging is enabled. By default, automated backups and binary logging are enabled in RDS MySQL. Binary logging is enabled whenever automated backups are enabled.
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>';
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 STAUS\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 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 an encrypted snapshot.
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 MySQL instance in Account B (the destination account). The security groups protect your Amazon RDS 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:
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 );
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:
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.