Amazon RDS: Amazon Relational Database Service or MySQL in the Cloud for Ruby On Rails.

For watchthatsite.com (not public yet) I have an instance on EC2 with Rails and MySQL but was looking for a more solid hosting solution for MySQL. And how fortunate, Amazon came out with the solution I need this week. Basically with two command line instructions you can start a new server with mysql configured, tuned, and secured. In this blog entry I will go through the steps that perform to move my sql database to Amazon RDS.

You can find more information on Amazon Relational Database Service (API Version 2009-10-16) here.

Prerequisite: you need to signup for an account on aws.amazon.com, it can be used for EC2, S3, SimpleDb and all the other services AWS provides.

1) Install the Command Line Toolkit

First thing, go download the command line toolkit and read the README.TXT on how to install it. In short you unzip the files, I did put mine at /Developer/aws/RDSCli-1.0.001. Then you create a credential file which contains your AWS access key id and secret key. Then I configured my ~/.bash_profile as follows:

export AWS_RDS_HOME=/Developer/aws/RDSCli-1.0.001
export AWS_CREDENTIAL_FILE=$AWS_RDS_HOME/credential-file-path.conf
export JAVA_HOME=/Library/Java/Home
export PATH=$AWS_RDS_HOME/bin:$PATH

to see that the command line toolkit is setup correctly type $rds —help

You will need the command line tool to execute several commands described here after.

2) Create an Instance

Let’s create a MySQL Server instance. RDS offers the following 5 server instance classes:

db.m1.small (1.7 GB of RAM, $0.11 per hour)
db.m1.large (7.5 GB of RAM, $0.44 per hour)
db.m1.xlarge (15 GB of RAM, $0.88 per hour)
db.m2.2xlarge (34 GB of RAM, $1.55 per hour)
db.m2.4xlarge (68 GB of RAM, $3.10 per hour)

I will choose a small instance which I will call dbserver1 with a database name db1 and allocate 5g of database space. I also set the master username as admin and password as secret.

$ rds-create-db-instance —db-instance-identifier db1 —allocated-storage 5 —db-instance-class db.m1.small —engine MySQL5.1 —master-username admin —master-user-password secret —db-name db1 —headers

The output is the following:

DBINSTANCE DBInstanceId Class Engine Storage Master Username Status Backup Retention
DBINSTANCE db1 db.m1.small mysql5.1 5 admin creating 1
SECGROUP Name Status
SECGROUP default active
PARAMGRP Group Name Apply Status
PARAMGRP default.mysql5.1 in-sync

Now you have a server running and you are being billed $0.11 per hour, that’s like $80 a month without bandwidth but with backup…and it took only 2 minutes to get going. Can’t beat that.

To see all the instances you have you can issue the

rds-describe-db-instances —headers

3) Grant Network Access

So I will grant access from my notebook, assuming the ip address is 24.19.0.48 (you can also specify ranges i.e. 24.19.0.0/50). (Note that access was revoked by AWS, not sure why??)

rds-authorize-db-security-group-ingress default —cidr-ip 24.19.0.48 —headers

I also have an ec2 instance which I want to grant access to

rds-authorize-db-security-group-ingress default —ec2-security-group-name watchthatsite —ec2-security-group-owner-id 526541544691

Note the ec2-security-group-owner-id is your Amazon AWS account number, you can find it for example on you account activity page. To see your security configuration issue the following command: rds-describe-db-security-groups default —headers

4) Using the Database

To use your database you first need to find out the endpoint address of your new server. So describe you instances:

rds-describe-db-instances —headers command

DBINSTANCE DBInstanceId Created Class Engine Storage Master Username Status Endpoint Address Port AZ Backup Retention
DBINSTANCE db1 2009-10-28T22:53:31.666Z db.m1.small mysql5.1 5 admin available db1.cyhik6zpub5c.us-east-1.rds.amazonaws.com 3306 us-east-1b 1
SECGROUP Name Status
SECGROUP default active
PARAMGRP Group Name Apply Status
PARAMGRP default.mysql5.1 in-sync

You find out your endpoint address, for me db1.cyhik6zpub5c.us-east-1.rds.amazonaws.com

So now you can connect to your database:

mysql -h db1.cyhik6zpub5c.us-east-1.rds.amazonaws.com -P 3306 -u admin -p db1

Let’s configure my Rails application to point to that database and run a migration:

So I change my config/database.yml to point to the above database

development:
adapter: mysql
host: db1.cyhik6zpub5c.us-east-1.rds.amazonaws.com
reconnect: false
database: db1
username: admin
password: secret

rake db:migrate

Wow, seem to work.

Let connect to the mysql console and do a show tables;

+-------------------+
| Tables_in_db1     |
+-------------------+
| schema_migrations | 
| users             | 
| watches           | 
+-------------------+

Yep, all there.

Now I still have to move my old production database to the new one, so let’s dump the data from my old database:

mysqldump watchthatsite_development -u admin > wts.sql

and reload that data in the new database:

mysql -h db1.cyhik6zpub5c.us-east-1.rds.amazonaws.com -P 3306 -u admin -p db1 < wts.sql

Restarting my Rails serverâ€¦That’s all!

Enjoy,
Daniel.