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:
1 2 3 4
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.
The output is the following:
1 2 3 4 5 6
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 18.104.22.168 (you can also specify ranges i.e. 22.214.171.124/50). (Note that access was revoked by AWS, not sure why??)
I also have an ec2 instance which I want to grant access to
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:
1 2 3 4 5 6
You find out your endpoint address, for me db1.cyhik6zpub5c.us-east-1.rds.amazonaws.com
So now you can connect to your database:
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
1 2 3 4 5 6 7
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:
and reload that data in the new database:
Restarting my Rails serverâ€¦That’s all!