How to Deploy and Monitor a MySQL Database on AWS

Security is paramount. We start by defining security groups that control inbound and outbound traffic for our EC2 instances and our RDS (Relational Database Service) MySQL instance. Proper configuration here is critical to protect your resources from unauthorized access.

a. Security Group for EC2 Instance: `rdsinstanceSG-1`

This security group will allow necessary traffic for your web server to function and for you to connect via SSH. Ensure these rules align with the minimum necessary access for your application.

Note on Security: For production environments, it's highly recommended to restrict 0.0.0.0/0 sources to specific IP addresses or other security groups for enhanced security. For SSH, limit access to your known IP. For HTTP/HTTPS, consider using a load balancer's security group instead of public access directly to the EC2 instance.

b. Security Group for RDS (MySQL): `rdssecurity`

This security group will specifically allow your EC2 instance (and potentially other authorized services) to connect to the MySQL database. It's crucial to be very restrictive with database access.

Best Practice: The most secure way to allow your EC2 instance to connect to RDS is by referencing the **security group ID of your EC2 instance** as the source for the RDS security group's ingress rule on port 3306. Avoid `0.0.0.0/0` for database access in any non-development environment.

Now, let's set up our managed MySQL database instance using AWS RDS. RDS handles routine database tasks like backups, patching, and scaling, freeing you to focus on your application.

1. Choose "Standard Create": This option provides granular control over your database configuration, suitable for most use cases.
2. Engine Option: Select "MYSQL" as the database engine.
3. Engine Version: Choose "MYSQL 5.7.33" or a more recent stable version that meets your application's requirements. AWS regularly updates supported versions.
4. Templates: Opt for "Free tier" for cost-effective learning and development. This template applies eligible free tier usage limits.
5. Instance Configuration: Select "Burstable classes (include 't' classes)" like db.t2.micro or db.t3.micro. These are suitable for workloads with moderate usage.
6. Settings: Define your database's core identity and credentials.
   • DB instance identifier: Provide a unique and meaningful name (e.g., project-web-db).
   • Master Username: Set a strong username (e.g., admin).
   • Master Password: Create a strong, complex password.
   • Confirm Password: Re-enter your master password for verification.
7. Storage Type: Select "General Purpose (SSD)" (gp2 or gp3). This offers a balance of price and performance for most applications.
8. Connectivity: Configure how your EC2 instance will communicate with the database.
   • Compute resources: Choose "Don’t connect to EC2 compute resources" for now. We'll establish the connection manually from our EC2 instance later.
   • Network type: Select IPv4.
   • VPC: Select your default VPC. Ensure both your EC2 instance and RDS instance are in the same VPC for easier private connectivity.
   • DB subnet Group: Select Default. This will ensure your RDS instance is provisioned across multiple Availability Zones for high availability.
   • Public access: Choose Yes for ease of testing and learning. **Crucially, for production, set this to `No`** and use private subnets or a bastion host for secure access.
   • VPC security group: Select your existing rdssecurity security group that you created in Step 1b.
   • Availability Zone: "No preference" is fine for single-AZ deployments, but for high availability, consider selecting a specific AZ.
9. Leave the rest of the options unchecked and proceed to create the database instance.

Upon creation, allow some time for the database instance to provision. Once available, note down your database **ENDPOINT**. This will look something like: cloudsinfo123.cnodczyuplp.us-east-1.rds.amazonaws.com. This endpoint is crucial for your web server to connect to the database.

Next, we'll launch an EC2 instance to serve as our web server. This instance will host your PHP application and interact with the MySQL database.

1. Go to the EC2 console and click "Launch instance".
2. Name: Give your web server a meaningful name, e.g., project-webserver.
3. Amazon Machine Image (AMI): Choose a suitable Free tier AMI, such as "Amazon Linux 2 AMI (HVM) - Kernel 5.10, SSD Volume Type" (e.g., ami-0abcdef1234567890).
4. Instance type: Select a free tier eligible type, such as `t2.micro`.
5. Key pair (login): Create a new key pair (e.g., `webserver-keypair.pem`) and download it. This is crucial for securely connecting to your EC2 instance via SSH. Convert it to `.ppk` if using PuTTY.
6. Network settings: Configure the network access for your web server.
   • VPC: Select your Default VPC. This should be the same VPC as your RDS instance.
   • Subnet: Choose a public subnet within your VPC.
   • Auto-assign Public IP: Enable. This assigns a public IPv4 address, allowing internet access.
   • Security groups: Select your existing rdsinstanceSG-1 security group, which you created in Step 1a.
