Symetrik Domino Consulting

Amazon’s Relational Database Service (RDS) is their answer to a hosted, maintained database service. They currently offer several versions of MySQL on their instances, the ability to incrementally backup the databases, and offer full fail-over to another availability zone. Pretty awesome stuff compared to having to manage your own MySQL servers, keep them updated, patched, and manage backing them up.

I am writing these instructions because I didn’t find a Ubuntu package to install the RDS Command Line Toolkit for Ubuntu 10.04, and I couldn’t find any clear concise instructions on how to install them, or how to use them. I believe that this is one area in which Amazon fails at miserably. The RDS is a great service, but you have to be a rocket scientist to figure out how to install and use them to use the service to begin with.

UPDATE: Scott Moser is working on an Ubuntu package for the RDS tools to go along with the EC2 tools. You can find his work here.

The main hurdle in using RDS is that you need to change a database parameter to be able to import a MySQL database from a local server dump to the Amazon RDS server instance that you create. You have to change database instance parameters with the command line tools because you can view, but not change any of the database instance parameters from Amazon’s web based management console. Honesty, this was way harder than it should have been.

First, I’ll outline the instructions for installing the RDS command line toolkit, then I’ll supply the parameters that I had to change. Judging from the discussions I found over at the Amazon RDS forum, the need to change some parameters to get RDS to work seems to be a pretty common occurrence.

The RDS Command Line Toolkit is available from Amazon’s website here.

I downloaded them locally and unzip’d the zip file to a directory called rds.

On the Ubuntu server:

$ sudo mkdir -p /usr/local/aws
$ mkdir ~/.ec2

Transfer the unzip’d rds directory to /usr/local/aws/rds on the server using sftp.

Transfer your cert-.pem and pk-.pem files to /home/ubuntu/.ec2/ directory

Set the permissions for the files that you just uploaded
On the Ubuntu server:

$ cd /usr/local/aws/rds/bin
$ sudo chmod 744 *
$ sudo chmod 0700 ~/.ec2
$ sudo chmod 0400 ~/.ec2/*

Setup the credential file

$ cd /usr/local/aws/rds
$ sudo cp credential-file-path.template credential-file
$ sudo nano credential-file

Add your own credentials into this file. This should be pretty self explanatory.

# Enter the AWS Keys without the < or >
# These can be found at http://aws.amazon.com 
# under Account->Security Credentials
AWSAccessKeyId=
AWSSecretKey=

Set the permissions on the credential file

$ sudo chmod 600 /usr/local/aws/rds/credential-file

Add these lines to the end of your ~/.bashrc file

$ sudo nano ~/.bashrc

Here’s the lines to add:

# Set Java home directory for EC2 tools
export JAVA_HOME=/usr/lib/jvm/java-6-openjdk

# Set location of AWS key
export EC2_REGION=eu-west-1
export EC2_URL=https://eu-west-1.ec2.amazonaws.com
export EC2_PRIVATE_KEY=~/.ec2/pk-.pem
export EC2_CERT=~/.ec2/cert-.pem

# Set location of the ec2 and rds command line tools
export EC2_HOME=/usr
export AWS_RDS_HOME=/usr/local/aws/rds

# Set AWS path
export PATH=$PATH:$EC2_HOME/bin:$AWS_RDS_HOME/bin

You will notice that there are a few lines that we added or may have already been added that relate to EC2 instead of RDS. This because I had the ec2-api-tools package already installed. If you want to install those tools, you can do them with the following command, but you probably already have them installed.

sudo apt-get install ec2-api-tools

Now that you have the RDS tools installed and your credentials setup, you should “source” your .bashrc file.

$ source .bashrc

At this point, you should create a database instance and a database parameter group through Amazon’s Management console. You need to create a second parameter group because you can’t modify the default group.

You will also need to give the instance security group access to the RDS database security group through the web management console. The Ubuntu server at EC2 that will be accessing your database on RDS will need to be a member of the instance security group.

Once, you have created those two items, you should be able to see them through the command line tools that we just installed.

$ rds-describe-db-instances
$ rds-describe-db-parameter-groups

Now that you have the rds command line tools installed and working, you need to change the following parameters in your RDS parameter group. RDS uses latin1_swedish by default, so I needed to change the default for new databases to UTF-8. Additionally, I had some stored procedures in my MySQL databases, and there is no super-user privilege on RDS, so I needed to change the log_bin_trust_function_creators parameter to be able to upload my databases. Last, but not least, my 10 megabyte database wouldn’t upload, so I figured out that I needed to increase the max_allowed_packet value. Please see the commands, parameters, and values below. In this example, my additional database parameter group is called mygroupname.

This is where I see Amazon’s failure to make this easy for someone who hasn’t used RDS before. I was only uploading a 10MG database, which isn’t that big. I needed to change at the very least the max_allowed_packets to get my data into RDS. I would assume that this would be a common issue with just about anyone.

$ rds-modify-db-parameter-group mygroupname --parameters="name=character_set_server, value=utf8, method=immediate" 
$ rds-modify-db-parameter-group mygroupname --parameters="name=collation_server, value=utf8_general_ci, method=immediate"
$ rds-modify-db-parameter-group mygroupname --parameters="name=max_allowed_packet, value=67108864, method=immediate"
$ rds-modify-db-parameter-group mygroupname --parameters="name=log_bin_trust_function_creators, value=1, method=immediate"

Then you need to assign your database instance to use the new group:

$ rds-modify-db-instance mydbinstancename --db-parameter-group-name mygroupname

Last, you’ll need to reboot your instance to make the parameter group settings take affect. Note that this can take several minutes before you see your RDS instance back online, but it happened pretty quickly for me.

$ rds-reboot-db-instance mydbinstancename

To import your local database dump to your RDS instance, you use this command on your local server:

$ mysql -h mydbinstancename.ckhdo9otbvgb.eu-west-1.rds.amazonaws.com -uroot -pmypassword mydatabasename < mylocaldatabasedumpname.sql

Here's some instructions that I found somewhat useful.
Amazon RDS User Guide
Hosting a simple LAMP application in the cloud

4 Responses to “Installing Amazon RDS Command Line Toolkit on Ubuntu 10.04”