Amazon Relation Database Service(RDS) is a managed database service in Cloud provided by AWS. If you are tired of managing database server of your website/webapp, RDS is your best choice. RDS comes with monitoring, 99.99% uptime and Backup service. AWS even provides RDS Free tier service for new customer so that they can test the reliability of the service.
In this article, let me get you through how you can create MySQL Database in RDS and migrate existing MySQL database to RDS. I have added video demonstration and relevant screenshots for each step in the database migration.
This is a video demonstration of this article. Please Subscribe , like and comment 🙂
Step1: Create dump of the existing MySQL Database:
Log in to the box where MySQL server is running and take dump of the MySQL database with mysqldump command.
mysqldump -u database_user -pdatabase_password database_name > dump_name
mysqldump -u mydbuser -p123456 easyaslinuxdemo > easyaslinuxdemo.sql
Here mydbuser ,123456 , easyaslinuxdemo and easyaslinuxdemo.sql are database user, database user’s password, database name and dump name respectively.
Now you have the database dump ready. Let’s create RDS instance.
Step2: Create a MySQL database in Amazon RDS:
Login to AWS RDS dashboard at https://console.aws.amazon.com/rds/ and Click on Create database .
On the next page, you have an option select the Database server you want to setup. Select MySQL and Click Next ..
On the next page, you are asked to select the usecase. Usecase is nothing but the performance of the database that AWS would provide. If your database receives high traffic, I would suggest you to select Production . In this article, we would select Free tier option Dev/Test – MySQL and click Next .
On the next page, You will be asked to select following important parameters for your database.
DB Engine version – Here you select the MySQL version for new RDS database server. Selected MySQL 5.6.40.
DB instance class – Here you select vCPU core and Memory for new RDS database server.(If you would like to go with Free tier service, select db.t2.micro). Selected db.t2.micro.
Multi-AZ deployment – You can select Yes here if you need replica of the RDS database created in another availability zone. This ensure High Availability for your database server.
Storage type – You can go with General purpose SSD.
Allocated storage – Default storage is 20GB. If you think your database would grow over 20GB, you can give a desired storage value.
DB instance identifier – Here give a unique name for your DB. Given testdb .
Master username – Here give a username for your database. Given testuser . Note down the database username.
Master password – Choose a password for your database user and note it down. Then Click Next .
On the next page, You need to select following parameters.
VPC – VPC is nothing but a virtual network. If the webapp/website which access your database server is in AWS itself, you may select the same VPC where webapp/website is hosted, Otherwise keep the default one.
Public accessibility – Select Yes, if the webapp/website which would access your database is hosted outside of AWS.
Database name – Give your database name. You may give the same name as DB instance identifier. Note down the database name.
Port – Keep the default port number 3306 and Click Create Database .
The DB status initially will be creating . Wait until it become Available .
Step3: Restore the MySQL dump to Amazon RDS:
Hope you have Database name, Master username, Master password, Port noted down. Now you need an endpoint to connect to the database. You can find it under connectivity tab.
Let’s do the migration. You should move the database dump to a location from where you can connect to the new RDS database. Make sure you can connect to your database server by running below command.
telnet db_endpoint port
In our case, it is.
telnet testdb.ciqwid.us-east-1.rds.amazonaws.com 3306
If it connects successfully, you can proceed with database restore. Please run below command.
mysql -u database_user -pdatabase_password -hdatabase_endpoint database_name < dump_name
In our case, it is.
mysql -utestuser -p1234567890 -htestdb.ciqnwiq59cbp.us-east-1.rds.amazonaws.com testdb < easyaslinuxdemo.sql
Hurray, You have completed the course. Now forget the pain for managing database by yourself, let AWS do this for you! Also don’t forget to update the database details in your webapp/website.
Let me come back with other useful articles soon.Subscribe to this blog so that you don’t miss out anything useful (Checkout Right Sidebar for the Subscription Form) . Please also put your thought on this article as comments .