MySQL database migration from On-Promise to AWS RDS.
Before the Cloud computing and SaaS era, all the database are hosted in the instance itself. This cause slowness and outage of the database. If the database service went down it directly impact the business. In this tutorial, we will learn how to migrate database from on-promise i.e. instance to AWS RDS for both fault-tolerant and high availability. We will go through step by step detail manner to get a clear understanding.
- SSH into the server and connect into MySQL with root user.
2. List out all the databases.
3. Take the dump of all the databases. the (information_schema, MySQL, performance_schema, sys) are the MySQL own databases and not required to take dumps of these. It’s advisable to create separate dump for the separate database if the database is big in sizde.
$ mysqldump -uroot -p --databases classicmodels world > sql_dump.sql
4. Now, It’s time to create an RDS in AWS. From AWS console navigate to RDS which located under Databasesection.
In the RDS, click on Create Database.
Choose MySQL as the database engine and Standard createas the Database creation method.
Select the Templates as per requirement. In this demo,l I am going to use the Free Tier.
After choosing the RDS Template we need to update the settings for the Database like DataBase Identifier , Master username and Master Passwordetc. . In this example I am going to use auto generated password but one can use his own custom password.
Then comes to DB Instance size. For production it’s recomamned to use larger instance typebut for our demo I am going to use t2.micro which falls under free tier.
Then comes the storage part. I am going to use 20 GB SSDas I am using free tier but in production environment it’s recomanded to use at least 100 GB SSD. The reason to use SSD is to get higher iops. Also, we can choose Auto Scalling in case the storage got full it will increase the storage.
In the Availability and Durability section we will choose Multi-AZ deployment standby instance in each Availability Zone.
in the Connectivity section we will choose the VPC. It’s recomanded not to use default VPC for production use case. Always use the same vpc as of The frontend EC2 for seamless connectivity. In this demo I am going to use the default VPC for making things simple.
If you want to access the RDS from your own laptop or PC using MySQL Workbench then choose Public access if you’re using for production and concern about the security the choose No as Public access.
Next comes the Security Group, choose this to allow the inbound traffic rules i.e. from which sources of IP the database is accessible. In this demo I am going to use All traffic and from Any Where option but in real environment we will allow this only from the frontend application IP range only.
In my default security group I have added a rule which allows all traffic from any where i.e. 0.0.0.0/0.
In the Database authentication we will choose Password Based authentication. The other option available is IAM Users and Roles based authentication.
After everything setup correctly click on Create Database to spin the RDS.
It will take sometime for the RDS to be up and running. We have to wait till it become functional :-p .
After the Database up and running we will see the green color at top.
There is an option to get the credentials which will be available to view only one time. We need to note that down in a secret place for our further referance.
Once we store the credentials in a safe pace we will again go back to our Instance terminal. and connect the RDS with mysql command line with the RDS endpoint as host.
Once we connected successfully we need to source the SQL dump file which we created earlier using mysqldump command.
After the sourceing done(which will take sometime as per the db size) we will check all the database for confirmation.
Then go to each individual database and check all the tables are available or not.
Congratulations!!!
You have successfully migrated the Database from on-promise to RDS for high availability and fault tolorance.
