How can I replicate an Amazon RDS for MySQL instance by using binary logs to an on-premises environment?
Last updated: 2018-12-27
How can I replicate an Amazon Relational Database Service (Amazon RDS) for MySQL 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 change the MySQL instance from the AWS read replica to the on-premises server.
Note: The minimum backup retention period should be one day.
3. Create a read replica of the DB instance.
4. Run a command similar to the following to show the replica instance's status:
mysql> show slave status \G;
Note: The seconds behind master must be zero.
5. After the read replica is caught up to the master instance, stop replication by running a command similar to the following:
mysql> CALL mysql.rds_stop_replication;
6. Record data from the replica, and then note the -log_file and -log_position parameters by running a command similar to the following:
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.
7. Dump the data from the read replica by using a mysqldump similar to the following:
mysql> mysqldump -h hostname -u username -p dbname > backup_file_name.sql
8. Restore the database using dumpfile to the newly external DB instance similar to the following:
mysql> mysql -h hostname -u username -p dbname < backup_file_name.sql
9. On the mysql master instance, set up a replication user similar to the following:
mysql>create user rep2user@'%' identified by 'rep2user'; mysql>grant replication slave, replication client on *.* to rep2user@'%'; mysql>show grants for rep2user@'%';
Note: Be sure to replace rep2user with your own user name.
10. In the on-premises environment, stop the MySQL server, and then modify my.cnf file and set "server-id=2".
11. Restart MySQL server on the on-premises server.
12. Run the following command to start replication:
13. Confirm that the on-premises MySQL server can connect to the master RDS instance.
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 master RDS 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 database instance?
14. Establish a connection to the master RDS instance by running a command similar to the following:
CHANGE MASTER TO MASTER_HOST='RDS-Endpoint',MASTER_USER='username', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;
Note: The RDS-Endpoint is the master RDS endpoint. The user name and password are the same user name and password that you created previously. The MASTER_LOG_FILE and MASTER_LOG_POS are the values noted previously.
15. Start the replication by running a command similar to the following on the external MySQL server:
16. Check that the replication is synchronizing the master RDS instance to the external MySQL server by running a command similar to the following:
Show slave status\G;
17. After the seconds behind master equal zero, you can delete the read replica instance.