How do I create a cross-Region read replica of my Amazon RDS for MySQL DB instance in another AWS account?

Short description

Use AWS Database Migration Service (AWS DMS), or set up an external binlog-based replication between two Amazon RDS for MySQL DB instances in different AWS Regions or accounts. You can't use the Amazon RDS console or AWS Command Line Interface (AWS CLI) to create a cross-Region read replica in another account.

Note: When you create cross-Region read replicas with longer network channels between Regional data centers, you might experience replication lag. Also, there are cross-Region replication costs.

Resolution

Note: In the following resolution, Account A contains the primary DB instance in the source Region. Account B contains the replica DB instance in the destination Region.

To create an Amazon RDS for MySQL cross-Region read replica in another account, complete the following steps:

1. For Account A, activate binary logging.
   Note: An outage occurs when you change the backup retention period. Also, there are additional costs when you transfer data.

2. Run the following command to update your binlog retention period:

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

   Note: Replace value with the number of hours for changes to apply before Amazon RDS deletes the binary logs after 168 hours. For more information, see mysql.rds_set_configuration.

3. Run the following command to create a replication user on the primary DB instance for Account A:

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

   Note: Replace domain_name and password with your domain name and password. For information, see CREATE USER statement on the MySQL website.

4. Run the following command to grant the required permissions to the replication user:

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

   Note: Replace domain_name with your domain name. For information, see REPLICATION CLIENT and REPLICATION SLAVE on the MySQL website.

5. Create a cross-Region read replica for Account A, and then select the destination Region.

6. Log in to the read replica of the DB instance that you created for Account A. Then, run the following command to confirm that the status of the read replica matches the primary DB instance.
   MySQL versions 8.0 and earlier:

   mysql> show slave status \G

   MySQL versions 8.4 and later:

   mysql> show replica status \G

   Note: The read replica status matches when the Seconds_Behind_Master value is 0.

7. Run the following command to stop the replication on the replica DB instance:

   mysql> call mysql.rds_stop_replication();

8. Log in to the replica DB instance.

9. Run the following command to record the output values.
   MySQL versions 8.0 and earlier:

   mysql> show slave status \G

   MySQL versions 8.4 and later:

   mysql> show replica status \G

   Note: Note the output values for Relay_Master_Log_File and Exec_Master_Log_Pos. These output values are your binary log coordinates that you use in a later step.

10. Create a DB snapshot of your replica DB instance for Account A.

11. (Optional) To generate logical backups and export data from the replica DB instance for Account A, you can use a native tool, such as mysqldump. For more information, see mysqldump — A database backup program on the MySQL website. Use mysqldump to import data to a new DB instance of the same version for Account B. You don't need to copy and share snapshots or AWS Key Management Service (AWS KMS) keys between the two accounts.
    Note: If you use mysqldump, then proceed to step 14 after you import you data to the DB instance for Account B.

12. Share the DB snapshot with Account B.
    Note: If your DB snapshot is encrypted, then you must also share the AWS KMS key with Account B. For more information, see Sharing encrypted snapshots for Amazon RDS.

13. Restore the DB snapshot in Account B.
    Note: You can't restore a DB instance from a shared encrypted snapshot. Instead, copy the DB snapshot, and then restore the DB instance from the copied version.

14. Set up network access between Account A and Account B to allow traffic to flow between the two accounts.

15. Configure the inbound security group rules in the primary DB instance for Account A to allow traffic to the public internet from the DB instance in Account B.
    Note: For private replication traffic, you must create and accept a virtual private cloud (VPC) peering connection between the two accounts.

16. To set up the external replication on your destination DB instance for Account B, run the following command as a superuser.
    MySQL versions 8.0 and earlier:

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

    MySQL versions 8.4 and later:

    mysql> CALL mysql.rds_set_external_source ('host_name', 'host_port', 'repl_user_name', 'replication_user_password', 'mysql_binary_log_file_name', 'mysql_binary_log_file_location'); 

    Note: Replace host_name with the primary DB instance endpoint and host_port with the primary DB instance port. Replace replication_user_name and replication_user_password with the domain name and password that you created in step 3. Replace mysql_binary_log_file_name with the binary log file name and mysql_binary_log_file_location with the binary log coordinates that you noted in step 9.

17. Run the following command to start the replication on the restored DB instance for Account B:

    CALL mysql.rds_start_replication;

    Example output:

    +-------------------------+  
    | Message                 |  
    +-------------------------+  
    | Replica running normally. |  
    +-------------------------+

18. Run the following command to check the replica's status for Account B.
    MySQL versions 8.0 and earlier:

    mysql> show slave status \G

    MySQL versions 8.4 and higher:

    mysql> show replica status \G

19. Delete the read replica that you created in step 5.