Seamless Migration of RDS MySQL Between AWS Accounts
Written on
Chapter 1: Introduction to RDS Migration
Transferring your RDS MySQL database from one AWS account to another can seem as easy as replanting a favorite houseplant. Just take a snapshot, share it with another account, restore it, and you're done! However, if you're aiming for minimal downtime—say, just one minute—and no data gaps, the process becomes a bit more complex.
Unfortunately, AWS does not provide a direct feature for sharing or moving RDS instances between accounts. Moreover, if you want to securely migrate without exposing your data to the public Internet, the costs can add up. While AWS Database Migration Service (DMS) exists, many find it overly complicated or unreliable.
This guide will walk you through the migration process step-by-step, making it easier to navigate the often turbulent waters of production database migration.
Chapter 2: Preparing for Migration
I currently have two accounts: Account A and Account B. Before I can decommission Account A, I need to transfer my critical production database to Account B.
Section 2.1: Initial Setup in Account A
Step 1: Enable Binary Logging
Binary logging is typically enabled by default on RDS databases with automatic backups. You can verify this by executing the command CALL mysql.rds_show_configuration;. If it’s not enabled, you can activate it with CALL mysql.rds_set_configuration('binlog retention hours', 24);.
Important: Always create a backup of your original database before proceeding!
Step 2: Create a Replication User
A dedicated user is necessary to manage the replication process. This user will require specific permissions:
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON . TO 'repl_user'@'%';
Step 3: Create a Read Replica of the Source Database
To migrate within the same AWS region, simply create a read replica using the default settings:
Navigate to RDS DB page > Actions > Create read replica.
If you're moving to a different region, select the target region during this step.
Step 4: Ensure the Read Replica is Up-to-Date
Run SHOW REPLICA STATUS to confirm that the Seconds_Behind_Source value is "0." This indicates that the replica is fully synchronized with the primary database.
Step 5: Stop Replication on the Read Replica
Once the read replica is up to date, execute:
CALL mysql.rds_stop_replication();
Step 6: Log the Binary Log Coordinates
Use SHOW REPLICA STATUS; on the replica to obtain the values for Relay_Source_Log_File and Exec_Source_Log_Pos, which are essential for external replication in the upcoming steps.
Step 7: Create a Snapshot of the Read Replica and Share it
Take a snapshot of the read replica in Account A and share it with Account B. If your snapshot is encrypted, remember to share the AWS KMS key used for encryption, granting Account B permission to use it.
Chapter 3: Restoring the Database in Account B
Step 8: Restore the Snapshot in Account B
If your snapshot is encrypted, you’ll need to create a copy before restoring it, as shared encrypted snapshots cannot be restored directly.
Decide whether to allow public access or keep it private, as this will affect your migration strategy.
Step 9: Network Setup for Migration
You can choose between two methods to facilitate data transfer between the source and destination databases: through the public Internet or via AWS's internal VPC network. The latter is more secure but involves additional setup.
Option 1: Public Internet Migration
Ensure both databases can communicate by adjusting inbound security group rules.
Option 2: AWS VPC Intranet Migration
For a private setup, create a VPC peering connection between the two accounts, and configure the corresponding security groups accordingly.
Chapter 4: Executing the Migration
With everything prepared, it’s time to initiate the migration.
Step 10: Set Up External Replication on Account B
Run the following command using a user with execution privileges:
CALL mysql.rds_set_external_master('source_db_host_name', source_db_host_port, 'repl_user', 'your_password', 'mysql_binary_log_file_name', mysql_binary_log_file_location);
Step 11: Start Replication in Account B
Run:
CALL mysql.rds_start_replication;
You should see the message: "Slave running normally!"
Step 12: Verify Replication Status
Execute SHOW REPLICA STATUS on the Account B instance to check the replication status.
Chapter 5: Cleanup and Final Thoughts
Finally, it's time to clean up. Remove the read replica and its snapshot in Account A, as they have served their purpose.
Switch your application to the new database, stop replication, and reset the master:
CALL mysql.rds_stop_replication();
CALL mysql.reset_external_master;
DROP USER 'repl_user'@'%';
As a final note, consider reviewing monitoring options for replication and cross-region costs.
I hope this guide has been helpful and you've successfully migrated your production app with minimal disruption!
The first video provides an insightful tutorial on migrating Amazon RDS instances across different AWS accounts with practical steps.
The second video introduces Amazon Aurora and Amazon RDS Blue/Green deployments, emphasizing efficient database management practices.