7. Configure storage: Use the default settings (e.g., 8 GB General Purpose SSD) or adjust as needed for your application's storage requirements.
8. Review your configurations and then click "Launch instance".

Connecting to your EC2 Instance using PuTTY:

Once your EC2 instance is running, you can connect to it via SSH using the key pair you downloaded.

1. Open PuTTY.
2. In the "Session" category, enter the **Host Name**: ec2-user@YOUR_EC2_PUBLIC_IP (e.g., ec2-user@3.83.55.14). You can find your EC2 instance's Public IP in the EC2 console.
3. Navigate to Connection > SSH > Auth in the PuTTY left-hand menu. Click "Browse" and select your downloaded `.ppk` key pair file (you might need to convert the `.pem` file to `.ppk` using PuTTYgen).
4. Click "Open," accept any security alerts about the host key, and you should be logged into your EC2 instance's command line.

With your EC2 instance accessible, let's install the necessary software to run your PHP web application and connect it to your MySQL database.

Install LAMP Stack Components:

Run the following commands in your EC2 instance's SSH terminal:

sudo yum update -y
sudo amazon-linux-extras install -y lamp-mariadb10.2-php7.2 php7.2
sudo yum install -y httpd
sudo systemctl start httpd
sudo systemctl enable httpd
sudo groupadd www
sudo usermod -a -G www ec2-user
exit # Reconnect after this to apply group changes
# After reconnecting to the instance, verify group membership:
groups
# Set permissions for the web root directory
sudo chgrp -R www /var/www
sudo chmod 2775 /var/www
sudo find /var/www -type d -exec sudo chmod 2775 {} +
sudo find /var/www -type f -exec sudo chmod g+w {} +
                        

These commands update packages, install Apache (httpd), PHP, and MariaDB client libraries (part of lamp-mariadb10.2-php7.2), start and enable Apache, and configure file permissions for the web server user (`ec2-user`) to write to the web root (`/var/www`). Reconnecting ensures the `ec2-user` group membership is active.

Connecting Apache to your DB instance:

To secure your database credentials and make them easily accessible to your PHP application, we'll create a configuration file outside the publicly accessible web root.

First, create the directory and file for database connection info:

cd /var/www
mkdir inc
cd inc
nano dbinfo.inc
                        

Add the following content to `dbinfo.inc`. **Replace the placeholders** with your RDS Endpoint, Master Username, Master Password, and the specific database name you want to use (you might need to create this database within MySQL later or use a default like `mysql` for initial connection tests).

<?php
define('DB_SERVER', 'YOUR_RDS_ENDPOINT'); // TODO: Replace with your RDS Endpoint (e.g., cloudsinfo123.cnodczyuplp.us-east-1.rds.amazonaws.com)
define('DB_USERNAME', 'YOUR_MASTER_USERNAME'); // TODO: Replace with your Master Username
define('DB_PASSWORD', 'YOUR_MASTER_PASSWORD'); // TODO: Replace with your Master Password
define('DB_DATABASE', 'YOUR_DATABASE_NAME'); // TODO: Replace with your desired database name (e.g., `webdb`)
?>
                        

Save and exit Nano (Press `Ctrl+X`, then `Y` to confirm changes, then `Enter` to save the file).

Next, create the main PHP application file (`Projectsubmit.php`). This file will contain the logic for connecting to the database, displaying data, and handling form submissions.

cd /var/www/html
nano Projectsubmit.php
                        

Add the following PHP code to `Projectsubmit.php`. This simple application demonstrates how to connect to the database, verify a table exists (and create it if not), and allow users to add and view employee data.

<?php include "../inc/dbinfo.inc"; ?>
<html><body>
<h1>AWS Solution Project Submission</h1>
<?php
/* Connect to MySQL and select the database. */
$connection = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD);
if (mysqli_connect_errno ()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error ();
    exit(); // Exit if connection fails
}
$database = mysqli_select_db ($connection, DB_DATABASE);
if (!$database) {
    echo "Failed to select database: " . mysqli_error($connection);
    exit(); // Exit if database selection fails
}

/* Ensure that the EMPLOYEES table exists. */
VerifyEmployeesTable ($connection, DB_DATABASE);

/* If input fields are populated, add a row to the EMPLOYEES table. */
$employee_name = isset($_POST['NAME']) ? htmlentities($_POST['NAME']) : '';
$employee_address = isset($_POST['ADDRESS']) ? htmlentities($_POST['ADDRESS']) : '';

if (strlen($employee_name) > 0 || strlen($employee_address) > 0) {
    AddEmployee($connection, $employee_name, $employee_address);
}
?>

