Migrating existing MySQL database into Amazon RDS

Amazon RDS  is a completely managed service by Amazon. It makes database maintenance very easy due to the many possibilities it offers, including automatic backups, point-in-time recovery, snapshots, scalability and replication.

It currently supports MySQL as well as other databases, such as MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, and the MySQL-compatible Amazon Aurora DB engine.

MySQL major versions 5.5, 5.6, and 5.7

• Supports major Mysql versions 5.5, 5.6, and 5.7
• Supports database size up to 6 TB.
• Instances offer up to 32 vCPUs and 244 GiB Memory.
• Supports automated backup and point-in-time recovery.
• Supports cross-region read replicas.

RDS Limitations

As Amazon takes care of infrastructure, backups and updates on the Amazon RDS DB instance, the shell access to the instance is disabled.  We can access the instance through Mysql console from other machines.

Steps to Migrate existing Mysql database to Amazon RDS

Note: This tutorial assumes that you have already setup new RDS instance and enabled connection between this instance and connecting machine where database is currently hosted, via security groups.

Execute below command:

mysqldump <db_name> | mysql --host=<hostname> --user=<db_user> -p<password> db_name

Example:

mysqldump az_demo | mysql --host=azdemo.cwddfrlq2.us-east-1.rds.amazonaws.com --user=az_demo -MySecretPass!! az_demo

[Note: Above values are not real,  focus on syntax]

You may need to supply DB credentials in your existing system for Mysql dump.  Command will look as below

mysqldump --user=demo -pDeMoPass az_demo | mysql --host=azdemo.cwddfrlq2.us-east-1.rds.amazonaws.com --user=az_demo -pMySecretPass!! az_demo 

Replace username, password and DB connections as per your setup.

If you want more consistent Database migration, execute below command as recommended by Amazon

mysqldump -u <local_user> \
--databases <database_name> \
--single-transaction \
--compress \
--order-by-primary \
-p<local_password> | mysql -u <RDS_user> \
--port=<port_number> \
--host=<host_name> \
-p<RDS_password>

• • --single-transaction – Use to ensure that all of the data loaded from the local database is consistent with a single point in time. If there are other processes changing the data while mysqldump is reading it, using this option helps maintain data integrity.

• • --compress – Use to reduce network bandwidth consumption by compressing the data from the local database before sending it to Amazon RDS.

• --order-by-primary – Use to reduce load time by sorting each table’s data by its primary key.