Day 44: Relational Database Service in AWS

What is AWS Relational Database Service (RDS)?

Amazon RDS is a managed database service by AWS that makes it easy to set up, operate, and scale relational databases in the cloud.

A relational database organizes data into tables (rows and columns) with relationships between them, and it uses SQL (Structured Query Language) for querying and data manipulation.

Instead of managing servers, patching, backups, and scaling manually, RDS handles these heavy-lifting tasks for you.

Why use RDS?

1.  Fully managed – AWS automates provisioning, patching, backups, and monitoring.
2.  Scalability – Easily scale compute and storage vertically or horizontally (read replicas).
3.  High availability – Multi-AZ deployments provide automatic failover for production systems.
4.  Security – Supports VPC isolation, IAM integration, encryption at rest (KMS), and encryption in transit (SSL/TLS).
5.  Cost-effective – Pay only for what you use; supports free tier for testing.

Supported Database Engines in RDS

AWS RDS supports multiple relational database engines:
• Amazon Aurora (MySQL & PostgreSQL-compatible, AWS-optimized, high performance)
• MySQL
• PostgreSQL
• MariaDB
• Oracle (license-included or BYOL)
• Microsoft SQL Server

Aurora is AWS’s own DB engine — up to 5x faster than MySQL and 3x faster than PostgreSQL.

Core Components of RDS
1. DB Instance → The actual database environment (compute + storage).
2. Endpoint & Port → The hostname & port to connect your applications.
3. Security Groups → Control inbound/outbound access to the DB.
4. Parameter Groups → Configurations for the DB engine.
5. Option Groups → Add-ons like Oracle TDE, SQL Server features, etc.
6. Subnet Group → Defines which subnets RDS can launch into.

Key Features
1. Multi-AZ Deployment → Ensures high availability by maintaining a standby copy.
2. Read Replicas → Scale read-heavy workloads by creating copies for read-only queries.
3. Automated Backups → Point-in-time recovery, retention up to 35 days.
4. Monitoring → CloudWatch metrics, Performance Insights, Enhanced Monitoring.
5. Encryption → At-rest (via AWS KMS) and in-transit (SSL).
6. IAM DB Authentication → Connect without passwords, using IAM tokens.

How RDS Works in Practice

Imagine you’re building an e-commerce application:
• Your app runs on EC2 instances or ECS/EKS containers.
• Instead of hosting MySQL on the EC2 VM (where you manage everything), you create an RDS MySQL instance.
• The app connects via RDS endpoint:

mysql -h mydb.rds.amazonaws.com -P 3306 -u admin -p

• AWS automatically takes care of:
• OS/database patching
• Disk scaling
• Automatic failover (if Multi-AZ)
• Backups for disaster recovery

You just focus on schema design and, queries.

When to Use RDS

• For traditional relational database workloads (finance, e-commerce, ERP, CRM, etc.)
• When you need high availability and automatic scaling.
• If you want to reduce DBA overhead (patching, backups, monitoring).
• For applications that use SQL-based relational data models.

Step-by-step: Create a Free-Tier MySQL RDS and connect from EC2

Plan at a glance
1. Create a MySQL RDS (free tier) — note endpoint & port.
2. Create an EC2 instance (same VPC/subnet as RDS or with network path).
3. Create an IAM role with RDS access (use for enhanced security if you use IAM DB auth; otherwise needed for instance profile tasks).
4. Attach the IAM role to EC2 instance.
5. Configure Security Groups so EC2 can reach RDS on port 3306.
6. Install mysql client on EC2 and connect to the RDS endpoint using DB credentials.
7. Take screenshot of successful connection (the mysql> prompt or a simple query result).

1) Create the RDS MySQL instance (console)
• AWS Console → RDS → Databases → Create database.
• Select Standard Create → Engine: MySQL.
• Edition: choose a free-tier compatible engine and version (e.g., MySQL 8.x) and db.t2.micro / db.t3.micro (Free Tier eligible).
• Templates: Free tier.
• DB instance identifier, username, and password — save these credentials.
• Networking:
• Select the VPC where your EC2 will be (or create matching one).
• Set Public accessibility = No (recommended). If you need direct public access for testing, set Yes — but prefer private + EC2 in same VPC.
• Choose a subnet group and availability zone (defaults fine).
• Connectivity & security:
• Choose or create a Security Group that allows inbound MySQL (port 3306) from your EC2 security group (best) or your IP (for quick test).
• Create the DB. Wait until status becomes Available.
• Copy the Endpoint and Port (you’ll need these).

