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/provision
$ service mysql stop
Then MySQL process in AMIMOTO AMI no longer works.