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.
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).
First, we need to create an Amazon EC2 instance from which to access the RDS:
ssh -i "docker.pem" email@example.com
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:
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 ‘18.104.22.168/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.
Several options are available to launch a RDS database instance:
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.
The database to create. The parameter meaning is different for different databases.
The id of the DB instance stored as a lowercase string.
The storage in gigabytes to be initially allocated to the database instance.
The compute and memory capacity of the DB instance.
The database engine to use such as “mysql”, “oracle-se”, “oracle-ee”, “postgres”.
The master user .
The master user password.
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.
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.
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:
Oracle Database SID
The storage in gigabytes to be initially allocated to the database instance. A value in the range 50 6144 may be specified.
The database engine to use. Oracle Database has various options oracle-se1, oracle-se2, oracle-se, oracle-ee.
EC2 Availability zone
The port number
The master user for Oracle database
The master user password
The Oracle Database version
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 22.214.171.124.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
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.
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:
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/).