2) Create an EC2 instance (console)
• EC2 → Instances → Launch instance.
• AMI: Amazon Linux 2 or Ubuntu LTS (examples below include both).
• Instance type: t2.micro (free tier).
• Configure Network: pick the same VPC and a subnet that can route to the RDS subnet.
• IAM role: you can attach the IAM role here (create role first if required).
• Security group: allow SSH (port 22) FROM your IP and allow outbound to RDS (outbound default is usually all traffic).
• Launch and SSH into the instance.

3) Create IAM role with RDS access (console)
• IAM → Roles → Create role → AWS service → EC2 → Next.
• Attach policies:
• For general RDS management or IAM DB Auth: attach AmazonRDSFullAccess (only for learning) OR more restricted policies (prefer).
• If you plan to use IAM DB Authentication for MySQL, attach AmazonRDSFullAccess or ensure rds-db:connect permissions are present for the DB resource.
• Name the role (e.g., EC2-RDS-Access-Role) and create.
• Attach to EC2 instance: in EC2 console → Select instance → Actions → Security → Modify IAM role → attach role.

Note: If you do not use IAM DB auth, you still can attach role for other automation tasks; DB connection will rely on DB username/password.

4) Security groups (important)
• RDS security group inbound rule: allow MySQL/Aurora (TCP 3306) from the EC2 instance security group (use SG ID as source). This is more secure than permitting IP addresses.
• EC2 security group: Allow SSH from your IP (TCP 22). Outbound: allow destination port 3306 (default outbound allow all works).

5) Install mysql client on EC2 & connect (commands)

Amazon Linux 2

# Update
sudo yum update -y

# Install MariaDB client (works as mysql client)
sudo yum install -y mariadb

# Or if you prefer the mysql client binary for MySQL:
# sudo yum install -y mysql

Ubuntu

sudo apt update && sudo apt upgrade -y
sudo apt install -y mysql-client

Connect to RDS (Remember to replace placeholders)

# Example:
mysql -h your-rds-endpoint.rds.amazonaws.com -P 3306 -u your_db_user -p

# Then enter the DB password when prompted.

Once connected you should see the mysql> prompt. Run a simple query:

SHOW DATABASES;
SELECT NOW();

6) If you want to use IAM DB Authentication (optional)
• Enable IAM DB auth when creating RDS (or modify parameter group).
• Create role with rds-db:connect and proper trust relationship for EC2.
• Use AWS CLI on EC2 to get an authentication token:

# Example: generate token (install aws cli v2 and configure credentials on EC2)
aws rds generate-db-auth-token --hostname your-rds-endpoint.rds.amazonaws.com --port 3306 --region us-east-1 --username your_db_user
# Use the resulting token as the password with mysql client and --enable-cleartext-plugin if needed:
mysql --host=your-rds-endpoint.rds.amazonaws.com --port=3306 --user=your_db_user --enable-cleartext-plugin --password=TOKEN

(Using IAM DB auth has extra setup steps — ask if you want a full walkthrough.)

7) Troubleshooting checklist
• RDS status = Available.
• Use the correct endpoint and port.
• DB username & password are correct (copy/paste carefully).
• EC2 and RDS are in same VPC or connected via routing (peered VPC requires routing & SG updates).
• Security groups: RDS SG inbound permits EC2 SG on port 3306.
• If RDS is not public, EC2 must be in same VPC or have VPN/direct connect/bastion.
• If using public access, ensure RDS public endpoint and SG allow your IP (less secure).

8) Capturing the small-win screenshot
• SSH to EC2, run the mysql command and capture the terminal showing:
• mysql> prompt OR
• Output of SHOW DATABASES; or SELECT NOW();
• Save screenshot(s) and post as proof. That’s your small win