RDS has been working out pretty well for AdGrok — it’s a one-click MySQL 5.1 instance that seems pretty promising:

• Automatic replication and failover to another deployment zone (colo)
• Easy to set up firewall configuration
• Easy to scale up (but not down) for CPU and disk space

If you’re just starting out, it’s a whole lot simpler than, say, a DRBD/Heartbeat/MySQL configuration running on EC2 instances.

There’s just one catch. It doesn’t use UTF-8 encoding by default, it uses latin1_swedish. If you’re going to do business outside the US, utf8 is a must.

The second catch — the Amazon web UI for managing RDS “parameter groups” is read-only. If you know the magick incantations, though, it’s not that bad. Here’s how to make it all go:

Install the RDS tools

I found them here, but that URL doesn’t seem very “authoritative.” The googles might find a newer version for you.

Unzip the archive into /opt, and add this to your ~/.bashrc — but note that the JAVA_HOME is a mac-specific thing — change that, and the REGION, to your liking, and go fetch your specific cert and private key from your EC2 “account” page. Note also that this assumes you made a symlink from the version of the RDS tools to /opt/rds, and from the ec2 tools to /opt/ec2.

export JAVA_HOME=$(/usr/libexec/java_home)
export EC2_HOME=/opt/ec2
export EC2_REGION=us-west-1
export AWS_RDS_HOME=/opt/rds
export EC2_PRIVATE_KEY=$HOME/.ssh/pk-SOMETHINGSOMETHING.pem
export EC2_CERT=$HOME/.ssh/cert-SOMETHINGSOMETHING.pem
export PATH=$EC2_HOME/bin:$AWS_RDS_HOME/bin:$PATH

Create your RDS instance

OK. Let’s assume you’ve got all that squared away now, and that you’re starting out with a new database (so you don’t have to convert the contents from latin1 to utf8). In the web interface, click the RDS tab, and create a new RDS instance. If in doubt, create a new security group. You can change the definition of the security group, but you can’t change the group for a given RDS instance. I set up “classes” of security groups for AdGrok — one for the blog, and one for the production application.

Let’s also assume you named your database “db1″ because you’re an engineer, and think that creativity in naming should be reserved for yachts.

Set up and assign the Parameter Group

Create and configure a new “parameter group” — the default parameter group, default.mysql5.1, isn’t editable.

rds-create-db-parameter-group utf8 -e mysql5.1 -d utf8 
 
rds-modify-db-parameter-group utf8 \
  --parameters="name=character_set_server, value=utf8, method=immediate" \
  --parameters="name=collation_server, value=utf8_general_ci, method=immediate"

Then assign your database instance to use the new group:

rds-modify-db-instance db1 --db-parameter-group-name utf8

And the final bit of pain — to make the parameter group settings take affect, you need to reboot the instance. Note that this will take several minutes before you see your RDS instance back online.

rds-reboot-db-instance db1

And finally, test that everything stuck by opening a mysql prompt:

mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+-------------------------------------------------+
| Variable_name            | VALUE                                           |
+--------------------------+-------------------------------------------------+
| character_set_client     | utf8                                            |
| character_set_connection | utf8                                            |
| character_set_database   | utf8                                            |
| character_set_filesystem | BINARY                                          |
| character_set_results    | utf8                                            |
| character_set_server     | utf8                                            |
| character_set_system     | utf8                                            |

You want all utf8s — if you don’t, make sure your .my.cnf has these lines:

[client]
host=....rds.amazonaws.com
default-character-set=utf8

and, if you’re on Ruby on Rails, your config/database.yml has these lines:

production:
  adapter: mysql
  encoding: utf8
  collation: utf8_general_ci

Related posts:

1. HOWTO: Deal with Amazon’s “requested Availability Zone is no longer supported” error
   In shutting down the AdGrok servers (talk about bittersweet…), I stopped the instances, but then remembered I wanted to shred the files first, so I clicked “start,” and was greeted......
2. Run a RAID6 on Amazon EBS For Fun and Profit
   With all this badmouthing Amazon’s Elastic Block Store, I wanted to share how we’ve set up our MySQL server for AdGrok. Step 1: Create a bajillion tiny EBS volumes We’ve......
3. HOWTO install etherpad on ubuntu 9.10
   Etherpad was opensourced by google, and has some generic installation instructions. Here’s the translation for Ubuntu Karmic Koala (release 9.10): Install the prerequisites: sudo apt-get install mysql-server-5.1 mercurial sun-java6-jdk sun-java6-jre......
4. HOWTO enable the query log on MySQL on Mac OS X
   Tailing the MySQL query log in real time can be a lifesaver for any developer, and it’s pretty easy to do: Make a file for the mysqld process to write......