We'll be migrating a locally hosted MySQL database to Amazon RDS. Effectively upgrading the monolithic architecture to a multi-tiered architecture.
- Scenario: Evolve the architecture of a Ghost or WordPress blog with a local MySQL database running on a single EC2 instance by utilizing Amazon RDS: MySQL.
Why use AWS RDS?
Amazon Relational Database Service is a collection of managed services that makes it easy to set up, operate, and scale databases in the cloud. Utilizing RDS will allow us to remove the admin tasks of managing our database.
Choose from popular engines such as:
MySQL
MariaDB
PostgreSQL
Oracle
SQL Server
Aurora: MySQL / PostgreSQL
Prerequisites
Creating the RDS DB
- In your AWS Account Navigate to RDS dashboard and click Create Database:
Select Standard Create
Select MySQL
- We could utilize Multi-AZ, and provisioned IOPS; however, we will be running this database under the free tier option (no bells and whistles):
- Keep note of your RDS Username / Password as we will utilize those credentials in the upcoming steps.
- Keep all defaults: The instance class should be listed as db.t3.micro
- Select General Purpose SSD - I've allocated the minimum amount for both storage and max storage threshold.
- Ensure to select Connect to an EC2 compute resource and select the EC2 instance hosting your blog.
- Everything looks good! We wont need enhanced monitoring - Lets go ahead and create the database.
- After database creation, be sure to gather the RDS Endpoint (you can find the RDS Endpoint by clicking on the RDS database you've just created)
Your endpoint should look similar to this:
- SSH into your EC2 instance (I'll be using EC2 Instance Connect) and run the following commands
(You can connect to your instance via the EC2 Dashboard):
MySQL Example Commands
The following is an example of MySQL commands we'll be using as well as showing what I'll be migrating.
mysql
show databases;
- The name of the database i'll be migrating is "theawsdev_com"
use YOURDATABASENAME;
show tables;
exit
End of Example
Backup Local MySQL Database
We will perform the database backup via the mysqldump command:
mysqldump -u USERNAME -p DATABASENAME > database_backup.sql
You won't have a confirmation that the backup succeeded, but running the list command will show that we have a new file called database_backup.sql.
ls -la
- Connect to the RDS Database instance
mysql -h RDSDATABASENAME -P 3306 -u RDSUSERNAME -p
show databases;
- Create an empty database for our import
create database DATABASENAME;
quit;
- Now lets import our backup file to our empty database:
mysql -h RDSENDPOINT -u RDSUSERNAME -p DATABASENAME < database_backup.sql
You may have a delay depending on the size of the backup and there will be no confirmation as to the success of the import.
Let's connect back to the RDS database and check if our import was successful:
mysql -h RDSDATABASENAME -P 3306 -u RDSUSERNAME -p
- Check database tables once more:
show databases;
use DATABASENAME;
show tables;
I show 61 rows in our new database which coincides with 61 rows in our previous database example - SUCCESS! We have migrated our local data to RDS.
Now all we will need to do is update our blog config files to point to our RDS DB.
Update Configuration to Point to RDS DB
- If you are running WordPress I advise reviewing this documentation to update your configuration file:
Deploy WordPress with Amazon RDS
Since I am running Ghost, the configuration file is found at /var/www/SITENAME
cd /var/www/SITENAME
nano config.production.json
- The configuration file database section will need to be updated with these new values:
"database": {
"client": "mysql",
"connection": {
"host": "RDS_ENDPOINT",
"port": 3306,
"user": "RDS_database_username",
"password": "RDS_database_password",
"database": "RDS_database_name",
"ssl": "Amazon RDS"
}
}
- After updating this section in our configuration file we can do the following commands to save and exit Nano.
CTRL + X
Y
Enter
We have now completed the migration of our local DB to RDS! Our blog is now utilizing AWS Managed Relational Database Service.
Thank you for joining me on this adventure - until next time!
