Tutorial: Moving to RDS will make you love the cloud

Amazon’s RDS handles the pain of database management for dollars a month, leaving you with more free time and so much win.

Here’s a quick tutorial on how to migrate your local MySQL database to RDS. You will need some experience with the Linux command line and some basic database knowledge. In this example, I’ll migrate a WordPress database since it’s a really common use-case (WordPress sites are 60%+ of sites with CMS systems and it runs on MySQL).

Watch the walk-through on YouTube at https://www.youtube.com/watch?v=gwO76ar56Kg.

What is it?

Everyone has a SQL database lurking somewhere. They are the memory of the Internet, keeping alive everything from ecommerce stores to games to CMS systems. But they can be burdensome beasts especially as they get loaded with data.

Amazon RDS is a managed database service which provides instances of all the major popular free and paid database types, together with their own special one called Aurora. If you’re starting out Blue Sky and have the budget, go with Aurora (test after test shows its staggering performance and low cost), otherwise just use your favorite RDMS on Amazon.

What do it do?

RDS is a managed database service. This means:

• AWS manages the servers the database lives on — the underlying operating systems, networks, patching, and database versions. You have no access to any of this, nor do you want it.
• AWS manages the backups, and you can specify some granularity in terms of frequency and behavior. This can be a once-a-day backup held for a defined number of days, or a point-in-time backup that let’s you go back to this morning at 11:34:44am.
• AWS provides brainlessly simple ways to add multi-AZ replication, read replicas and scaling which would normally need a DBA.
• You don’t have root access to the machines and there are restrictions on what you can do. This is less likely to bother developers and more like to aggravate people used to fine-tuning databases.

Ultimately, RDS is a your virtual DBA that gives you a wicked fast and managed infrastructure — if you can live with that, then let’s roll.

Tutorial: Migrating a MySQL database to RDS.

For my example, I’m using a MySQL database from a WordPress installation. There are a number of good reasons to use this as an example:

• By default, many WordPress installers plop the database on the same machine as the webserver itself. This is not a good production strategy — it makes it hard to scale and a low-powered server is less performant as it struggles to do everything, especially under any load.
• Oftentimes, people then forget to backup and patch the database since it’s hidden in the stack.
• Separating the two is really the first step in scaling out a webserver configuration. With it separated, you can then easily create an autoscaling group of webservers that are identical and all connect to the same database.

Incidentally, WordPress has a horrific schema much more suited to a NoSQL database. I can only hope that one day it moves to DynamoDB…

Anyway, this demo will achieve the following:

Create the RDS database

1. Go to the AWS Console → RDS and click Launch DB Instance. Select MySQL Dev/Test and complete the next form. Take specific note of the username and password (and please select something secure):

2. Once this is revved up and ready, take a note of the Endpoint on the main RDS dashboard (it looks like mysqldb.XXXXXXXXX.region.rds.amazonaws.com:3306).

3. Go to Security Groups and select the Inbound tab for the chosen security group. Add your webserver IP address in CIDR format for type “MySQL/Aurora” (e.g. 12.12.12.12/32).

There are better ways of doing this but for this tutorial, this allows the webserver to reach the database (which is not open to the Internet generally).

Backup and migrate the existing database

4. SSH into your WordPress server and find the wp-config.php file, copying down the database connection settings of the existing MySQL server.

5. Make a backup of the existing database by entering the following command:

mysqldump -u username -p -h hostname database_name > backup.sql

It will prompt you to enter the password, creating a new file in the directory called backup.sql.

6. Now, remember the RDS endpoint and username and password from first step? Enter the following command, replacing the parameters shown:

mysql -u RDS_username -p -h RDS_endpoint

You are now connected to your RDS MySQL database (if you receive access denied messages, you need to check your Security Group settings from the first section). Enter:

create database wp_db_name;

Then exit from the MySQL terminal (type exit).

7. Back on the command prompt, enter:

mysql -u RDS_username -p -h RDS_endpoint wp_db_name < backup.sql

This imports the backup file into the RDS MySQL instance.

Update your wp-config database settings

7. For the sake of not screwing things up beyond repair, back up the wp-config.php file from earlier (cp wp-config.php wp-config-original.php).

8. Edit the wp-config.php file (using vi, nano, or any other text editor). Don’t forget you’ll need sudo to do this usually. Change the definitions for DB_USER, DB_PASSWORD and DB_HOST to match the RDS settings and save the file.

Congratulations, your webserver is now using RDS.

In a nutshell, you are migrating the database from local to RDS and changing the connection string used by the application — this is the same process for many applications, not just WordPress. It’s surprisingly simple once you’ve done it a few times.

Is that it?

Not quite. There are some additional things to consider in production which I didn’t cover for the sake of simplicity:

• In the migration phase, make sure you put the WordPress site into maintenance mode to avoid users adding data while you move it across. The move itself will only take a couple of minutes for a typical WordPress site.
• Stop the MySQL service on the webserver box once you’ve finished— there’s no need to have this running.
• For the sake of being a pro, please test your site and its plugins to make sure everything work before wiping the old database. Running a migration on a test machine first is a good idea if you’ve never done this before.
• If you have a site with serious traffic demands, definitely look at using a Multi-AZ Aurora implementation instead. The performance gains are well worth the extra spend.