Creating a web server and an Amazon RDS DB instance

In this tutorial, I will be creating an Amazon EC2 instance that uses a custom VPC, subnets, and security groups. The tutorial will show how to create the DB instance and automatically set up connectivity with the EC2 instance that you created. The tutorial also shows how to install the web server and a database client on the EC2 instance.

Create a Custom VPC

In this tutorial I will create a custom VPC using the wizard, but the other method would also be the same. When logged to the AWS account I will navigate to the VPC dashboard and select create VPC.

In the next interface I will select VPC and more and give you details. I will be using the CIDR block on 10.0.0.0/16 on the VPC.

I will be creating 4 subnets in two availability zones. Of the 4, 2 subnets will be public and the other 2 will be private. The subnets will be of the CIDR block of 10.0.0.0/24, 10.0.1.0/24, 10.0.2.0/24 and 10.0.3.0/24.

With the wizard, the route tables for both of my subnets and an internet gateway will be created and attached automatically.

With the wizard, the route tables for both of my subnets and an internet gateway will be created and attached automatically. After verifying the details are right, I will click on create VPC and the VPC will be created.

Launch an EC2 instance

Next, I will navigate to the EC2 dashboard and select launch an Amazon EC2 instance. The instance will be launched one of the public subnets of the custom VPC. I will be using Amazon Linux 2 Ami, t2.micro free tier instance type and a custom security group that will be allowing ssh and http traffic from all ipv4 Ip addresses.

Before launching the instance, I will launch the security group. Ensure you select the custom VPC created.

From the EC2 dashboard, click on launch instance

In the new interface, give the server a name, select Amazon Linux 2 for the AMI, select t2.micro for the instance type, select a key pair if you have any (If not you can create a new one). On the network setting, I will click edit to select the custom VPC created and select the public subnets. Also, I will enable auto-assign public Ip and on the security groups choose, select existing security group and in the drop down select the security group I created.

Then select launch instance and the instance will be launched.

Create a Database instance

In this step I will be creating a security group for the database that will only allow traffic from the webserver, a subnet group for the database and then the database instance.

In the EC2 or VPC dashboard, select security groups and click on create.

Here I will create a new security group, my-db-sg and the inbound rules will be allowing MYSQL/Aurora traffic from the webserver.

Click on create to create the security groups

On the search bar, I will search for RDS and click to go to the Amazon RDS dashboard. On the left, I will click on subnet group and click on create. I will ensure that I select the private subnet

Then click on create.

On the left again I will click on databases and select create database

In the new interface I will select MYSQL as the database engine

I will scroll down and select free tier then give my database instance identifier.

I will use admin for my master name and then give a password for the username.

On the connectivity I will not connect with an EC2 (But connecting with an EC2 would still work the same), I will then select my VPC and then the subnet group that I created.

On the security group I choose existing and select my DB security group

I will then scroll down to the additional details expand and give my database initial name as sample. Then untick the check boxes for backup, encryption and upgrades.

Then create on create.

Installing a web server on the EC2 instance

Here I will Install a web server on the EC2 instance that I launched earlier on. The web server will connect to the Amazon RDS DB instance.

I will start by connecting to the instance though ssh

ssh -i <key name> ec2-user@<instance ip>

I will update the instance to the latest bug fixes and security updates by running the following command

sudo yum update -y

The -y option installs the updates without asking for confirmation.

Next, I will install PHP and MariaDB software's using the amazon-linux-extras install command. This command will install multiple software packages and related dependencies at the same time.

sudo amazon-linux-extras install php8.0 mariadb10.5

After that I will install the Apache web server and start it. I will configure the web server to start with each system boot using the systemctl command.

sudo yum install -y httpd
sudo systemctl start httpd
sudo systemctl enable httpd

Connect the Apache web server to the DB instance

Here I will add content to the Apache web server that will connect to your Amazon RDS DB instance.

While still connected to the EC2 instance, I will change the directory to /var/www and create a new subdirectory named inc. Because I need elevated rights, I will switch to the root user

cd /var/www
sudo su
mkdir inc
cd inc

I will create a new file in the inc directory named dbinfo.inc, and then edit the file by calling nano

touch dbinfo.inc
nano dbinfo.inc

<?php

define('DB_SERVER', 'felix-db.cpvs7xtgksmh.us-east-1.rds.amazonaws.com');
define('DB_USERNAME', 'admin');
define('DB_PASSWORD', 'demo-password');
define('DB_DATABASE', 'sample');

?>

I will save and close the dbinfo.inc file. For nano I will do this by Ctrl+O and Ctrl+X.

Next I will change the directory to /var/www/html and create a new file in the html directory named SamplePage.php

cd /var/www/html
nano SamplePage.php

I will then add the following contents to the SamplePage.php file:

<?php include "../inc/dbinfo.inc"; ?>
<html>
<body>
<h1>Sample page</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();

  $database = mysqli_select_db($connection, DB_DATABASE);

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

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

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

<!-- Input form -->
<form action="<?PHP echo $_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. -->
<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");

while($query_data = mysqli_fetch_row($result)) {
  echo "<tr>";
  echo "<td>",$query_data[0], "</td>",
       "<td>",$query_data[1], "</td>",
       "<td>",$query_data[2], "</td>";
  echo "</tr>";
}
?>

</table>

<!-- Clean up. -->
<?php

  mysqli_free_result($result);
  mysqli_close($connection);

?>

</body>
</html>


<?php

/* Add an employee to the table. */
function AddEmployee($connection, $name, $address) {
   $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.</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.</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;
}
?>

then save and close

Next I will verify that the web server successfully connects to the DB instance by opening a web browser and browsing to http://EC2 instance endpoint/SamplePage.php

The above page should appear, and I can add records

To see the records in the database I will connect the database:

mysql -u admin -h felix-db.cpvs7xtgksmh.us-east-1.rds.amazonaws.com -p

then I will check the table records

To make sure that the DB instance is as secure as possible, sources outside of the VPC should not connect to the DB instance.

Thank you