How to use Amazon RDS with AMIMOTO AMI WordPress

What is Amazon RDS

Amazon Relational Database Service is a web service that makes it easy to set up, operate, and scale a relational database in the cloud.
It provides cost-efficient and resizable capacity while managing time-consuming database administration tasks by automatically patching the database software and backing up your database, storing the backups for a user-defined retention period and enabling point-in-time recovery.

You can choose between MySQL, MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, and the MySQL-compatible Amazon Aurora DB engine.

Thanks to an option called a Multi-AZ deployment you can run your DB instance in several Availability Zones. When you select this option, Amazon automatically provisions and maintains a synchronous standby replica of your DB instance in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to the standby replica to provide data redundancy, failover support, eliminate I/O freezes, and minimize latency spikes during system backups.

Advantages

• Scale your CPU, IOPS, and storage space
• Snapshot-based on demand
and automatic backups
• Multi Availability Zones deployment
• Secondary synchronous instance in
case the primary fails

Disadvantages

• No shell access to the DB instance
• Restricted user privileges
• Disabled replication in MySQL
• Weekly 4-hour maintenance window
• RDS Instance cannot be stopped

Creating a MySQL server in Amazon RDS

For more information, refer to the following URL:
Creating a MySQL DB Instance and Connecting to a Database on a MySQL DB Instance

Here, we will describe the important items to input in every step.

Step: Specify DB Details

Multi-AZ Deployment

If you want to create a DB Instance in a single availability zone, select [No].

In [Yes], Multi-AZ deployment and MySQL are located in multiple availability zones, one of them becomes a cold standby state.
When failure occurs on master side, it will be switched to the MySQL server on the slave side automatically. It means fault tolerance is high but note that the price will be doubled.

Allocated Storage

Disk space is used by DB.

DB Instance Identifier

DB instance name must be unique in your account.

Master Username, Master Password, Confirm Password

Specify the username and password when connecting to MySQL.

Step: Configure Advanced Settings

VPC

Choose any VPC, usually it’s the default VPC.

DB Security Groups

Choose a security group RDS belongs to.
Please refer to the following URL:
Working with DB Security Groups

In this security group, you must allow access to port 3306 from which AMIMOTO AMI security group belongs to.

Database Name

The database will be automatically created in RDS when you input a database name.
If you don’t input, start RDS then connect to MySQL client and run
create database
to create database.

EC2 to RDS Connection Confimation

When RDS is activated, make sure if you can ssh connect to AMIMOTO AMI instance.

$ mysql -h {endpoint} -P 3306 -u {mymasteruser} -p 

You can find Endpoint in the RDS information of AWS Management Console.

mymasteruser and password are specified when you want to create RDS Master Username, Master Password.

Data Migration and wp-config.php rewriting

If you are migrating MySQL data to RDS in AMIMOTO AMI, perform the following steps below:

Export data via wp-cli

Using wp-cli, you can export the DB data.

$ wp --path=/path/to/wordpress db export

/path/to/wordpress is the directory where WordPress is installed.
In AMIMOTO AMI, it’s usually in /var/www/vhosts/{instance_id} .

Importing to RDS by mysql command

Run the mysql command to import the file which is exported by wp-cli to RDS.

$ mysql -h {endpoint} -P 3306 -u {mymasteruser} -p {databasename} < dump.sql

Rewriting wp-config.php

Please rewrite the following in wp-config.php :

 
// ** MySQL settings - You can get this info from your web host ** //
$db_data = false;
if ( file_exists('/opt/aws/cloud_formation.json') ) {
        $db_data = json_decode(file_get_contents('/opt/aws/cloud_formation.json'), true);
        if ( isset($db_data['rds']) ) {
                $db_data = $db_data['rds'];
                $db_data['host'] = $db_data['endpoint'] . ':' . $db_data['port'];
        }
}
if ( !$db_data ) {
        $db_data = array(
                'database' => 'RDS_DATABASE_NAME_HERE',
                'username' => 'RDS_USER_NAME_HERE',
                'password' => 'RDS_PASSWORD_HERE',
                'host'     => 'RDS_ENDPOINT_HERE',
        );
} 

• RDS_DATABASE_NAME_HERE : Database Name which was set in Configure Advanced Settings
• RDS_USER_NAME_HERE : Master Username which was set in Specify DB Details
• RDS_PASSWORD_HERE : Master Password which was set in Specify DB Details
• RDS_ENDPOINT_HERE : RDS endpoint

When you set as above in wp-config.php, let’s check if we can connect to WordPress.

Stop MySQL process in AMIMOTO AMI

Since MySQL was moved to RDS, MySQL process in AMIMOTO AMI is no longer necessary.

You can stop the MySQL server and turn off automatic start of MySQL server through the following process:

Rewrite the configuration file

In AMIMOTO AMI, the middleware settings use chef-solo, where they are described in /opt/local/amimoto.json

To stop starting MySQL, please rewrite the file as follows:

{
  "nginx" : { "config" : { "user" : "nginx", "group" : "nginx" } },
  "php"   : { "config" : { "user" : "nginx", "group" : "nginx" } },
  "mysql" : { "enabled" : false },
  "phpfpm"  : { "enabled" : true },
  "run_list" : [ "recipe[amimoto]" ]
}

For AMIMOTO AMI HHVM version, rewrite as follows:

{
  "nginx" : { "config" : { "user" : "nginx", "group" : "nginx" } },
  "php"   : { "config" : { "user" : "nginx", "group" : "nginx" } },
  "mysql" : { "enabled" : false },
  "hhvm"  : { "enabled" : true },
  "run_list" : [ "recipe[amimoto]" ]
}

Then run the following command:

$ sudo /opt/local/proviosion
$ service mysql stop

Then MySQL process in AMIMOTO AMI no longer works.

Slide