How can I use binary logs from an Amazon RDS for MySQL active DB instance to replicate to an on-premises standby instance?
Last updated: 2020-08-14
How can I replicate an Amazon Relational Database Service (Amazon RDS) for MySQL DB instance by using binary logs to an external, on-premises environment?
To migrate data from Amazon RDS for MySQL to an on-premises database server, create a read replica on AWS, and then switch the replication target from the Amazon RDS MySQL read replica to the on-premises server.
If you use GTID-based replication, see Configuring GTID-based replication with an external master instance.
1. Confirm that automated backups are enabled on the DB instance that you want to replicate. In this example, the DB instance is RDS-active.
Note: The minimum backup retention period is one day.
2. Create a read replica of the DB instance using the same configuration. In this example, the replica is RDS-standby.
3. Log in to the RDS-standby DB instance, and confirm that the replica is caught up with RDS-active:
mysql> show slave status \G
Note: The seconds_behind_master must be 0, which means there is no replica lag.
4. Stop replication on RDS-standby:
mysql> call mysql.rds_stop_replication;
5. Record data from the replica, and then note the -log_file and -log_position parameters:
mysql> show slave status \G
Note: The -log_file is the value of Relay_Master_Log_File, and -log_position is the value of Exec_Master_Log_Pos.
6. Exit the terminal, and use mysqldump (or a similar utility) to create a backup of RDS-standby that will be replicated to the target server. In this example, the target on-premises server is MySQL-target.
$ mysqldump -h hostname -u username -p dbname > backup_file_name.sql
7. After you have created the backup, transfer the backup file to the target on-premises server by logging in to MySQL-target.
8. Create a new database, and restore the database using dumpfile to the new external DB instance:
$ mysql -h hostname -u username -p dbname < backup_file_name.sql
9. Log in to the RDS-active DB instance, set up a replication user, and grant the necessary privileges to the user:
mysql> create user repl_user@'%' identified by 'repl_user'; mysql>grant replication slave, replication client on *.* to repl_user@'%'; mysql>show grants for repl_user@'%';
Note: Be sure to replace repl_user with your own replication user name.
10. Log in to the target DB instance and stop the MySQL server.
11. Modify the my.cnf file parameters to point to your unique server ID, such as server_id=2, and the name of the database that you wish to replicate from the DB instance, such as replicate-do-db=test.
12. Save the file.
13. Restart MySQL server on MySQL-target.
14. Establish a connection to the RDS-active DB instance:
mysql> change master to master_host='rds-endpoint',master_user='repl_user', master_password='password', master_log_file='mysql-bin.000001', master_log_pos= 107;
15. Confirm that MySQL-target can connect to RDS-active.
Note: If you use Amazon Elastic Compute Cloud (Amazon EC2) as an external MySQL instance, then allow connections from the security group or IP address in the security group of the RDS-active DB instance. If you use an external MySQL server, run telnet to test the connectivity. For more information, see How do I resolve problems when connecting to my Amazon RDS DB instance?
The RDS-Endpoint is the endpoint for the RDS-active DB instance. The user name and password are the same user name and password that you created in step 9. The MASTER_LOG_FILE and MASTER_LOG_POS are the values noted in step 5.
16. Log in to MySQL-target, and start the replication:
mysql> start slave;
17. Check that the replication is synchronizing between RDS-active and MySQL-target:
mysql> Show slave status\G
18. After the seconds behind master equal zero, you can delete the RDS-standby DB instance.