Amazon RDS (Relational Database Service) is a cloud service to set up and run relational databases in the cloud environment AWS. RDS provides administrative support and enterprise features. RDS benefits are that it is easy to administer, scalable, highly available and durable, and secure. Some of the databases RDS supported are MySQL, Oracle, PostgreSQL, and SQL Server.

In this tutorial we shall discuss creating a MySQL DB Instance with RDS and access the database. We shall also discuss creating an Oracle Database instance with RDS.

Why use Amazon RDS

Amazon RDS is a managed database service, which implies that it provides all the administration features such as database installation, scaling, and cloud out-of-the-box so that a developer may allocate more resources toward developing applications for a relational database rather than setting up and managing a database. A RDS DB instance may be launched from the Amazon RDS Console or from AWS Explorer. For those more used to the command line, a single command installs and configures a RDS database instance. RDS database instance configuration parameters and settings are pre-optimized without a DBA having to provide optimal settings.

DevOps is a high priority for most IT managers and RDS is integrable with DevOps tools such as Jenkins. Continuous monitoring with Amazon CloudWatch, and automation with automated patch updates, automated backups, and automatic host deployment are some DevOps features provided by RDS.

RDS supports the top four relational databases (Oracle, MySQL, SQL Server, and PostgreSQL) based on DBEngines ranking. MySQL compatible MariaDB, an open source database developed by developers of MySQL, is also supported. Amazon RDS supports a MySQL compatible database developed by Amazon called Amazon Aurora. In fact Aurora is provisioned only through RDS.  Aurora is a high performance, highly available (99.99% availability) database which provides high throughput (up to 5x standard MySQL).  

One of the benefits of the AWS Infrastructure as a Service (IaaS) is the ability to scale the infrastructure resources according to requirement and RDS provides scaling for compute, memory and storage resources.

Durability and high availability are other top priorities of database users and Amazon RDS replicates data across multiple Amazon Zones so that failure of a single zone does not cause data loss or interruption in a database service.

The main benefit of Amazon RDS is that a user is able to avail of several other AWS services that are integrated with RDS, such as AWS Key Management Service (KMS) for database encryption, network isolation with Amazon Virtual Private Cloud (VPC), resource level permissions with Amazon Identity and Access Management (IAM), and event notification with Amazon Simple Notification Service (SNS).

 

Setting the Environment

 

First, we need to create an Amazon EC2 instance from which to access the RDS:

  1. Create an EC2 instance from Amazon Linux AMI 2016.09.0 (HVM), SSD Volume Type - ami-c481fad3. (Why Amazon Linux? Because it comes preinstalled with most of the AWS API tools and CloudInit.)
  2. Obtain the Public IP of the EC2 instance from the EC2 Console.
  3. SSH Login to the EC2 Instance:

ssh -i "docker.pem"  ec2-user@64.180.241.52

 

Creating a DB Security Group for RDS

 

Amazon RDS Security Groups are used to regulate the traffic to a DB Instance. Three types of security groups are available in Amazon RDS: DB, VPC, and EC2 security groups. Which security group to use? The use case for different security groups is different. To regulate access to a DB instance in a VPC use a VPC security group. For example, an RDS DB instance may need to share data with an application server running on an EC2 instance in the same VPC. If the RDS DB instance is not in a VPC use a DB security group. An EC2 security group regulates access only to a EC2 instance. We‘ll use a DB security group, which is for a DB Instance that is not in a VPC.

To create a RDS Security Group:

  1. Navigate to the RDS Dashboard for the region used; for example, https://console.aws.amazon.com/rds/home?region=us-east-1#security.
  2. Click on Create DB Security Group.

 

3. In the Create DB Security Group user interface specify a Name (rds for example) and Description and click on Create.

A new DB Security Group gets created, as shown below. Initially a new DB security group has no authorizations as indicated by the Status column. The “no-authorizations” implies that the DB security group is not associated with any source connection  such as a range of addresses or a EC2 security group.

4. Select the new Security Group and select a Connection Type as CIDR/IP from the drop-down list.

