In this post I will show you how to migrate a MySQL database to an RDS instance. I’ll be migrating a database that is running on a single EC2 instance. This instance is also running WordPress as part of a single server LEMP stack. This process will work for any application currently using MySQL, whether it be on-premise, on an EC2 instance, or elsewhere.
Create Your RDS Instance
AWS has made it incredibly quick and easy to get up and running in RDS. In the console, when you go to create your RDS instance there is an Easy create option. This will create the instance in the default VPC and assign it the default security group. This is fine for our demonstration, but if you want to configure these options yourself then don’t choose the Easy create option.
I’ve selected the Free tier option for the instance size. There are also options for Dev/Test and Production that have larger instance sizes and storage configured. I’ve also given the instance an identifier, master username and ticked the option for an auto generated password.
While the RDS instance is being created I’ll create a new security group for it to allow only traffic from my WebSG security group. This security group is assigned to my current EC2 instance running the full LEMP stack.
Backup existing current MySQL Database
Now I need to create a backup of my current database which I can migrate to the newly built RDS instance.
To do this I need to SSH to the EC2 instance where the database is currently running. To create a backup I need to run the mysqldump command with the appropriate details, as shown below:
mysqldump -u USERNAME -p DATABASE > awsprobackup.sql
USERNAME must be a user that has appropriate permissions in MySQL to create this backup and DATABASE is the name of the database you wish to migrate.
Prepare the RDS Instance
Once the backup has been created I need to connect to the RDS instance and create a new empty database. I’ll connect to the RDS instance using the following command (ensuring the RDS endpoint is correct):
mysql -h ENDPOINTNAME.xx-xxx-x.rds.amazonaws.com -u USERNAME -p
USERNAME this time will be the Master username specified when first creating the RDS instance (which I set as admin).
Now I need to run the following command to create a new database:
mysql> CREATE DATABASE newdatabase;
I also need to create a new user and give it the correct privileges for this database. To do this I run the following commands:
mysql> CREATE USER 'newuser'@'%' IDENTIFIED BY 'password' mysql> GRANT ALL PRIVILEGES ON newdatabase.* TO 'newuser'@%' mysql> FLUSH PRIVILEGES;
Once done I can exit from the MySQL command line.
Migrate the Database
With MySQL now configured on the RDS instance, I can restore the backup to it. To do so, I need to fun the following command on the EC2 instance from the directory where I created the backup earlier on:
mysql -h ENDPOINTNAME.xx-xx-x.rds.amazonaws.com -u USERNAME -p -D newdatabase < awsprobackup.sql
In usual Linux fashion, no news is good news, so unless an error appears the backup should be restore successfully. I can check by logging into MySQL on the RDS instance and viewing the tables in the database.
Once happy that everything is restored I need to change the configuration in WordPress to point to the new database with the new credentials. I won’t cover how to do that specifically for WordPress but the process for any application will essentially be the same.
The final step is to restart the web server (in my case that is Nginx) for the configuration file changes to take effect. Once that is done everything should be up and running using the new RDS instance.