Configure and
connect a MySQL
Database
Instance with a
Web Server
Description
You are working as a database administrator for an IT firm. You have been
asked to create a new database instance on AWS cloud and connect it with
the employee management portal hosted on a web server.
Login to Amazon Console and Search for EC2 and Click on it
2
You will be navigated to EC2 Dashboard
Navigate to Security groups and Create Security groups, edit the required
ones as per below screenshots
3
4
Click on Create Security group. You will see the below screen
Here, Click on Edit Inbound rules and update with HTTP and SSH Ports
through Add rule button as below and Save the rules
5
Let’s create a Security group for MySQL Database now in the same way, but
restrict the access to database only from WebApp Security group and save
6
Now Let’s create MySQL RDS Instance. On the top search bar if you search for
RDS. You will see the below RDS dashboard. Click on create database button
7
Select MySQL version latest, Free tier template, Name the DB instance,
Provide Username and Password. Set all the details as below.
8
9
Finally Click on Create database. Once the database is created, you will see
the screenshot below
10
Navigate back to EC2 dashboard through Search bar at the top and Click on
Instances link. Let’s create and EC2 Instance Following the steps below
11
Click on Launch Instance button and follow further as below
Click on Create New Key Pair link To access the instance later
12
Enter the key pair name and Click Create Key pair. This will download a pem
file
13
You will see the keypair getting selected and further select the security group
we created for EC2 and leave the rest as defaults and click Launch instance
14
Now our EC2 Instance has been created. Let’s configure the WebApp on the
Instance and connect to MySQL Database
15
Copy the public IPv4 DNS and connect to the machine using your Linux
terminal using the pem file we downloaded earlier.
E.g.: ssh -i webappserver.pem ec2-user@ec2-3-91-239-15.compute-
1.amazonaws.com
Accept the fingerprint prompt and you will be logging in to the EC2 Instance.
Install an Apache web server with PHP
1. Follow below steps/commands to complete the process
sudo yum update -y
sudo amazon-linux-extras install -y lamp-mariadb10.2-php7.2 php7.2
sudo yum install -y httpd
16
sudo systemctl start httpd
sudo systemctl enable httpd
2. We can test our web server is properly installed and started.
To do this, enter the public Domain Name System (DNS) name of your
EC2 instance in the address bar of a web browser, for example:
http://ec2-user@ec2-3-91-239-15.compute-1.amazonaws.com.
If your web server is running, then you see the Apache test page.
To allow ec2-user to manage files in the default root directory for your
Apache web server, modify the ownership and permissions of the
/var/www directory. We will add ec2-user to the Apache group, to give
the Apache group ownership of the /var/www directory and assign write
permissions to the group.
To set file permissions for the Apache web server, run the below
commands
sudo usermod -a -G apache ec2-user
sudo chown -R ec2-user:apache /var/www
sudo chmod 2775 /var/www
We are ready to use the folders now.
17
Connect Apache web server to DB instance
We can now add content to our Apache web server that connects to our
Amazon RDS MySQL DB instance.
Follow these steps:
Create new directory inside var/www as webappdb
mkdir webappdb
cd webappdb
Now create new file using
vi dbinfo.inc
Content for dbinfo.inc
<?php
define('DB_SERVER','webappdb.colzicsrustq.us-east-1.rds.amazonaws.com');
define('DB_USERNAME', 'admin');
define('DB_PASSWORD', 'admin123');
define('DB_DATABASE', 'webappdb');
?>
Now Navigate to /var/www/html and create UserData.php file with below
content
18
<?php include "../webappdb/dbinfo.inc"; ?>
<html>
<body>
<h1>User Data</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);
19
}
?>
<!-- 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>
20
<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>",
21
"<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);
22
$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>");
23
/* 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;
?>
We can connect to database from webserver using mysql command
mysql -h webappdb.colzicsrustq.us-east-1.rds.amazonaws.com -P 3306 -u
admin -p
Create database in mysql console
create database webappdb;
24
Now we can use the ec2 domain name and navigate to UserData.php through
browser to access our web app connected with MySQL RDS DB. Which would
display the page where we can enter the data and click on Add data which
would persist the data into data base
We can connect to database from webserver using mysql command again and
validate the data
mysql -h webappdb.colzicsrustq.us-east-1.rds.amazonaws.com -P 3306 -u
admin -p
MySQL [webappdb]> show tables;
+--------------------+
| Tables_in_webappdb |
25
+--------------------+
| EMPLOYEES |
+--------------------+
1 row in set (0.00 sec)
MySQL [webappdb]> select * from EMPLOYEES;
+----+------------+-------------+
| ID | NAME | ADDRESS |
+----+------------+-------------+
| 1 | Rama | Ayodhya |
| 2 | Krishna | Dwaraka |
| 3 | Narayana | Vaikunta |
| 4 | Venkatesha | Tirupathi |
| 5 | Vittala | Pandarapura |
+----+------------+-------------+
5 rows in set (0.00 sec)
26
Now we need to add the created EC2 webserver for Autoscaling. We will
Create AMI, then Launch Template, Autoscaling Group, Load Balancers.
First lets create an AMI using our instance where we have installed Apache
Webserver. For this, Navigate back to EC2 dashboard from the top search bar
and click on instances. Select the running instance, Click on Actions > Image
and Templates > Create Image as shown below
Enter the details for AMI Creation and follow the steps as below and click on
Create image
27
Once the image is created. We can validate the status to be Available in AMI’s
screen
28
Let’s start creating Launch template using this now. Navigate to Launch
Templates option from the left to start initiation of Launch template, as per
below screenshots
Click on create Launch template and follow the instructions as per below
screenshots
29
30
31
32
After updating the User data, Click on Create Launch template. Check for this
33
Now, after creation of Launch Template we will create Auto Scaling Group.
Click on Auto Scaling option in the left menu and click on Create Auto Scaling..
34
Provide the information as mentioned below
35
Let’s enable at least for two AZ’s
36
Next, configure group size and scaling policies as per requirement, refer below
screenshots
37
To track Scaling behaviour, we are using Average CPU Utilization. As soon as
CPU load of the instance will hit the threshold value for specified time, a new
instance will be created, and load balancer will distribute the traffic across the
instances evenly.
Skip to Review from Here to last screen and validate all the information
38
39
Finally Click on Create Auto Scaling Group. We will be landing in this page
40
Now if you go back to EC2 Dashboard and Click on Instances you will see 3
new instances launched as per Auto scaling policy
41
If we are terminating one of the Instance from Auto scaling group. It should
adjust the numbers as per configuration
42
Now, we can see the one got terminated and other instance got adjusted
automatically as per the configuration in Auto scaling.
43
