Saturday, April 20, 2024

Setting up AWS RDS

April 18, 2012

Amazon Web Services (AWS) provides a managed MySQL solution via Relational Database Service (RDS). The following instructions will enable you to configure and run RDS. Refer to Using Amazon Web Services for initial information about AWS requirements.

The following instructions are for Ubuntu.

Pre-requisites

$ [ -z `which java 2>/dev/null` ] && sudo apt-get install -y openjdk-6-jre-headless
$ [ -z `which unzip 2>/dev/null` ] && sudo apt-get install -y unzip

Installation

# See http://docs.amazonwebservices.com/AmazonRDS/latest/CommandLineReference/Welcome.html?r=8890
$ cd $HOME
$ mkdir -p aws
$ cd aws
$ wget http://s3.amazonaws.com/rds-downloads/RDSCli.zip
$ unzip RDSCli.zip
$ ln -s RDSCli-*/ rds

Configuration

You need to create the $HOME/aws/credentials file with your specific account details.

AWSAccessKeyId=XXXXXXXXXXXXXXXXX
AWSSecretKey=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

The following should be added to a given startup profile

export JAVA_HOME=/usr/lib/jvm/java-6-openjdk/jre
export AWS_RDS_HOME=$HOME/aws/rds
export PATH=$AWS_RDS_HOME/bin:$PATH
export AWS_CREDENTIAL_FILE=$HOME/aws/credentials

A number of different MySQL versions are available.

$ rds-describe-db-engine-versions -e mysql
VERSION  mysql  5.1.45  mysql5.1  MySQL Community Edition  Mysql 5.1.45
VERSION  mysql  5.1.49  mysql5.1  MySQL Community Edition  MySQL 5.1.49-R1 with innodb plugin
VERSION  mysql  5.1.50  mysql5.1  MySQL Community Edition  MySQL 5.1.50-R3
VERSION  mysql  5.1.57  mysql5.1  MySQL Community Edition  MySQL 5.1.57-R1
VERSION  mysql  5.1.61  mysql5.1  MySQL Community Edition  MySQL 5.1.61-R1
VERSION  mysql  5.5.12  mysql5.5  MySQL Community Edition  MySQL 5.5.12-R1
VERSION  mysql  5.5.20  mysql5.5  MySQL Community Edition  MySQL 5.5.20-R1
VERSION  mysql  5.5.8   mysql5.5  MySQL Community Edition  MySQL 5.5.8.R1 GA

Verification

$ rds-describe-db-instances
$ rds-describe-db-parameter-groups
DBPARAMETERGROUP  default.mysql5.5  mysql5.5  Default parameter group for mysql5.5

It is not possible to change the default parameter group so you need to create a copy for any future configuration modifications.

$ rds-create-db-parameter-group running-mysql55 -d 'Running MySQL 5.5' -f mysql5.5

Creating a new instance

$ rds-create-db-instance book2 --db-instance-class db.m1.large --allocated-storage 20 --engine mysql --master-username dba --master-user-password passwd --db-parameter-group-name running-mysql55
DBINSTANCE  book2  db.m1.large  mysql  20  dba  creating  1  ****  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync
$  rds-describe-db-instances
DBINSTANCE  book2  db.m1.large  mysql  20  dba  creating  us-east-1a  1  ****  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync

It can take some time (e.g. 5-10 minutes for your instance to be available. When ready it will show available. In this case, the instance host is defined, however the instance is not yet accessible.

$ rds-describe-db-instances
DBINSTANCE  book2  2012-04-18T15:09:08.420Z  db.m1.large  mysql  20  dba  backing-up  book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com  3306  us-east-1a  1  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync

While waiting, you can create the necessary permissions to access the RDS instance.

$ rds-describe-db-security-groups
SECGROUP  default  default
$ rds-authorize-db-security-group-ingress default --cidr-ip 10.194.163.1/21
SECGROUP  default  default
      IP-RANGE  10.194.163.1/21  authorizing
$ rds-describe-db-security-groups
SECGROUP  default  default
      IP-RANGE  10.194.163.1/21  authorized

$ rds-describe-db-instances
DBINSTANCE  book2  2012-04-18T15:09:08.420Z  db.m1.large  mysql  20  dba  available  book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com  3306  us-east-1a  1  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync

The following can be used to monitor.

$ while [ : ]; do  date; rds-describe-db-instances; sleep 15; done
Wed Apr 18 19:27:14 UTC 2012
DBINSTANCE  book2  db.m1.large  mysql  20  dba  creating  1  ****  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync
...
Wed Apr 18 19:35:48 UTC 2012
DBINSTANCE  book2  2012-04-18T19:31:51.919Z  db.m1.large  mysql  20  dba  available  book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com  3306  us-east-1c  1  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  running-mysql55  in-sync

Confirmation of running MySQL Instance.

$ mysql -udba -ppasswd -hbook2.cqmcgpjfkies.us-east-1.rds.amazonaws.com -e "SELECT VERSION()"
+------------+
| VERSION()  |
+------------+
| 5.5.20-log |
+------------+

Terminating

$ rds-delete-db-instance book2 --skip-final-snapshot
    Once you begin deleting this database, it will no longer be able to accept
    connections.
    Are you sure you want to delete this database? [Ny]y
DBINSTANCE  book2  2012-04-18T14:58:54.251Z  db.m1.large  mysql  20  dba  deleting  book2.cqmcgpjfkies.us-east-1.rds.amazonaws.com  3306  us-east-1a  1  n  5.5.20  general-public-license
      SECGROUP  default  active
      PARAMGRP  default.mysql5.5  in-sync

You can pass -f to avoid the prompt.

Filed Under: Article