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

Short description

To migrate data from an Amazon RDS for MySQL DB instance to an on-premises server, 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 global transaction identifier (GTID) based replication, then see Configuring binary log file position replication with an external source instance.

Resolution

Create a read replica of the active DB instance

Complete the following steps:

1. Confirm that you turned on automated backups for the active DB instance that you want to replicate.
   Note: The minimum backup retention period is 1 day. When you back up your data, you incur additional costs. For more information, see Amazon RDS pricing.

2. Create a read replica of the active DB instance with the same configuration.

3. Log in to the read replica DB instance.

4. Run the following command to confirm that the read replica DB instance matches your active DB instance.
   For MySQL version 8.0.23 and later:

   mysql> show replica status \G

   For MySQL version before 8.0.23:

   mysql> show slave status \G

   Note: When the replica instance doesn't lag behind your DB instance, the Seconds_Behind_Master parameter shows as 0.

5. Run the following command to stop the replication on the read replica DB instance:

   mysql> call mysql.rds_stop_replication;

6. To record the -log_file and -log_position parameters, run the following command on the read replica DB instance.
   For MySQL version 8.0.23 and later:

   mysql> show replica status \G

   For MySQL version before 8.0.23:

   mysql> show slave status \G

   Note: The -log_file is the value of relay_source_log_file, and -log_position is the value of exec_source_log_pos.

Create a dump file of the read replica DB instance

Complete the following steps:

1. Run the following command in the mysqldump client or a similar utility to create a backup of the read replica DB instance:

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

2. Log in to your on-premises server, and then transfer the dump file to the server.

Restore the dump file to the new DB instance

Complete the following steps:

1. Create a new external DB instance on the on-premises server.

2. Run the following command to restore the dump file to the new external DB instance:

   mysql -h hostname -u username -p dbname < backup_file_name.sql

3. Log in to your active DB instance.

4. Run the following command to configure a replication user and grant the necessary permissions 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: Replace repl_user with your replication username.

5. Log in to the new external DB instance, and then stop the on-premises server.

6. Modify the my.cnf file parameters to point to your unique server ID and database name that you want to replicate. For example, server_id=2 and replicate-do-db=test.

7. Save the file.

8. Restart the on-premises server.

Confirm that the new external DB instance can connect to the active DB instance

If you use Amazon Elastic Compute Cloud (Amazon EC2) as an external MySQL instance, then update your instance to allow connections from the security group. Or, allow connections from the IP address in the security group of your active DB instance. If you use an external MySQL server, then run telnet to test the connectivity. For more information, see Why can't I connect to my Amazon RDS DB instance?

Complete the following steps:

1. To change the parameters that the replica server uses to connect to the source, run the following command on the new external DB instance.
   For MySQL version 8.0.23 and later:

   mysql> change replication source to source_host='rds-endpoint', source_user='repl_user', source_password='password', source_log_file='mysql-bin.000001', source_log_pos=107;

   For MySQL version before 8.0.23:

   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;  
   

   Note: Replace rds-endpoint with the endpoint of your active DB instance. Use the username and password that you created when you restored the dump file to the new database. Replace the relay_source_file and exec_source_log_pos values with the -log_file and -log_position values of the read replica.
2. Confirm that the on-premises server can connect to your active DB instance.

Start the replication

Complete the following steps:

1. Log in to the on-premises server.

2. Run the following command to start the replication:
   For MySQL version 8.0.23 and later:

   mysql> start replica;

   For MySQL version before 8.0.23:

   mysql> start slave;

3. Run the following command to check that the replication synchronizes between your active DB instance and the on-premises server:
   For MySQL version 8.0.23 and later:

   mysql> show replica status\G

   For MySQL version before 8.0.23:

   mysql> show slave status\G

4. When the Seconds_Behind_Master parameter equals 0, delete the read replica DB instance.

Related information

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

Exporting data from a MySQL DB instance by using replication

Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)