AWS RDS Tutorial with Hands On [Step-by-Step]

In this tutorial, we will learn about AWS RDS i.e Relational Database Service and then walk through the step-by-step process of creating an Amazon RDS DB instance. After that, we will connect to the database through MySQL Workbench and start working on our database without wasting our time on administration-related tasks.

Before getting our hands dirty we will get a quick overview of Amazon RDS so let's get started.

 

AWS RDS overview

Amazon RDS is a managed relational database service that provides you with six familiar database engines to choose from, including Amazon Aurora, MySQL, MariaDB, Oracle, Microsoft SQL Server, and PostgreSQL.

Let's have a look at the features of Amazon RDS.

 

1. Lower administrative burden

Amazon RDS lowers the administrative burden through its user-friendly options. We can use the AWS Management Console, the Amazon RDS Command Line Interface, or simple API calls to access the capabilities of a production-ready relational database in minutes. The instances are pre-configured with parameters and settings appropriate for the engine and class we select. RDS makes sure that relational database software for our deployment is up-to-date with automatic software patching. After analyzing configuration and usage metrics from our database instances, it also provides us with best practice guidance.

 

2. Performance

Amazon RDS provides us with General Purpose (SSD) Storage and Provisioned IOPS (SSD) Storage. General Purpose Storage is a storage type that is suitable for a broad range of database workloads whereas Amazon RDS Provisioned IOPS Storage is an SSD-backed storage option designed to deliver fast, predictable, and consistent I/O performance.

 

3. Scalability

The compute and memory resources powering the deployment of the Amazon RDS database instance can be scaled up to a maximum of 32 vCPUs and 244 GiB of RAM and that too within a few minutes. According to our storage requirements, we can also provide additional storage on the fly with zero downtime. Through Read Replicas, we can serve high-volume application read traffic from multiple copies of our data, thereby increasing aggregate read throughput.

 

4. Availability and durability

Amazon RDS  provides an automated backup feature that enables point-in-time recovery for our database instance. We can also create database snapshots and store them in Amazon S3. Through Amazon RDS, we can also create a Multi-AZ database instance that results in synchronous replication of our data to a standby instance in a different Availability Zone (AZ). In the event of a hardware failure, Amazon RDS will automatically replace the compute instance powering our deployment.

 

5. Security

Amazon RDS ensures security by allowing us to encrypt our databases using keys we can manage through AWS Key Management Service (KMS).  It is recommended to run database instances in Amazon VPC which allows us to isolate databases in our own virtual network. We can also configure firewall settings and control network access to our database instances. AWS RDS provides us the ability to control the actions that our AWS IAM users and groups can take on specific AWS RDS resources as it is integrated with AWS Identity and Access Management (IAM).

 

6. Manageability

AWS RDS provides Amazon CloudWatch metrics for our database instances at no additional charge. We can use the RDS Management Console to view key operational metrics, including compute/memory/storage capacity utilization, I/O activity, and instance connections. Amazon RDS also provides Enhanced Monitoring, which provides access to over 50 CPU, memory, file system, and disk I/O metrics, and Performance Insights, an easy-to-use tool that helps us quickly detect performance problems. We can also be notified via email or SMS text messages of our database events through Amazon SNS. In order to support compliance and enhance security, AWS RDS integrates with AWS Config that records and audits changes to the configuration of our DB instance.

 

7. Cost-effectiveness

With AWS RDS we pay only for what we use i.e. a monthly charge for each database instance launched. We can also stop and start database instances for up to 7 days at a time that means the database is not required to be running all the time. Note that through AWS RDS Reserved Instances, we can reserve a DB instance for a one or three-year term and receive great discounts compared to On-Demand pricing.

 

The source of the above-stated details is the official website of AWS. Now let's create a database through Amazon Relational Database Service

 

AWS RDS - Hands-on

First, we will log in to our AWS account then we will click on RDS under the Services tab. We can see that currently, we do not have any databases. We will click on Create Database.

 

Database configurations

Here we will choose a database creation method. We can choose from two options either Standard create or Easy create. Here we will choose Standard create so that we can set all the configuration options on our own and not use already set recommended best-practice configurations. For the Engine options, we will choose MySQL.

 

In Templates, we will choose Production so that we can explore all the options while configuring the database but we will make sure to remain under the free tier.

 

In Settings, we will enter our DB instance identifier. We will leave it to the default value for now i.e. database-1. In Credentials Settings, we will enter username and password.

 

In the DB instance class we will choose Burstable classes after that we will enable Include previous generation classes and choose db.t2.micro.

 

In Storage, we will select our storage type to be General Purpose SSD(gp2).  We will leave the allocated storage to be 20 GiB.

 

In Availability & Durability, we will select Do not create a standby instance for now. Note that it's recommended to create a standby instance for production usage to provide data redundancy which provides fault tolerance. It also eliminates I/O freezes and minimizes latency spikes during system backups as stated.

 

In Connectivity, we will leave the VPC and Subnet group to be default. Here we will make our database publicly accessible.

 

For the VPC security group, we will create a new security group named mydbsg.

 

In Database authentication, we will select Password Authentication for now.

 

In Additional configuration, we will provide the name of our initial database and leave the remaining settings to default. Here we can configure backup related options like backup retention period and backup window.

 

We can also set our configurations related to the Monitoring of our database.  Note that if we enable enhanced monitoring metrics we can see that how different processes or threads use the CPU. We can also select the log types to publish to Amazon CloudWatch Logs.

 

Here we can configure Maintenance-related options like Enable auto minor version upgrade and Maintenance window. We can also implement Deletion protection on our database. Through this option, we protect our database from deleting accidentally. Note that if we want to delete the database first we will have to disable deletion protection and then delete it.

 

RDS Security Group

Here we can view the Inbound rules of our newly created security group for our RDS instance. It allows traffic only from our Public IP address only. If we want it to be accessible from anywhere then we will have to change the source from our IP address to Anywhere.

 

MySQL Workbench

Now we want to execute queries in our database. For this, we want to know our Database Endpoint. We can find it under the Connectivity & Security section of our RDS instance.

 

Now we will set up a new connection in MySQL Workbench. We will provide a Connection Name then the Hostname that will be the DB Endpoint we just saw. Then we will provide the username and password that we have just set while configuring our RDS instance.

 

After successful connection, we will execute the following script to check that everything is working fine.

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

 

Here we can see our Database and the table we have just created.

 

Conclusion

With this, we have come to the end of our tutorial in which we learned about Amazon Relational Database Service and then walked through the process of creation of our AWS RDS database instance. We also looked into the security group of our RDS database instance and in the end, we logged in to our database through MySQL workbench.

Stay tuned for some more informative tutorials coming ahead and feel free to leave any feedback or queries in the comments section.

Happy learning!