Day 44: Relational Database Service in AWS

One of the pivotal pillars of AWS’s infrastructure is the Relational Database Service (RDS), a game-changing solution that has transformed the way organizations manage, operate, and scale their relational databases. Amazon Relational Database Service (Amazon RDS) is a collection of managed services that makes it simple to set up, operate, and scale databases in the cloud, So let’s dive into RDS today

What is RDS (Relational Database Service)?

Amazon RDS is a Relational Database Service by Amazon Web Services. A relational database is a type of database that stores data in tables with rows and columns. Amazon RDS provides an industry-standard relational database that is cost-efficient with resizable capacity. It gives users solutions for easier setup, operation, and scale of a relational database in the cloud.

RDS supports several popular relational database engines including:

1. MySQL: An open-source relational database management system known for its performance, reliability, and ease of use.
2. PostgreSQL: Another open-source database system known for its advanced features, extensibility, and strong emphasis on standards compliance.
3. Microsoft SQL Server: A widely-used relational database management system developed by Microsoft, known for its robust features and integration with Microsoft technologies.
4. Oracle Database: A powerful and feature-rich relational database system known for its scalability, security, and comprehensive functionality.
5. MariaDB: An open-source fork of MySQL that offers enhanced performance and features.
6. Amazon Aurora: A MySQL and PostgreSQL-compatible database engine developed by AWS, designed to provide high performance and availability.

Some of the key features of Amazon RDS are:

1. Automated Database Management: RDS automates routine tasks such as database provisioning, patching, backups, and scaling, reducing the operational overhead and allowing developers to focus on application development.
2. Multiple Database Engine Support: RDS supports various popular relational database engines, including MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, and Amazon Aurora, offering flexibility in choosing the right engine for your application’s needs.
3. High Availability and Fault Tolerance: RDS provides built-in mechanisms for high availability through Multi-AZ deployments, which replicate your database instance across multiple Availability Zones to ensure failover in case of a hardware or software failure.
4. Automated Backups and Restore: RDS enables automated backups of your database, and you can easily restore your database to any point in time within your backup retention window. You can also create manual snapshots for longer-term retention.
5. Scalability: RDS allows you to scale your database resources vertically (instance type upgrades) or horizontally (read replicas for read scalability). Amazon Aurora, in particular, provides auto-scaling features to adjust capacity based on actual usage.
6. Security: RDS offers various security features, including network isolation using Amazon VPC, encryption at rest using AWS Key Management Service (KMS), encryption in transit, and IAM database authentication.
7. Patch Management: RDS handles patch management and security updates for the underlying database software, ensuring that your database instances are up to date and secure.
8. Monitoring and Performance Insights: RDS provides monitoring tools and metrics through Amazon CloudWatch, giving you visibility into the performance of your database. Amazon RDS Performance Insights offers detailed performance data and recommendations for optimization.
9. Read Replicas: RDS supports the creation of read replicas, which can be used to offload read traffic from the primary database, improve read scalability, and enhance performance.
10. Global Databases (Amazon Aurora Global Databases): Amazon Aurora supports global databases, allowing you to replicate your database across multiple AWS regions for disaster recovery and low-latency global access.
11. Database Migration: RDS supports database migration tools and services that make it easier to move your existing databases to the cloud, including AWS Database Migration Service (DMS) and AWS Schema Conversion Tool (SCT).
12. Cross-Region Automated Backups: Amazon RDS offers the ability to automate backups across multiple AWS regions, enhancing data protection and disaster recovery capabilities.

Tasks -

Task 1)

- Create a Free tier RDS instance of MySQL
- Create an EC2 instance
- Create an IAM role with RDS access
- Assign the role to EC2 so that your EC2 Instance can connect with RDS
- Once the RDS instance is up and running, get the credentials and connect your EC2 instance using a MySQL client.

Open aws consolse > Search for RDS

Click on Create Database > Select Standard Create > Under “Engine options”, choose “MySQL”

Under Settings, I am providing the following details:

DB instance identifier: database-example-rd-1

Credentials Settings:

Master username: admin

And provide the password of your choice according to the constraints mentioned.

Configure other settings like storage, backups, VPC, and security groups according to your requirements. Review the configuration and click “Create Database”.

RDS creation takes a few minutes.

Create an EC2 instance

I am creating an instance named rds-ec2-instance.

I am configuring the security group to allow inbound traffic on the MySQL port (default is 3306).

Create an IAM role with RDS access. Assign the role to EC2 so that your EC2 Instance can connect with RDS.

Go to IAM Dashboard > Click on Roles > Create Role > Select the EC2 service for the trusted entity > In permission policies, attach the permission AmazonRDSFullAccess > Name the role as “rds-ec2” > Create Role.

The role is created.

Let’s assign this role to our EC2 which we created.

Go to your EC2 Management Console > Actions > Security > Modify IAM Roles

Attach IAM Role to our instance

Once the RDS instance is up and running, get the credentials and connect your EC2 instance using a MySQL client.

Go to RDS Dashboard > Select the Database you created > Copy the endpoint, port, and master username.

Click on Set up EC2 connection. Select the instance which we created for this purpose.

Let’s connect to the EC2 instance using SSH. And then install the MySQL client in the instance:

sudo apt-get update
sudo apt-get install mysql-client
mysql — version

To connect to the RDS instance using the MySQL client and the endpoint address, username, and password, we use the following command:

mysql -h <RDS_ENDPOINT> -P <RDS_PORT> -u <MASTER_USERNAME> -p
#The below details we copied when we created the RDS instance:
#<RDS_ENDPOINT> with the endpoint of your RDS instance
#<RDS_PORT> with the port number (default is 3306)
#<MASTER_USERNAME> with the master username
# ‘-h’ is used to specify the endpoint of MySQL server to which we want to connect (basically the host)

My command will look like this:

mysql -h database-example-rd-1.crlzycnnmkbk.ap-south-1.rds.amazonaws.com -P 3306 -u admin -p

After running this command, you will be prompted for the password. Give the password you created while creating RDS:

Yay! We have created a Free tier RDS instance of MySQL, an EC2 instance, assigned an IAM role with RDS access to the EC2 instance, and connected to the RDS instance from the EC2 instance using a MySQL client.