Migrate Local MySQL to RDS

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

EC2 Instance Running Ghost or WordPress blog with local MySQL DB
RDS Instance

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.

Keep default options:

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:

      # Change DATABASENAME to your database - I will be naming the dump file database_backup.sql

      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

# Change RDSDATABASENAME / RDSUSERNAME with the appropriate RDS credentials, you will be prompted for a password

mysql -h RDSDATABASENAME -P 3306 -u RDSUSERNAME -p

show databases;

Create an empty database for our import

#change DATABASENAME to a specified name

create database DATABASENAME;

quit;

Created db theawsdev2_com

Now lets import our backup file to our empty database:

#Change RDSENDPOINT / RDSUSERNAME / DATABASENAME to the appropriate values, you will be prompted for a password.

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:

#Change RDSDATABASENAME / RDSUSERNAME with the appropriate RDS credentials, you will be prompted for a password

mysql -h RDSDATABASENAME -P 3306 -u RDSUSERNAME -p

Check database tables once more:

show databases;

#I will be selecting the newly created "theawsdev2_com"

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!