We are going to see how we can migrate an existing database on MySQL / MariaDB to an already running RDS Instance on the AWS.
- Take the MySQLdump of the needed database.
mysqldump -u username -p databasename --single-transaction --quick --lock-tables=false >databasename-backup-$(date +%F).sql
- ( Dont forget to replace the username as root – most of the times, and databasename -> Db name of database which you are going to migrate to RDS )
- Once prompted, enter your password.
- Once done, login to the RDS Instance from your MySQL server ( Make sure the security groups are configured to allow the connection from Ec2 to RDS )
mysql -h hostaddress -P 3306 -u rdsusername -p
- ( Dont forget to replace hostaddress with the address of your RDS Instance and rdsusernmae with username for your RDS Instance, when prompted give the password too )
- You find that hostaddress under – Connectivity & security -> Endpoint & port under RDS Database From AWS Console.
Once logged in, create the database using MySQL commands :
-
create database databasename;
\q
-
- Once Database is created in RDS, Import the SQL file created in Step 1 :
-
mysql -h hostaddress -u rdsusername -p databasename < backupfile.sql
-
- This should import the SQL file to RDS and restore the contents into the new database.
Do you feel you need a different master user for this database just created ? Comment your thoughts.
In case you find any difficulty in doing this, feel free to avail our K9 response on-demand assistance