Deploying MySQL on RDS and Joining Tables Like It's Production

Rds challenge lab devto post 🗄️🐬

aws #rds #database #tutorial

Build Your DB Server and Interact With Your DB

INTRO
Did a hands-on AWS challenge lab on Amazon RDS. Task: spin up a managed database, connect from a Linux server, and run real SQL — create tables, insert data, join across tables. No hand-holding here, just requirements to figure out myself. Here's the walkthrough.

SCENARIO
Service: Amazon RDS
Role: Cloud/DB Admin
Goal: Launch RDS under set constraints, connect via EC2, run SQL (create, insert, select, join)

ARCHITECTURE

• LinuxServer (EC2) sits in the Lab VPC — this is the client
• RDS instance (Aurora or MySQL) in the same VPC
• Security group lets LinuxServer talk to RDS
• Flow: LinuxServer -> MySQL client (port 3306) -> RDS -> tables

STEP 1: LAUNCH THE RDS INSTANCE
Constraints for this lab:

• Engine: Aurora (Provisioned) or MySQL — no serverless
• Template: Dev/Test or Free tier
• No standby instance (single-AZ only)
• Instance size: db.t3.micro to db.t3.medium
• Storage: gp2, up to 100 GB — no Provisioned IOPS
• Network: Lab VPC
• Security group must allow LinuxServer access
• MySQL only: turn off Enhanced Monitoring
• On-Demand only

These limits keep costs in check — Provisioned IOPS and Multi-AZ are the fastest ways to blow up an RDS bill.

Noted the master username, password, and endpoint — needed next.

STEP 2: CONNECT TO THE LINUX SERVER
Downloaded the PEM key, grabbed the LinuxServer address, connected over SSH:

chmod 400 labsuser.pem
ssh -i labsuser.pem ec2-user@<LinuxServer-address>

This box is just the SQL client — it needs network access to RDS, nothing more.

STEP 3: INSTALL MYSQL CLIENT AND CONNECT
On the LinuxServer:

sudo yum install mysql -y

Connect using the master credentials from Step 1:

mysql -h <rds-endpoint> -u <master-username> -p

If it hangs, it's almost always the security group — check port 3306 inbound.

STEP 4: CREATE THE RESTART TABLE

CREATE DATABASE lab_db;
USE lab_db;

CREATE TABLE RESTART (
    StudentID INT,
    StudentName VARCHAR(100),
    RestartCity VARCHAR(100),
    GraduationDate DATETIME
);

STEP 5: INSERT 10 SAMPLE ROWS

INSERT INTO RESTART (StudentID, StudentName, RestartCity, GraduationDate) VALUES
(1, 'Ahmed Khan', 'Karachi', '2024-01-15 10:00:00'),
(2, 'Sara Ali', 'Lahore', '2024-02-10 11:30:00'),
(3, 'Bilal Hassan', 'Islamabad', '2024-03-05 09:15:00'),
(4, 'Ayesha Malik', 'Karachi', '2024-01-20 14:00:00'),
(5, 'Usman Tariq', 'Faisalabad', '2024-04-12 10:45:00'),
(6, 'Hina Shaikh', 'Karachi', '2024-02-28 13:30:00'),
(7, 'Omar Farooq', 'Multan', '2024-03-18 12:00:00'),
(8, 'Zara Iqbal', 'Lahore', '2024-01-30 15:20:00'),
(9, 'Danish Raza', 'Karachi', '2024-04-02 09:50:00'),
(10, 'Mahnoor Aslam', 'Islamabad', '2024-02-15 11:10:00');

STEP 6: SELECT ALL FROM RESTART

SELECT * FROM RESTART;

STEP 7: CREATE THE CLOUD_PRACTITIONER TABLE

CREATE TABLE CLOUD_PRACTITIONER (
    StudentID INT,
    CertificationDate DATETIME
);

STEP 8: INSERT 5 SAMPLE ROWS

INSERT INTO CLOUD_PRACTITIONER (StudentID, CertificationDate) VALUES
(1, '2024-05-01 10:00:00'),
(3, '2024-05-03 11:00:00'),
(5, '2024-05-05 09:30:00'),
(7, '2024-05-07 14:15:00'),
(9, '2024-05-09 10:20:00');

STEP 9: SELECT ALL FROM CLOUD_PRACTITIONER

SELECT * FROM CLOUD_PRACTITIONER;

STEP 10: INNER JOIN

SELECT R.StudentID, R.StudentName, C.CertificationDate
FROM RESTART R
INNER JOIN CLOUD_PRACTITIONER C
ON R.StudentID = C.StudentID;

Only students in both tables show up — filters out anyone not yet certified.

WHY THIS MATTERS

• RDS constraints = real cost guardrails, not busywork
• Security groups are the #1 connection blocker
• Base table + event table + join = same pattern used in production apps
• Always capture before/after proof at each step

TOOLS USED

• Amazon RDS (Aurora / MySQL)
• Amazon EC2
• Amazon VPC + Security Groups
• MySQL client / SQL

AWS #RDS #Database #MySQL #CloudComputing #SQL