5. Specify the CIDR/IP of your current machine and the CIDR/IP to Authorize. A CIDR consists of the IPv4 and the Prefix length separated by a “/”.(For more information about CIDRs, visit https://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing.)

 

In our example we are using the CIDR/IP ‘64.180.241.52/32’ for current machine and authorized machine.

 

6. Click on Authorize.

A new Connection Type gets created and the security Group called “rds” gets authorized, as shown below.

 

Creating an RDS MySQL Database Instance

 

Several options are available to launch a RDS database instance:

  • AWS Explorer
  • Amazon Management Console RDS Dashboard
  • AWS Command Line Interface

The AWS Command Line Interface command create-db-instance launches a new RDS DB instance. Next, we shall use the create-db-instance command with the following command parameters.

Parameter

Description

Value

--db-name 

The database to create. The parameter meaning is different for different databases.   

mysqldb

--db-instance-identifier

The id of the DB instance stored as a lowercase string.

mysqldbinstance

--allocated-storage

The storage in gigabytes to be initially allocated to the database instance.

20

--db-instance-class

The compute and memory capacity of the DB instance.

db.m1.small

--engine

The database engine to use such as “mysql”, “oracle-se”, “oracle-ee”, “postgres”.

mysql

--master-username

The master user .

mysql

--master-user-password

The master user password.

mysql

 

Using the preceding command parameters, the AWS CLI RDS command to launch a MySQL DB Instance becomes as follows:

aws rds create-db-instance --db-name  mysql --db-instance-identifier mysqldbinstance  --allocated-storage 20 --db-instance-class db.m1.small --engine mysql  --master-username mysql --master-user-password mysql

The output indicates that a MySQL DB Instance MySQL5.6 has been launched.

The full syntax of the AWS RDS instance command is available at http://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html. When selecting a MySQL database name, reserved words must not be used. As an example, if “mysql” is used for the database name an error is generated, as shown below.

Describing a MySQL DB Instance

 

To get information about the provisioned RDS instances, run the AWS CLI  describe-db-instances command with the database identifier specified using --db-instance-identifier.

aws describe-db-instances --db-instance-identifier mysqldbinstance

The RDS instance detail is output.

Creating an RDS Oracle Database Instance

 

In this section we shall create a RDS DB Instance for Oracle Database. We shall use the AWS CLI command aws rds create-db-instance with the following command parameters:

Parameter

Description

Value

--db-name 

Oracle Database  SID

oradb

--db-instance-identifier

The id of the DB instance stored as a lowercase string.

oradbinstance

--allocated-storage

The storage in gigabytes to be initially allocated to the database instance. A value in the range 50 6144 may be specified.

20

--db-instance-class

The compute and memory capacity of the DB instance.

db.m1.small

--engine

The database engine to use. Oracle Database has various options  oracle-se1, oracle-se2, oracle-se, oracle-ee.

oracle-se

--availability-zone

EC2 Availability zone

us-east-1c 

--port 

The port number

1521

--master-username

The master user for Oracle database

oradb

--master-user-password

The master user password

oradb

--engine-version

The Oracle Database version

12.1.0.1.v1

 

  1. Using the preceding command parameters, run the following command.

aws rds create-db-instance --db-name  oradb --db-instance-identifier oradbinstance  --allocated-storage 20 --db-instance-class db.m1.small --engine oracle-se --availability-zone us-east-1c  --port  1521 --master-username oradb --master-user-password oradb --engine-version 12.1.0.1.v1

A RDS DB Instance is created for Oracle Database.

2. Describe the DB Instances with the describe-db-instances command.

aws rds describe-db-instances

The Oracle DB Instance detail is output.

 

Alternatively, the following command could be used to describe a DB Instance:

aws describe-db-instances --db-instance-identifier oradbinstance

 Displaying DB Instances in RDS Dashboard

 

RDS DB Instances may be listed in the AWS Management Console RDS Dashboard at https://console.aws.amazon.com/rds/home?region=us-east-1#dbinstances:. The region setting (us-east-1 in example) would be different for different regions.

 

 

Click on a DB Instance to get detail about an RDS database instance. For example, get the detail for the MySQL DB Instance. The DB Instance Endpoint is the DNS Name at which to access the DB Instance and is the form ‘hostname:port’.

Initially when a new RDS DB Instance is created the Endpoint may not yet be available. The Endpoint must be authorized; because we created a Security Group, the Endpoint is authorized as indicated by “authorized”. The Endpoint for the Oracle Database DB Instance oradbinstance.crbmlbxmp8qi.us-east-1.rds.amazonaws.com:1521 may be obtained similarly from the RDS Dashboard.

 

 

Next, we shall discuss various options available to connect to a DB Instance.

Connecting to an RDS MySQL Database Instance

 

The MySQL Utility may be used to connect to a MySQL DB Instance provisioned by RDS. In the following command –h command parameter specifies the DNS Name of the DB Instance, -P the port number,  -u the username and –p the password.

mysql -h   mysqldbinstance.crbmlbxmp8qi.us-east-1.rds.amazonaws.com -P 3306 -u mysqluser  -p mysql_pw

The MySQL Utility is started and the mysql> command prompt is displayed.

The MySQL Workbench may be used to connect to an RDS MySQL Database Instance. Specify the connection detail in the Connect to Database dialog and click on OK.

 

The Oracle DB Instance may be connected to using the SQL*Plus Utility with a command similar to the following.

sqlplus 'oradb@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbinstance.crbmlbxmp8qi.us-east-1.rds.amazonaws.com) (PORT=1521))(CONNECT_DATA=(SID=oradb)))'

In this tutorial we discussed the benefits of using Amazon RDS to create relational DB Instances. We also demonstrated using RDS with database instances for MySQL database and Oracle Database. RDS is not without limitations:

  • Being a managed service also implies that the direct access which some DBAs might be used to is not provided by RDS.
  • Direct access to a database shell, system procedures, and tables with advanced privileges, as well as direct host access, is restricted.
  • The cross-region read replicas and cross-region migration features are supported for only some of the databases such as MySQL and PostgreSQL, in addition to Aurora.

 

RDS is not without scope for further improvement. To mention one recent improvement, on November 30, Aurora became PostgreSQL-compatible (https://aws.amazon.com/about-aws/whats-new/2016/11/announcing-postgresql-compatibility-for-amazon-aurora/).