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

Short description

To migrate data from Amazon RDS for MySQL to an on-premises database server, first create a read replica on AWS. Then, switch the replication target from the Amazon RDS for MySQL read replica to the on-premises server.

If you use GTID-based replication, then see Configuring binary log file position replication with an external source instance.

Resolution

1.    Confirm that automated backups are turned on for 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 create the backup, transfer the backup file to the target on-premises server by logging in to MySQL-target.

8.    Create a new database, and then 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 then grant the necessary privileges to the user. Make sure to replace repl_user with your own replication username.

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: For more information, see Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication).

10.    Log in to the target DB instance, and then stop the MySQL server.

11.    Modify the my.cnf file parameters to point to your unique server ID and the name of the database that you want to replicate from the DB instance. For example, server_id=2 and 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. To do this, run the following command on MySQL-target:

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, then 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 username and password are the same username 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;

Check that the replication is synchronizing between RDS-active and MySQL-target:

mysql> Show slave status\G

17.    After the seconds behind master equals zero, you can delete the RDS-standby DB instance.

Related information

How do I migrate to an Amazon RDS or Aurora MySQL-Compatible Edition DB instance using AWS DMS?

Exporting data from a MySQL DB instance by using replication