1. Enterprise Tiered Architecture Overview

Running stateful database workloads alongside stateless web compute requires a clean separation of concerns. In traditional monolithic on-premises setups, Apache, PHP, and MySQL frequently run on the same physical host, creating single points of failure and resource contention during traffic spikes.

On Amazon Web Services (AWS), the modern standard decouples compute and storage into a Two-Tier Architecture:

• Web Compute Tier (EC2): Resides in a Public Subnet, running Apache HTTP Server and PHP to handle inbound client HTTP/HTTPS requests.
• Database Tier (Amazon RDS MySQL): Isolated inside a dedicated Private DB Subnet Group across multiple Availability Zones (Multi-AZ), completely protected from direct internet ingress.
• Monitoring & Failover Tier (Route 53): Performs automated synthetic health checks against public endpoints, tracking latency and server availability.

2. Least-Privilege VPC Security Group Architecture

Security Groups in AWS act as stateful virtual firewalls at the Elastic Network Interface (ENI) level. To ensure zero-trust compliance, the database must never accept connections from the public internet (0.0.0.0/0). Instead, configure Security Group Referencing, where the RDS firewall explicitly permits port 3306 inbound traffic solely from the EC2 instance's Security Group ID.

a. Web Server Security Group (sg-webserver-prod)

b. Database Security Group (sg-rds-prod)

Security Warning: Never open Port 3306 to 0.0.0.0/0. Doing so exposes your database to brute-force credential stuffing and ransomware attacks across public scanning networks. Additionally, RDS does not support direct host SSH access, so opening Port 22 on an RDS security group is redundant and flagged in cloud audits.

3. Provisioning Amazon RDS MySQL Engine

Amazon Relational Database Service (RDS) automates provisioning, patch management, automated backups, and point-in-time recovery.

1. Navigate to the Amazon RDS Console and select Create database.
2. Database Engine: Select MySQL (Engine Version: MySQL 8.0.x recommended).
3. Templates: Select Free Tier (or Production for Multi-AZ deployments).
4. Settings:
   • DB Instance Identifier: production-mysql-db
   • Master Username: dbadmin
   • Credentials: Enable AWS Secrets Manager or generate a cryptographically strong master password.
5. Instance Class: Burstable classes (e.g., db.t3.micro for development, db.m6i.large for production).
6. Storage: General Purpose SSD (gp3) with storage autoscaling enabled.
7. Connectivity:
   • Virtual Private Cloud (VPC): Select your target VPC.
   • Subnet Group: Associate with a private DB subnet group spanning at least two Availability Zones.
   • Public Access: Select No (enforcing private internal routing).
   • VPC Security Group: Select sg-rds-prod.

Once the database status turns to Available, copy the generated Database Endpoint (e.g., production-mysql-db.c123456789.us-east-1.rds.amazonaws.com).

4. Launching & Hardening the EC2 Web Server

Deploy an Amazon Linux 2023 or Amazon Linux 2 instance into the public subnet of the same VPC:

1. Launch an EC2 instance named app-web-server using a t3.micro instance class.
2. Associate the instance with the sg-webserver-prod security group.
3. Attach an IAM Instance Profile granting access to AWS Systems Manager (SSM) for session management without exposing Port 22 publicly.
4. Connect to your instance via SSH or AWS SSM Session Manager and execute the setup commands below:

# 1. Update system operating system packages
sudo dnf update -y

# 2. Install Apache HTTP Server, PHP 8.2, and MySQL extensions
sudo dnf install -y httpd php php-mysqli php-json php-common

# 3. Start and enable Apache service to persist across reboots
sudo systemctl start httpd
sudo systemctl enable httpd

# 4. Configure group permissions for web directories
sudo usermod -a -G apache ec2-user
sudo chown -R ec2-user:apache /var/www
sudo chmod 2775 /var/www
find /var/www -type d -exec sudo chmod 2775 {} +
find /var/www -type f -exec sudo chmod 0664 {} +

# 5. Create configuration folder for protected credentials
mkdir -p /var/www/inc
chmod 700 /var/www/inc

5. Secure Application Integration & Database Handshake

Store database connection parameters outside of the public document root (/var/www/html) so raw credentials are not exposed if the PHP execution engine fails:

Database Configuration (/var/www/inc/dbinfo.inc)

<?php
// Database connection configuration
define('DB_SERVER', 'production-mysql-db.c123456789.us-east-1.rds.amazonaws.com');
define('DB_USERNAME', 'dbadmin');
define('DB_PASSWORD', 'YourProductionSecurePassword123!');
define('DB_DATABASE', 'appproduction');
define('DB_PORT', 3306);
?>

Application Logic & Health Probe (/var/www/html/app.php)

<?php 
include "/var/www/inc/dbinfo.inc"; 

// Initialize MySQL connection with SSL options
$connection = mysqli_init();
if (!$connection) {
    die("mysqli_init failed");
}

// Establish database connection
if (!mysqli_real_connect($connection, DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT)) {
    http_response_code(500);
    echo "
Database Connection Error
";
    echo "<p>Error: " . htmlspecialchars(mysqli_connect_error()) . "</p>";
    exit();
}

