In order to improve efficiency and elasticity of EC2 instance, we need to use a database that is installed on a separate machine. A good solution is using AWS RDS. Before we create a database RDS instance, we need to make a dump of the existing database on our WordPress server.
In the following, I will list these instructions.
1. To create a sql dump of the existing database we need to login on the server via SSH (Or you can remote access your MySQL from your local machine)
$ ssh -i YOUR_KEY_PAIR.pem ubuntu@EC2_IP_ADDRESS
2. Dump data from MySQL
$ mysqldump -uwordpressuser -pYOUR_PASSWORD wordpress > backup_wordpress.sql
3.1 After making a dump, we need to create a RDS instance. Open your AWS management console and RDS tab, and click “Launch a DB Instance”.
3.2 Step 1: Select “MySQL” to be Database engine.
3.3 Step 2: Choose “No”, or your bill will have extra charge.
3.4 Step 3: Choose “db.t2.micro” for Database Instance Class, or you can choose the higher one. Remember choose “No” for Multi-AZ.
DB Instance Identifier is as same as your wordpress database name on EC2. Master Username and Master Password must be equal to wordpress database on EC2. When you finish, click “Next Step”.
3.5 Step 4: Check your setting for RDS and launch instance. Remember select wordpress security group which you’ve created before for VPC Security Groups. And make sure you’ve already added port 3306 in this security group.
3.6 After launching an instance, you get an Endpoint. You need to use this Endpoint to connect with WordPress server.
4.1 Back to EC2 instance, and connect to RDS.
$ mysql -uroot -pYOUR_PASSWORD --host=wordpress.XXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com
4.2 Create “wordpressuser” on RDS just like you create wordpressuser on EC2 wordpress server.
$ mysql -u root -p mysql> CREATE DATABASE wordpress; mysql> CREATE USER wordpressuser@'%'; mysql> SET PASSWORD FOR wordpressuser@'%'= PASSWORD("YOUR_PASSWORD"); mysql> GRANT ALL PRIVILEGES ON wordpress.* TO wordpressuser@'%' IDENTIFIED BY 'YOUR_PASSWORD'; mysql> FLUSH PRIVILEGES; mysql> exit
5. Modify the “wp-config.php” so the host matches the remote host.
$ vim /var/www/html/wp-config.php // ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define('DB_NAME', 'wordpress'); /** MySQL database username */ define('DB_USER', 'wordpressuser'); /** MySQL database password */ define('DB_PASSWORD', 'YOUR_PASSWORD'); /** MySQL hostname */ define('DB_HOST', 'wordpress.XXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com:3306');
6. Transfer data from EC2 to RDS and enjoy it!
$ mysql -uroot -pYOUR_PASSWORD --database=wordpress --host=wordpress.XXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com < backup_wordpress.sql