How do I migrate my Amazon RDS for MySQL DB instance using a custom start time?

Last updated: 2021-10-15

I want to migrate my Amazon Relational Database Service (Amazon RDS) for MySQL DB instance to another MySQL DB instance using a custom start time. How can I do this?

Short description

To migrate data from Amazon RDS for MySQL to another MySQL DB instance, you can use one of the following methods:

  • Binlog based replication
    Note: If you're using version MySQL version 8.0.17, the engine might not print the last binlog file position and file name during a crash recovery. As a result, you won't be able to use the binlog replication approach to migrate your data. Check the MySQL website for this known issue.
  • AWS DMS

Resolution

Binlog replication

Prerequisites:

  • Binlog replication uses binlog files that are generated on the source to record ongoing changes. Set the binlog_format parameter to the binary logging format of your choice (ROW, STATEMENT, or MIXED). 
  • Increase the binlog retention hours parameter to a larger value than your current value. This way, binlogs that haven't yet been shipped remain on the source Amazon RDS for MySQL instance.
  • Make sure that the Amazon RDS for MySQL instance is reachable from the target host.

To migrate data from Amazon RDS for MySQL to another MySQL DB instance using binlog replication, perform the following:

1.    Perform point-in-time restore from the source Amazon RDS DB instance with a custom start time (or point-in-time value).

2.    Create a backup of the point-in-time restore RDS instance.

For example, you can use mysqldump to perform this task:

mysqldump -h rdsendpoint -u username -p dbname > backupfile.sql

3.    Check the error log file of the point-in-time restore RDS instance and record the message related to the last applied binlog file and position. 

Here's an example log file message:

[Note] InnoDB: Last MySQL binlog file position 0 456, file name mysql-bin-changelog.152707

Note: You'll need to access the record binlog file, and any subsequent binlog files, for replication. The replication from the source RDS instance can only be performed using these files. The maximum binlog retention period in RDS MySQL can be set to seven days only and the default value is "NULL". (For more information, see mysql.rds_set_configuration.) Therefore, retain these binlog files on the source instance to complete in later steps.

4.    Set up a replication user and grant the necessary privileges to the user on the source Amazon RDS for MySQL instance:

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@'%';

5.    Transfer the backup file to the target on-premises server by logging in to MySQL-target. Create a new database, and restore the database using dumpfile to the new external DB instance:

$ mysql -h hostname -u username -p dbname < backupfile.sql

6.    Stop the target MySQL engine:

$ service mysqld stop

7. Modify the my.cnf file parameters to point to your unique server ID and the database that you're trying to replicate.

For example:

server_id=2
replicate-do-db=testdb

If you're replicating multiple databases, you can use the replicate-do-db option multiple times and specify those databases on separate lines like this:

replicate-do-db=<db_name_1>
replicate-do-db=<db_name_2>
replicate-do-db=<db_name_N>

For more information about creating a replication filter with the database name, see replicate-do-db on the MySQL website.

8.    Save the file and restart the MySQL DB engine on the target MySQL DB instance.

For example, if you're on a Linux system, you can use the following syntax:

service mysqld restart

9.    Establish a connection to the source RDS for MySQL DB instance.

For example:

mysql> change master to master_host='rds-endpoint',master_user='repl_user', master_password='password', master_log_file='mysql-bin.152707', master_log_pos= 456;

master_host: Endpoint of the source Amazon RDS for MySQL instance.
master_user: Name of the replication user (created in Step 4).
master_password: Password of the replication user.
master_log_file: The binlog file name recorded in Step 3. (In Step 3, the example output indicated "mysql-bin-changelog.152707" as the binlog file name.)
master_log_pos: The binlog position recorded in Step 3. (In Step 3, the example output indicated "456" as the binlog file position.)

10.     Log in to the target RDS for MySQL DB instance, and begin the replication with the following command:

mysql> start slave;

11.  Confirm that the replication is synchronizing between the source RDS for MySQL DB instance and target MySQL DB instance:

mysql> show slave status\G

AWS DMS

Before you set up replication using AWS Database Migration Service (AWS DMS), check the following resources:

  • To take a backup from the point-in-time restored instance, see Steps 1-5 in the Binlog replication section. It's a best practice to follow these steps as the custom start time can be at any (past) point in time within your backup retention period. After you restore the backup from the target DB instance, record the checkpoint log sequence number (LSN) that is generated during the DB recovery process. You'll need to reference the LSN to set a change data capture (CDC) start time. To obtain the checkpoint LSN, review the error log file of the restored RDS MySQL instance, immediately after the point-in-time restore operation completes. For example:
[Note] InnoDB: Log scan progressed past the checkpoint lsn 44326835524
  • To start the CDC from a custom start point, the user can run the show master status command to return a binlog file name, position, and several other values.  For more information about starting the CDC from a custom start point, see  Performing replication starting from a CDC start point.
  • To check the prerequisites for setting up a MySQL database as a source for AWS DMS replication, see Using a MySQL-compatible database as a source for AWS DMS.
  • To check the prerequisites for setting up a MySQL database as a target for AWS DMS replication, see Using a MySQL-compatible database as a target for AWS Database Migration Service.

  • Did this article help?


    Do you need billing or technical support?