<!-- Input form -->
<form action="<?PHP echo htmlspecialchars($_SERVER['SCRIPT_NAME']); ?>" method="POST">
<table border="0">
<tr><td>NAME</td><td>ADDRESS</td></tr>
<tr>
<td><input type="text" name="NAME" maxlength="45" size="30" /></td>
<td><input type="text" name="ADDRESS" maxlength="90" size="60" /></td>
<td><input type="submit" value="Add Data" /></td>
</tr>
</table>
</form>

<!-- Display table data. -->
<h2>Employee Data</h2>
<table border="1" cellpadding="2" cellspacing="2">
<tr><td>ID</td><td>NAME</td><td>ADDRESS</td></tr>
<?php
$result = mysqli_query($connection, "SELECT * FROM EMPLOYEES");
if ($result) {
    while($query_data = mysqli_fetch_row($result)) {
        echo "<tr>";
        echo "<td>", htmlspecialchars($query_data[0]), "</td>",
             "<td>", htmlspecialchars($query_data[1]), "</td>",
             "<td>", htmlspecialchars($query_data[2]), "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='3'>Error retrieving data: " . htmlspecialchars(mysqli_error($connection)) . "</td></tr>";
}
?>
</table>

<!-- Clean up. -->
<?php
if ($result) {
    mysqli_free_result($result);
}
mysqli_close($connection);
?>

</body></html>

<?php
/* Add an employee to the table. */
function AddEmployee($connection, $name, $address) {
    // Sanitize inputs to prevent SQL injection
    $n = mysqli_real_escape_string($connection, $name);
    $a = mysqli_real_escape_string($connection, $address);
    $query = "INSERT INTO EMPLOYEES (NAME, ADDRESS) VALUES ('$n', '$a');";
    if(!mysqli_query($connection, $query)) {
        echo("<p>Error adding employee data: " . htmlspecialchars(mysqli_error($connection)) . "</p>");
    } else {
        echo("<p>Employee added successfully!</p>");
    }
}

/* Check whether the table exists and, if not, create it. */
function VerifyEmployeesTable($connection, $dbName) {
    if(!TableExists("EMPLOYEES", $connection, $dbName)) {
        $query = "CREATE TABLE EMPLOYEES (
            ID int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            NAME VARCHAR(45),
            ADDRESS VARCHAR(90)
        )";
        if(!mysqli_query($connection, $query)) {
            echo("<p>Error creating table: " . htmlspecialchars(mysqli_error($connection)) . "</p>");
        } else {
            echo("<p>Table 'EMPLOYEES' created successfully.</p>");
        }
    }
}

/* Check for the existence of a table. */
function TableExists($tableName, $connection, $dbName) {
    $t = mysqli_real_escape_string($connection, $tableName);
    $d = mysqli_real_escape_string($connection, $dbName);
    $checktable = mysqli_query($connection,
        "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME = '$t' AND TABLE_SCHEMA = '$d'");
    if(mysqli_num_rows($checktable) > 0) return true;
    return false;
}
?>
                        

Save and exit Nano (Ctrl+X, Y, Enter).

Now, you can access your web application by navigating to your EC2 instance's **Public IP address** followed by /Projectsubmit.php in your web browser. For example: http://ec2-3-83-55-14.compute-1.amazonaws.com/Projectsubmit.php (replace with your actual EC2 Public IP or Public DNS). This page will allow you to add data to your MySQL database and display it.

**Important Security Note on PHP Code:** The provided PHP code is for demonstration purposes. For production environments, consider using **Prepared Statements** with PDO or MySQLi to prevent SQL injection vulnerabilities more robustly. Also, ensure error logging is properly configured and errors are not displayed directly to users.

Our core expertise lies in designing, building, and managing robust cloud solutions. We specialize in AWS solutions architecture, ensuring applications are secure, scalable, and cost-effective, with a strong focus on both AWS SysOps and AWS developer practices. Our proficiency extends to Azure, where we are certified in administration (AZ-104), development (AZ-204), and architecting complex solutions (AZ-305). To streamline the software delivery process, we leverage key DevOps tools like Jenkins for CI/CD, Terraform for Infrastructure as Code, and Ansible for automation. We are also experts in Docker for containerization and Kubernetes for orchestrating these containerized applications, enabling us to build highly resilient and efficient systems.

Join our community and stay up-to-date with the latest in cloud architecture and DevOps. Subscribe to our newsletter to receive our newest articles and insights directly in your inbox.

Subscribe