Migrate WordPress MySQL to AWS RDS

In this article, I am going to explain how to migrate your WordPress local MySQL database to Amazon Web Service (AWS) Relational Database Service (RDS). Why would you want to do this? Because of the following benefits:

  1. It increases the performance since your database is separate from the resources running in your EC2 instance.
  2. It allows you to horizontally scale up your website, with multiple EC2 instances connecting to the same database.
  3. It helps you to take care of your database maintenance and upgrade tasks.

Convinced? Now here are the steps to enable this:

Firstly, navigate to the AWS console and choose RDS. Then create a MySQL database. Fill in the form as shown below:

DB instance identifier: wordpress

Master username: admin

Master password: rememberYourChoice

You may leave everything as default, such as default VPC.

Secondly, edit the security group of this newly created database. Select the database, on the Connectivity and Security tab, select the security group. Then at the inbound rule tab, choose edit inbound rule. Remove the default, and choose type: MySQL, Protocol: TCP and Port range: 3306.

In the source section, you may choose custom and select the security of your WordPress EC2 instance, or your IP address of your WordPress. If you haven’t created your WordPress EC2 instance, you may create it now, or simply using the Bitnami WordPress from the marketplace.

Thirdly, ssh into your WordPress instance, and backup your existing WordPress database with this command:

mysqldump -u root -p my_wordpress_db > backup.sql

^ Replace the placeholder my_wordpress_db with your database name, such as bitnami_wordpress. Then the backup.sql file should be created in your current directory. We can import this file to the newly created AWS RDS instance by running the command:

mysql -u admin -p -h RDS_ENDPOINT -D wordpress < backup.sql

Replace the admin placeholder and replace RDS_ENDPOINT with your RDS endpoint URL, which can be found in the Connectivity & security tab on your RDS instance. If you hit an error:

ERROR 1049 (42000): Unknown database 'wordpress'

Then it means your wordpress database is not yet created. You may first connect the database with the command:

mysql -h RDS_ENDPOINT --user=USERNAME --password=rememberYourChoice

Replace the RDS_ENDPOINT, admin and rememberYourChoice with your values. Once you connect to the MySQL, run the command to create a database:

mysql> CREATE DATABASE wordpress;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye

Finally, edit the wp-config.php file in your WordPress EC2 instance. It would be at your WordPress directory, such as /home/bitnami/apps/wordpress/htdocs if you are using Bitnami. Change the values in the file:

/** The name of the database for WordPress */
define( 'DB_NAME', 'wordpress' );

/** MySQL database username */
define( 'DB_USER', 'admin' );

/** MySQL database password */
define( 'DB_PASSWORD', 'rememberYourChoice' );

/** MySQL hostname */
define( 'DB_HOST', 'RDS_ENDPOINT' );

Replace the database name, admin, PASSWORD_THAT_YOU_DEFINE and RDS_ENDPOINT with your values. Once you save the file, the change should be effective right away.

Done. Let me know if you have any questions.

By Victor Leung

Experience in software development, consulting services and technical product management. Understanding of business and technology with an MBA in Finance and a Master degree in Computer Science. AWS Certified Solution Architect with experience in building products from scratch and serving as a charismatic leader.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.