Overview

In this guide, you’ll learn how to set up an Amazon Relational Database Service (RDS) MySQL instance with high availability (Multi-AZ) and connect it to a web application running on an EC2 instance. This setup ensures your database is reliable, scalable, and secure while allowing your web app to interact with it seamlessly. Whether you’re building a small app or preparing for production workloads, this lab provides foundational AWS skills with practical, real-world applications.

Why this matters: Amazon RDS simplifies database management by automating backups, patching, and replication, freeing you to focus on your application. By the end, you’ll have a fully functional database and a web app interacting with it—plus the know-how to troubleshoot and scale.

---

Prerequisites

Before starting, ensure you have:

• An AWS Account: Sign up at aws.amazon.com if you don’t have one.
• Basic AWS Knowledge: Familiarity with the AWS Management Console and EC2 is helpful but not required.
• Tools:
  • AWS CLI (optional, for command-line enthusiasts).
  • Terraform (optional, for Infrastructure as Code).
  • A text editor (e.g., Notepad or VS Code) to store values like endpoints.
• Permissions: IAM role or user with access to RDS, VPC, EC2, and Security Groups.

---

Step-by-Step Guide

Step 1: Set Up a Security Group for RDS

What: A security group acts as a virtual firewall to control traffic to your RDS instance.

Why: This ensures only your web server (EC2 instance) can access the database, enhancing security.

1. Log in to the AWS Management Console:
   • Navigate to Services > VPC > Security Groups.
2. Create a Security Group:
   • Click Create Security Group.
   • Name: DB-Security-Group.
   • Description: “Allows web server access to RDS”.
   • VPC: Select your default VPC (or the lab-provided VPC).
3. Add an Inbound Rule:
   • Click Add Rule.
   • Type: MySQL/Aurora (3306).
   • Source: Search for your web server’s security group (e.g., Web-Security-Group) or use 0.0.0.0/0 for testing (not recommended for production).
   • Click Create Security Group.

CLI Option:

bash

aws ec2 create-security-group –group-name DB-Security-Group –description “Allows web server access to RDS” –vpc-id <your-vpc-id>

aws ec2 authorize-security-group-ingress –group-id <db-sg-id> –protocol tcp –port 3306 –source-group <web-sg-id>

Step 2: Create a DB Subnet Group

What: A DB subnet group defines which subnets RDS can use across Availability Zones (AZs).

Why: Multi-AZ deployments require subnets in at least two AZs for high availability.

1. Navigate to RDS:
   • Go to Services > RDS > Subnet Groups.
2. Create a Subnet Group:
   • Click Create DB Subnet Group.
   • Name: DB-Subnet-Group.
   • Description: “Subnets for RDS Multi-AZ”.
   • VPC: Select your VPC.
3. Add Subnets:
   • Choose two Availability Zones (e.g., us-east-1a and us-east-1b).
   • Add one subnet per AZ (e.g., 10.0.1.0/24 and 10.0.3.0/24).
   • Click Create.

Terraform Option:

hcl

resource “aws_db_subnet_group” “db_subnet_group” {

  name       = “db-subnet-group”

  subnet_ids = [“subnet-12345678”, “subnet-87654321”]

}

Step 3: Launch an RDS MySQL Instance

What: Deploy a Multi-AZ MySQL database instance.

Why: Multi-AZ ensures your database remains available during failures by replicating data to a standby instance.

1. Start the Creation Process:
   • In RDS, click Databases > Create Database > Standard Create.
   • Engine: MySQL.
   • Version: Latest (e.g., 8.0.x).
   • Template: Dev/Test.
2. Configure Settings:
   • DB Instance Identifier: my-rds-db.
   • Master Username: admin.
   • Master Password: SecurePass123! (use a strong password in production).
3. Instance and Storage:
   • Class: db.t3.medium (burstable for cost-efficiency).
   • Storage: 20 GB, General Purpose SSD.
4. Connectivity:
   • VPC: Your VPC.
   • Subnet Group: DB-Subnet-Group.
   • Security Group: DB-Security-Group.
5. Additional Settings:
   • Database Name: mydb.
   • Backups: Enable (optional for production; disable for faster lab setup).
   • Click Create Database.
6. Wait and Note the Endpoint:
   • Wait 5-10 minutes for the status to become Available.
   • Copy the Endpoint (e.g., my-rds-db.abcd1234.us-east-1.rds.amazonaws.com).

CLI Option:

bash

aws rds create-db-instance –db-instance-identifier my-rds-db –db-instance-class db.t3.medium –engine mysql –master-username admin –master-user-password SecurePass123! –allocated-storage 20 –multi-az –db-subnet-group-name DB-Subnet-Group –vpc-security-group-ids <db-sg-id>

Step 4: Connect a Web Application to RDS

What: Link an EC2-hosted web app to your RDS instance.

Why: This demonstrates how applications interact with a managed database.

1. Locate Your Web Server:
   • Find your EC2 instance’s public IP (e.g., via EC2 > Instances).
2. Access the Web App:
   • Open a browser and enter http://<ec2-public-ip>.
3. Configure the Database Connection:
   • In the app’s interface, input:
     • Endpoint: Your RDS endpoint.
     • Database: mydb.
     • Username: admin.
     • Password: SecurePass123!.
   • Submit the form.
4. Test the App:
   • Add, edit, or delete entries to confirm the app is reading/writing to RDS.

Code Snippet (Example PHP):

php

$conn = new mysqli(“my-rds-db.abcd1234.us-east-1.rds.amazonaws.com”, “admin”, “SecurePass123!”, “mydb”);

if ($conn->connect_error) {

    die(“Connection failed: ” . $conn->connect_error);

}

---

Real-World Use Cases

1. E-Commerce Platforms:
   • Use this setup to store product catalogs and customer data, with Multi-AZ ensuring uptime during peak shopping seasons.
2. Content Management Systems (CMS):
   • Power a blog or news site, scaling RDS storage as content grows.
3. Internal Tools:
   • Build employee dashboards with secure database access, modifying the security group for additional services like Lambda.

Extending the Setup:

• Add read replicas for analytics workloads.
• Integrate with AWS Lambda for serverless app logic.
• Use Elastic Load Balancer with Auto Scaling for the web tier.

---

Troubleshooting

1. “Access Denied” When Starting Lab:
   • Cause: Browser popup blocker or IAM permissions.
   • Solution: Allow popups or verify your IAM role has RDS/EC2 access.
2. RDS Instance Not Accessible:
   • Cause: Security group misconfiguration.
   • Solution: Ensure port 3306 is open and the source matches your EC2’s security group. Check with:
     bash
     aws ec2 describe-security-groups –group-ids <db-sg-id>
     
3. Web App Fails to Connect:
   • Cause: Wrong endpoint or credentials.
   • Solution: Double-check the endpoint and credentials in the RDS console. Test connectivity with:
     bash
     mysql -h <rds-endpoint> -u admin -p
     
4. Slow Database Performance:
   • Cause: Insufficient instance size or missing indexes.
   • Solution: Upgrade to a larger instance (e.g., db.m5.large) or use CloudWatch to monitor CPU/memory.

Debugging Tip: Enable CloudWatch Logs for RDS to track queries and errors.

---

Conclusion & Best Practices

You’ve successfully launched a Multi-AZ RDS MySQL instance and connected it to a web app! Key takeaways:

• Security: Always restrict security group access to specific sources.
• Scalability: Use Multi-AZ for reliability and scale storage as needed.
• Efficiency: Automate with Terraform or CLI for repeatable setups.

Next Steps: Explore automated backups, encryption at rest, and integrating with AWS Secrets Manager for credential management. Happy building!