May 7, 2019
Cloud Migration: How to Go from an On-premises Database to Amazon Relational Database Service (RDS) with No Downtime
When it comes to database migration to the cloud, clients/companies want minimal downtime and want the migration to happen during off-time. This requires lots of planning and migration strategies. It requires analyzing the off-peak time of database usage and various architectural aspects. In this blog, I will try to explain how to migrate data from an on-premises database to Amazon's Relational Database Service (RDS) with no downtime.
Let us try to answer two basic questions before learning how to migrate an on-premises database to RDS.
Why do we need to migrate?
Scale — Vertically or Horizontally, Simplicity, Automatic Management, Robust MySQL Support, Minimal Downtime, Automated Backup, and Security are the main reasons to migrate the on-premises database to RDS.
How do you Migrate?
In order to migrate data from an existing MySQL instance running on-premises or on in a data center, one has to configure it as a replication source. The On-Premises database remains in sync with the RDS. Once all testing is done, one can decommission or remove the old or On-Premises database.
Although RDS is quite robust and automatic, still it has some pros and cons:
- Unlike Physical / data-center machine one can scale up CPU, storage space and other stuff by using Amazon RDS.
- It is very easy to create replication servers (read replicas) using RDS.
- One can create a secondary instance for failover in the case primary instance fails and this will be a synchronous instance.
- It is very easy to do Backups and software version patching, as most of the things are automated with RDS.
- One cannot have instance access or shell access of the database instance but we can only access the RDS service.
- We don’t have full root / Superuser privilege on the RDS databases.
prod-servername-oltp01 is a master on-premises database and prod-servername-oltp02(on-premises) is its replication slave. It’s a good practice to have a replication slave. If you don’t have one, you have to create it so that you can take the dump from the slave itself.
Take dump from the replication slave prod-servername-oltp02 using the following command. It's better to use --master-data and --single-transaction options because that provides a convenient way to make an online backup suitable for use prior to point-in-time recovery and give the bin-log position for creating a replication slave.
Creating a RDS database:
Create a DB identifier, user and password.
Select the RDS version and Instance type.
Select the VPC, Subnet and Security group
Set backup policy:
Set-up log policies and maintenance window:
Changes in the RDS parameter group:
For AWS RDS instances, you manage your database engine configuration through the parameters in a DB parameter group.
When a database instance is created a default DB parameter group is created. To create a parameter group, one has to click on the RDS instance and then select "Modify." In the Modify screen, you can create a different parameter group.
The following parameter needs to be changed for RDS migration and creating a replication slave.
Taking dump from the source/master database:
One can consider splitting the mysqldump into two phases: –no-data so you dump schema only, and then –data-only so you get the data. This way one can tackle errors in schema and data differently. Below is the command to dump the schema and data together.
mysqldump -u$USER -p$PASS --databases <database-name> --routines --triggers --single-transaction --master-data=2 | gzip > $BACKUP_FILE 2>> $BACKUP_LOG
Here the variables $USER $PASS, $BACKUP_FILE and $BACKUP_LOG are used for mysql username/password, location, and name for backup dump and log file respectively.
Issues with definers:
RDS is having a problem with Triggers and Views thus cannot be applied using the syntax which includes SQL DEFINER entries. The load will fail if the definer user is not changed to the current operating user. Linux sed can be used to replace the local definer users with the current operating user.
sed -i -e 's/`root`@`localhost`/`root`@`%`/g' -e 's/`abc`@`localhost`/`root`@`%`/g' db.sql
Copying / restoring the database to destination RDS instance:
scp ./db.sql 10.90.102.51:/backup/p-vkukreti
nohup mysql -u root -h prod-aws-server.cj3vbvmmfkzq.us-west-2.rds.amazonaws.com -p < db.sql >
RDS support for SUPER user:
Amazon RDS does not support the SUPER privilege for regular users. The following privileges are the ones given to root user.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION;
Login into RDS server:
Once data is migrated to RDS you can log in as follows:
[p-vkukreti@ba-us1-jump02 ~]$ mysql -u root -h prod-aws-server.cj3vbvmmfkzq.us-west-2.rds.amazonaws.com -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+4 rows in set (0.03 sec)
Making RDS writer replication slave of On-Premises Master:
CALL mysql.rds_stop_replication;CALL mysql.rds_reset_external_master;
call mysql.rds_set_external_master('10.201.205.11', 3306, 'aws_slave', ‘<password>’, 'binlog.014241', 173996496, 0);
root:(none)> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Skip Replication error in RDS:
One cannot skip a particular error number, so you must be careful while performing a dump and restore, otherwise replication slave will break. One can use Amazon RDS function of mysql.rds_skip_repl_error to skip error counter. This Stored Procedure takes no argument and skips one event at a time.
By migrating our on-premises database to RDS, we can take advantage of a world-class database running on an Enterprise-class cloud platform. You can get the best performance and automated management and the flexibility to use all the features of the database. Now as we have replicated the on-premises, infrastructure to RDS and set up M/M replication, we are ready for a graceful switchover without downtime.