How to setup RDS Replication across AWS accounts

Every day, engineering teams are working tirelessly to solve problems and more often than not, this means choosing between the best of many possible solutions. When we share our approaches to technical problems, we hope to fast track this process for our teams.

Recently, Careem’s DataOPS team was tasked with the migration of a database from one AWS account to another with minimum downtime. Here you will find the specific steps we took to set up a live RDS MySQL replication across AWS accounts.

The same approach can also be applied to set up production account data for testing in another AWS account like QA. When you are testing on live data, sensitive information must be scrubbed by adding database triggers on the QA account.

Note: We have tested this functionality on AWS RDS MySQL 5.7 and it works.

Let’s suppose you have a database named DB1. For the sake of clarity, the accounts will be referred to as “Account A” and “Account B”.

Do the following task on On Account A.

• Create one new exclusive VPC Security group to be used in new read replica. Let's name it sg_share_RDS.
• On “DB1”, run following to make sure binlog files there for at least 24 hours.

call mysql.rds_set_configuration(‘binlog retention hours’,24);

• Create new RDS Subnet Group, with at least two public subnets attached to this subnet group, let’s name it “share_rds_subnet”.
• Create new Read Replica from database “DB1” with port 3316(or any nondefault port i.e. 3306).
• Set Public access to ‘Yes’ and assign Subnet Group “share_rds_subnet” to Read Replica. Also, enable auto backup on this new read replica.
• Assign security group sg_share_RDS to newly created read replica & Allow port 3316 to your VPN Security group to make a connection to this read replica.

Lock every database user in the new read replica, after switch off (read_only=0) in read replica parameter group, we prefer you also have exclusive DB parameter group for newly created read replica. After locking down all users, create one database user that is exclusively used for replication with a tough password. For example

GRANT REPLICATION SLAVE ON *.* TO <your_user>@'%' IDENTIFIED BY <your_password>;

Run below command

SHOW SLAVE STATUS; 

And check seconds_behind_master must be zero. on a newly created read replica. Make sure the newly create read replica is ready and fully synced with the master. Now do the following steps.

Stop replication on read replica by calling

CALL mysql.rds_stop_replication().

• Run following command on stop read replica and save values of (Master_Log_File , Read_Master_Log_Pos) to use it later.

SHOW MASTER STATUS;

• If you are on Mac run dig <your stop read replica endpoint> note down the public IP address.
• Take a snapshot of a read replica in account “A” and share snapshot to other AWS account “B” from AWS console.
• After share snapshot restores RDS snapshot in AWS account (“B”) make sure you also have designated security group and subnet group (with at least two public subnets) on the target AWS account.
• Once a snapshot is restored and available, connect to it and run the following commands. Now run dig <newly created RDS endpoint in account “B”>

Get the public IP address of newly created RDS in account “B” and allow this IP in account “A” ( source DB ) security group to connect on the port which you defined for your read replica. This means only this IP (from the account “B”) can connect to source DB in account “A”.

Now run following in your target DB in account “B”.

CALL mysql.rds_set_external_master(<Public IP Address of stop Read Replica>, <port of read replica>, <username> , <password> , Master_Log_File,Read_Master_Log_Pos, 1);
CALL mysql.rds_start_replication();

Now run following command

SHOW SLAVE STATUS; 

on newly created DB in the account “B” and check if its successfully connect to its master database (The read replica on AWS account “A”).