// Ensure the schema table exists
VerifyEmployeesTable($connection, DB_DATABASE);

// Handle POST submissions safely
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $employee_name = trim($_POST['NAME'] ?? '');
    $employee_address = trim($_POST['ADDRESS'] ?? '');

    if (!empty($employee_name) && !empty($employee_address)) {
        AddEmployee($connection, $employee_name, $employee_address);
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>AWS Enterprise Portal</title>
    <style>
        body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif; margin: 40px; }
        table { border-collapse: collapse; width: 100%; margin-top: 20px; }
        th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
        th { background-color: #f4f4f4; }
        .form-group { margin-bottom: 15px; }
    </style>
</head>
<body>
    <h1>Enterprise Employee Portal (EC2 to RDS)</h1>
    
    <form action="<?php echo htmlspecialchars($_SERVER['SCRIPT_NAME']); ?>" method="POST">
        <div class="form-group">
            <label>Name: </label>
            <input type="text" name="NAME" required maxlength="45">
        </div>
        <div class="form-group">
            <label>Address: </label>
            <input type="text" name="ADDRESS" required maxlength="90">
        </div>
        <button type="submit">Save Record</button>
    </form>

    <h2>Registered Records</h2>
    <table>
        <tr><th>ID</th><th>Name</th><th>Address</th></tr>
        <?php
        $result = mysqli_query($connection, "SELECT ID, NAME, ADDRESS FROM EMPLOYEES");
        while($row = mysqli_fetch_assoc($result)) {
            echo "<tr>";
            echo "<td>" . htmlspecialchars($row['ID']) . "</td>";
            echo "<td>" . htmlspecialchars($row['NAME']) . "</td>";
            echo "<td>" . htmlspecialchars($row['ADDRESS']) . "</td>";
            echo "</tr>";
        }
        mysqli_free_result($result);
        mysqli_close($connection);
        ?>
    </table>
</body>
</html>
<?php
function AddEmployee($connection, $name, $address) {
    $stmt = mysqli_prepare($connection, "INSERT INTO EMPLOYEES (NAME, ADDRESS) VALUES (?, ?)");
    mysqli_stmt_bind_param($stmt, "ss", $name, $address);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}

function VerifyEmployeesTable($connection, $dbName) {
    $query = "CREATE TABLE IF NOT EXISTS EMPLOYEES (
        ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        NAME VARCHAR(45) NOT NULL,
        ADDRESS VARCHAR(90) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    mysqli_query($connection, $query);
}
?>

6. Proactive Endpoint Monitoring with Amazon Route 53

High availability requires external synthetic monitoring to detect outages before customers report them. Amazon Route 53 Health Checks continuously send DNS and HTTP requests from global edge locations to evaluate web server responsiveness.

Step-by-Step Health Check Setup

1. Open the Amazon Route 53 Console and select Health checks from the left navigation panel.
2. Click Create health check and specify the parameters:
   • Name: production-webserver-healthcheck
   • What to monitor: Endpoint
   • Monitor endpoint by: IP address or Domain Name (select Domain Name if utilizing Route 53 Hosted Zones).
   • Protocol: HTTP (or HTTPS on Port 443).
   • Path: /app.php
3. Advanced Configuration:
   • Request Interval: Standard (30 seconds) or Fast (10 seconds).
   • Failure Threshold: 3 consecutive failed probes before flagging the endpoint as Unhealthy.
4. Alarm Notification: Integrate with Amazon CloudWatch & SNS to automatically dispatch an SMS or Slack notification to the platform engineering on-call team upon status changes.

7. Production Pitfalls & Troubleshooting Guide

When deploying multi-tier architectures on AWS, engineers regularly encounter these network and authentication pitfalls:

1. Database Connection Timeout (Error 2003 / HY000)

The PHP application hangs and outputs: Can't connect to MySQL server on '...' (110 "Connection timed out").

• Root Cause: The RDS Security Group does not have an inbound rule allowing TCP traffic on Port 3306 from the EC2 instance's Security Group, or the instances reside in different VPCs without VPC Peering.
• Remedy: Verify the RDS Security Group has an inbound rule referencing sg-webserver-prod as its source, and test network connectivity from the EC2 terminal using netcat:

  nc -zv production-mysql-db.c123456789.us-east-1.rds.amazonaws.com 3306

2. SQL Injection Vulnerabilities in Legacy PHP Code

Using raw string concatenation like mysqli_query("INSERT INTO ... '$name'") leaves your database vulnerable to SQL injection attacks even if mysqli_real_escape_string is used.

Remedy: Always enforce Prepared Statements (mysqli_prepare and mysqli_stmt_bind_param) as demonstrated in Section 5.

3. Subnet Group Availability Zone Mismatches

If your EC2 instance is located in us-east-1a and the primary RDS instance is launched in a private subnet in us-east-1b without proper cross-AZ routing tables, connectivity fails. Always confirm the DB Subnet Group contains subnets matching all Availability Zones utilized by your compute tier.