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.