Guidelines for Migrating a Large Dataset Database from RDS to RDS with Minimal Downtime

Migrating a large dataset database with minimal downtime can be a daunting task, but with careful planning and execution, it is possible to minimize downtime and ensure a smooth transition.

This article will provide a step-by-step guide to migrating a large dataset database, based on the guidelines outlined in our document.

Getting Started

Before we dive into the steps, it is important to understand the importance of planning and executing the migration carefully. A poorly executed migration can result in data loss or extended downtime, which can have a significant impact on your business. To avoid these issues, it is essential to carefully plan the migration, test it thoroughly, and have a rollback plan in case of any issues.

The first step in migrating a large dataset database is to modify the custom DB parameter group by setting the parameter log_bin_trust_function_creators to 1. This will allow the database to trust stored functions and procedures, which is necessary for replication.

The next step is to prepare an EC2 machine with sufficient storage. This machine will be used to back up data from the source slave database. It is essential to ensure that the backup machine has enough storage capacity to hold the database backup.

Once the backup machine is ready, the binlog retention period for the source master database should be set to a sufficiently long duration. This can be done by connecting to the source master using MySQL Workbench and executing the following command:

CALL mysql.rds_set_configuration("binlog retention hours", 24);

It is important to note that this command sets the binlog retention period in hours. You should set it to a value greater than the expected duration of the migration to ensure that you have enough time to complete the migration.

The source slave database should also be readied at this point. This involves ensuring that the database is up to date and that there are no issues that could cause the migration to fail.

Before proceeding further, it is important to verify that the retention settings on both the master and slave databases are identical. The retention period should be set to the same value on both databases. This can be checked by connecting to both the source master and slave with MySQL Workbench and executing the command:

CALL mysql.rds_show_configuration;

This command will show the current configuration of the database, including the binlog retention period. Ensure that the retention period is set to the same value on both databases.

At this point, replication of data from the source master database to the source slave database should be stopped. This can be done by connecting to the source slave with MySQL Workbench and executing the command:

CALL mysql.rds_stop_replication;

Stopping replication ensures that there are no changes to the database during the migration process.

The mysql client should then be installed into the backup machine, and data should be backed up from the source slave database to the backup machine using the following command:

mysqldump -h source-database-rep.abc.ap-southeast-1.rds.amazonaws.com -u root -pPASSWORD --single-transaction --all-databases --triggers --routines --events > your-backup.sql

This command will create a backup of the entire database and store it in the file your-backup.sql. It is important to ensure that the backup file is stored in a secure location and that it is not accessible to unauthorized users.

The target master database can now be prepared, and the backup can be restored into the target master database. It is recommended to execute this using Unix screen to ensure that the process is not interrupted.

Once the backup has been restored, the name and position of the last bin log file from the source slave database should be copied. This can be done by connecting to the source slave database with MySQL Workbench and executing the command:

show slave status;

This command will show the replication status of the database, including the name and position of the last bin log file. These values should be copied and used to configure the target master database to replicate data from the source master database.

The target master database can now be set to replicate data from the source master database by connecting to the target master with MySQL Workbench and executing the following command:

CALL mysql.rds_set_external_master (
  source-database-rep.abc.ap-southeast-1.rds.amazonaws.com,
	3306,
  source-database-root,
  source-database-password,
  Relay_Master_Log_File value,
  Exec_Master_Log_Pos value,
  0
);
CALL mysql.rds_start_replication;

This command will configure the target master database to replicate data from the source master database. It is important to wait until the target master database catches up with the source master database before proceeding. This can be checked by connecting to the target master with MySQL Workbench and executing the command:

show slave status;

This command will show the replication status of the target master database. Wait until the replication status shows that the target master database has caught up with the source master database.

Once the target master database is caught up with the source master database, several tables should be compared between the source and target master databases to ensure that the data has been migrated correctly. It is important to verify that the data has been migrated correctly before proceeding.

At this point, your services should be paused, and the instances in the source cluster should be stopped. The replication connection between the target master database and the source master database should then be cut off by connecting to the target master with MySQL Workbench and executing the command:

CALL mysql.rds_stop_replication;
CALL mysql.rds_reset_external_master;

This command will stop the replication connection and reset the external master configuration. The target slave database should now be prepared if necessary, and the target cluster can be promoted as the new primary. The source cluster can then be deleted.

Finally, the custom DB parameter group should be modified by setting the log_bin_trust_function_creators parameter to default.

By following these steps, it is possible to migrate a large dataset database with minimal downtime and ensure a smooth transition. It is important to plan the migration carefully, test it thoroughly, and have a rollback plan in case of any issues.

References:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html