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
3) Grant Network Access
So I will grant access from my notebook, assuming the ip address is 22.214.171.124 (you can also specify ranges i.e. 126.96.36.199/50). (Note that access was revoked by AWS, not sure why??)
rds-authorize-db-security-group-ingress default --cidr-ip 188.8.131.52 --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
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!