Migration of Database in EC2 Instance to RDS Database
first ssh into your ec2 instance like shown below:-
Then get into root and get inside mysql by your user name and password
sudo su
mysql -u gaurav -p
SHOW DATABASES;
USE gauravdb;
SHOW TABLES;
You can see the table above inside my ec2 mysql database,which i need to migrate to RDS
Now the sql is in mysql in ec2 now,we need to migrate it to RDS,first we need to create database like shown below.I have configure RDS below to make it low cost you can configure according to your requirements.
Keep note of password and user we need it later on;
Select security group which has port 3306 open,
Keep note of database name we need it later on;
After all configuration Create database
You can view your credential details for last time so keep the record in proper place
If you click on view credential details you can see your username and password
Now,Migration of Database in EC2 Instance to RDS Database
first,we dump gauravdb data in gk.sql,you can give any name to dump but it should end with .sql like shown below;
mysqldump -u root -p gauravdb > gk.sql
Then provide your RDS endpoint which you get after creating RDS database and RDS username,RDS dabase name and name of sql where you dump the ec2 database data then provide your rds password,
mysql -h <replace-rds-end-point-here> -P 3306 -u rdsuser -p rdsdb < ec2db.sql
mysql -h gaurav.cl64zok7w5jg.us-east-1.rds.amazonaws.com -P 3306 -u gaurav -p rdsdb < gk.sql
Then again provide rds endpoint and username and password of RDS database like shown below;
mysql -h <replace-rds-end-point-here> -P 3306 -u rdsuser -p
mysql -h gaurav.cl64zok7w5jg.us-east-1.rds.amazonaws.com -P 3306 -u gaurav -p
Then provide your RDS database name after USE like shown below;
USE rdsdb;
Then write SHOW TABLES to view the data inside of RDS database like shown below;
SHOW TABLES;
you can see above in the table data from ec2 mysql database has sucessfully migrated to RDS mysql database;
you can see below in RDS database connection has been establish between ec2 and RDS
Documentation by:Gaurav khatiwada
2023 AUGUST 27