AWS RDS + EC2 + MySQL Setup Guide (Step-by-Step)

1. Create Amazon RDS Database

Step 1: Open RDS Console

• Go to AWS Management Console
• Search for RDS
• Click Create database

Step 2: Choose Database Engine

• Select Amazon Aurora or MySQL
• Choose MySQL (Free Tier)

Step 3: Database Configuration

• DB identifier: nishan-db (or any name)
• Master username: admin
• Master password: Create a strong password

Step 4: Instance Settings

• Choose Free Tier eligible template
• DB instance class: db.t3.micro (if available)

Step 5: Storage

• Storage: default or 20–30 GB

Step 6: Connectivity

• VPC: Default VPC
• Public access: Yes (for testing)
• Security group: Create new or use default wizard

Step 7: Create Database

• Click Create database
• Wait until status becomes Available

2. Create EC2 Instance

Step 1: Launch EC2

• Go to AWS EC2 Dashboard
• Click Launch Instance

Step 2: Configure Instance

• Name: ec2-rds-client
• AMI: Amazon Linux 2023
• Instance type: t2.micro (Free Tier)

Step 3: Key Pair

• Select existing key or create new key pair

Step 4: Network Settings

• VPC: Default VPC
• Subnet: Choose any default subnet
• Auto-assign public IP: Enable
• Security Group: Use wizard or default (allow SSH, MySQL if needed)

Step 5: Storage

• Set storage to 30 GB (as required)

Step 6: Launch Instance

• Click Launch Instance

3. Connect to EC2 Instance

Use SSH:

ssh -i your-key.pem ec2-user@your-ec2-public-ip

4. Install MySQL Client in EC2

Update system:

sudo dnf update -y

Install MariaDB/MySQL client:

sudo dnf install mariadb105 -y

Check version:

mysql --version

5. Connect EC2 to RDS Database

Step 1: Get RDS Endpoint

• Go to RDS Console
• Click your database
• Copy Endpoint URL

Example:

database-nishan.c0b8ykiu2ebv.us-east-1.rds.amazonaws.com

Step 2: Connect using MySQL client

Run this in EC2 terminal:

mysql -h database-nishan.c0b8ykiu2ebv.us-east-1.rds.amazonaws.com -u admin -p

• Enter password when prompted

6. MySQL Operations

Check Databases

show databases;

Create Database

create database nishan_db;

Use Database

use nishan_db;

7. Create Student Table

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    date_of_birth DATE,
    gender ENUM('Male', 'Female', 'Other'),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

8. Insert Data into Table

INSERT INTO student (first_name, last_name, email, phone, date_of_birth, gender, address)
VALUES 
('Ram', 'Sharma', 'ram@gmail.com', '9812345678', '2002-05-10', 'Male', 'Kathmandu'),
('Sita', 'Koirala', 'sita@gmail.com', '9823456789', '2001-08-15', 'Female', 'Pokhara');

9. Verify Table

Show Tables

show tables;