How can I replicate using binary logs from an Amazon RDS for MySQL active instance to an on-premises standby instance?

Last updated: 2019-11-14

How can I replicate an Amazon Relational Database Service (Amazon RDS) for MySQL instance by using binary logs to an external, on-premises environment?

Short Description

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.

Resolution

1.    Confirm that automated backups are enabled on the instance that you want to replicate. In this example, the instance is called RDS-active.

Note: The minimum backup retention period should be one day.

2.    Create a read replica of the DB instance using the same configuration. In this example, the replica is called RDS-standby.

3.    Log in to the RDS-standby instance, and confirm that the replica is caught up with RDS-active:

mysql> show slave status \G

Note: The seconds behind master must be zero, 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 called MySQL-target.

$ mysqldump -h hostname -u username -p dbname > backup_file_name.sql

7.    After the backup is created, 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 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 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 will be replicated.

12.    Save the file.

13.    Restart MySQL server on MySQL-target.

14.    Establish a connection to the RDS-active 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 instance. If you use an external MySQL server, run telnet to test the connectivity. For more information, see How do I resolve problems connecting to my Amazon RDS DB instance?

The RDS-Endpoint is the endpoint for the RDS-active 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 instance.