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.
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
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> \
-p<local_password> | mysql -u <RDS_user> \
--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
mysqldumpis 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.