In this article, I’m going to explain how to migrate your local WordPress MySQL database to Amazon Web Services (AWS) Relational Database Service (RDS). You might want to do this for the following benefits:

  1. Improved performance, as your database will be separate from the resources running on your EC2 instance.
  2. The ability to horizontally scale your website, allowing multiple EC2 instances to connect to the same database.
  3. Easier database maintenance and upgrade tasks.

Convinced? Here are the steps to accomplish this migration:

First, navigate to the AWS console and choose RDS. Create a MySQL database, filling in the form as shown below:

DB Instance Identifier: wordpress Master Username: admin Master Password: [YourChoice]

You can leave most settings at their default values, including the default VPC.

Second, edit the security group of this newly created database. Select the database, navigate to the Connectivity and Security tab, and choose the security group. In the inbound rule tab, edit the inbound rule to remove the default settings and choose the type: MySQL, Protocol: TCP, and Port range: 3306.

In the source section, you may either choose the security group associated with your WordPress EC2 instance or enter your own IP address. If you haven’t created your WordPress EC2 instance yet, you can do it now, or simply use Bitnami WordPress from the marketplace.

Third, SSH into your WordPress instance and back up your existing WordPress database with this command:

    mysqldump -u root -p [YourDatabaseName] > backup.sql

Replace [YourDatabaseName] with the name of your database, such as bitnami_wordpress. The backup.sql file should be created in your current directory. Import this file to your newly created AWS RDS instance by running the command:

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

Replace admin and [RDS_ENDPOINT] with your own values. If you encounter an error like:

    ERROR 1049 (42000): Unknown database 'wordpress'

This means that your wordpress database has not been created yet. First, connect to the database with the command:

    mysql -h [RDS_ENDPOINT] --user=admin --password=[YourPassword]

Once connected to MySQL, create a new database with the command:

    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. This file is usually located in your WordPress directory, such as /home/bitnami/apps/wordpress/htdocs if you’re using Bitnami. Update the following values:

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

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

    /** MySQL database password */
    define( 'DB_PASSWORD', '[YourPassword]' );

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

Replace the placeholders with your own values and save the file. The changes should take effect immediately.

Done. Feel free to reach out if you have any questions.