AWS - Connecting to an RDS Instance from a Web server

Recently, I conducted a hands-on session on connecting to an AWS RDS instance remotely from a web server on an EC2 instance. I gained some valuable experience from this exercise. All the steps in this session were sourced from docs.aws.amazon.com.

So the following were the objectives of this hands-on;

1. Launch an EC2 instance to connect with your DB instance.

2. Create an Amazon RDS DB instance.

3. Install a web server on your EC2 instance.

4. Finally, connect to the DB instance from the web server.

The resources which comprise the EC2 instance and RDS DB instance will be created in the default VPC with the latter housed in a private subnet and the former, in a public subnet.

Step 1 - Launch an EC2 instance to connect with your DB instance. I launched a single Amazon Linux t2.micro instance from the AWS Management Console. I named it tutorial-ec2-instance-web-server. Next I created a key pair name which I named ec2-rds kp.pem which I would use in connecting to the instance. For the network settings; I would allow SSH traffic from My IP which is the public IP address which is available to me at that time(https://checkip.amazonaws.com).I would enable allow “HTTPs traffic from the internet" and “HTTP traffic from the internet” respectively.

After the instance is launched, I noted the identifier for the new EC2 instance from the Launch status page. From here, I could click to see more details about my running instance. Next I created the RDS DB instance.

Step 2 - Create an Amazon RDS DB instance.

In AWS, you can create an RDS DB instance for any of the following engines; MariaDB, MySQL or PostgreSQL. I choose MySQL DB due to my earlier familiar experience with it. As an overview, databases allows for the storing and querying of structured data. Amazon RDS is a relational database which relies on the use of SQL statements to interact with it.

So I signed in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/. After choosing Databases, Create Database and Standard Create, I chose MySQL as the preferred Engine.

In the next step, because this is a hands-on project, I chose the Free tier template.

Continuing, I kept the default settings in the Availability and Durability section. For the settings sections, I set the values; tutorial-db-instance and tutorial_user as the DB instance identifier and Master username respectively**. For** the password, I left the auto generate password field off and chose a secret value for the password.

Next, I kept the defaults for the Storage section and for the Connectivity section, I chose Connect to an EC2 compute resource for Compute resource. I then selected the EC2 instance (tutorial-ec2-instance-web-server) that was created in the earlier section.

In other to conclude this task, I followed up with the following;

(i) I selected the Password authentication option in the Database authentication section

(ii) In the Additional configuration section, I entered sample for the initial database name.

(iii) I then chose Create database to create the MySQL DB instance. The new DB instance would appear in the Databases list with the status Creating. Once the status changed to Available, I then chose the DB instance name to show its details.

(iv) These details would include the Endpoint and Port of the DB instance as can be seen in the Connectivity & security section.

Step 3 - Install a web server on your EC2 instance

In this final part, I installed an Apache web server with PHP. With guidance, I was then able to connect to the RDS DB instance and perform some HTML data input and updates. Because of the technical jargon involved, I will keep this brief but still clear.

The steps below explain this process;

1. Connecting to the EC2 instance - Remember in the first section, I allowed SSH traffic to the EC2 instance in the network settings. This would be useful for me at this point. The following mode shows how you can connect to an ec2 instance via SSH;

   »ssh -i location_of_pem_file ec2-user@ec2-instance-public-dns-name

   So using Command prompt from right inside Visual Code, I was able to seamlessly connect to my instance.

   

   2. Installing the webserver - So once connected to the EC2 instance, I was able to first update the software and then went on to install the Apache webserver, PHP, and MariaDB or PostgreSQL software using the following commands;

      »sudo dnf install -y httpd php php-mysqli mariadb105

      

      After the webserver had been installed, I then started it and then I tested by entering the public Domain Name System (DNS) name of the EC2 instance in the address bar of a web browser.

      »sudo systemctl start httpd

      

      Next, I set the file permissions for the Apache web server, but I won't share the details today to keep this presentation simple.

      In short, setting the right file permissions ensures that only the user (in this case, me) has access to the web server. This is important for security reasons.

   3. Connecting the Apache web server to the RDS DB Instance - To begin, I added content to the Apache web server that connects to the Amazon RDS DB instance. This consists of two parts; creating two directories in the Apache web server folder, one directory for creating a file for database connection and the other for creating a simple PHP file.

      »cd /var/www

      »mkdir inc

      »cd inc where /var/www is the Apache web server folder

      »dbinfo.inc

      »nano dbinfo.inc

      I then added the following declarations in the dbinfo.inc using Nano editor.

      <?php define('DB_SERVER', 'db_instance_endpoint'); define('DB_USERNAME', 'tutorial_user'); define('DB_PASSWORD', 'master password'); define('DB_DATABASE', 'sample');

      ?>

      The value for the DB_SERVER field was obtained earlier in this presentation when the RDS DB instance was successfully created. The other values for DB_USERNAME, DB_PASSWORD, and DB_DATABASE were chosen during the instance setup.This is what our web server would use to launch into the instance from the EC2 instance.

      Having saved this file, I changed directory to the html, created the new PHP file named mypage.php and added some contents to it using Nano editor.

      »cd /var/www/html

      »mypage.php

      »nano mypage.php

      The PHP script creates a simple web application that allows users to add employee records to a MySQL database and display the existing records. it is a quite a lengthy script and it would be quite wordy to display it in this presentation. However I will share snippets of it in a bit. In a nutshell, it does the following;

      (i) Database Connection: The script connects to a MySQL database using credentials defined in an included file (dbinfo.inc).

      »$connection = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD);

      (ii)HTML Form: It displays an HTML form where users can input employee names and addresses.

      (iii)Data Handling: Upon form submission, it processes the input and adds the new record to the EMPLOYEES table.

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

      (iv)Display Records: It then retrieves and displays all records from the EMPLOYEES table in a tabular format.

      »$result = mysqli_query($connection, "SELECT * FROM EMPLOYEES");

      For a well designed database, a clean up is necessary. So it should release all resources afterwards and close the connection;

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

To wrap up this hands-on session, I needed to verify that the web server successfully connected to the database instance by browsing to http://EC2instance endpoint/mypage.php http://ec2 instance public dns name/mypage.php on a browser.

This hands-on tutorial, although it may seem complicated, can actually be fun to follow. It consists of logical steps that demonstrate the computing capabilities of the cloud. It can how we can remotely administer a database instance living in the cloud from a server instance also in the cloud. So much can be achieved with dealing with any hardware resource.

In a future hands-on session, I will show how to use Amazon Lambda, a powerful serverless technology, to interact with an RDS DB instance.

Thank you for following.