Chapter 15: Amazon VPC — Subnets, Route Tables, NACL, NAT Gateway and Bastion Host

15.11 Project: MySQL, PHP-FPM and Nginx on three tiers

By Ravindra Bagale · Last updated: 30 September 2026

Step 1 — Database server: MariaDB (MySQL-compatible)

SSH to db (ssh -J ec2-user@<BASTION_PUBLIC_IP> ec2-user@10.0.3.10). Amazon Linux 2023 ships MariaDB 10.5, which is MySQL-compatible (same mysql client, same SQL, same port 3306):

sudo dnf install mariadb105-server -y
sudo service mariadb start
sudo systemctl enable mariadb          # systemctl is the modern equivalent; enable = start at every boot
sudo mysql_secure_installation         # remove anonymous users, test database, remote root: answer Y

Let MariaDB listen on the network, not only on localhost:

sudo nano /etc/my.cnf.d/mariadb-server.cnf

Under the [mysqld] line add:

bind-address = 0.0.0.0

0.0.0.0 means "listen on every network interface of this server". That is safe here because db-sg allows port 3306 only from app-sg. For an even tighter setup, use the database server's own private IP (10.0.3.10) instead. Restart and check:

sudo service mariadb restart
sudo ss -tlnp | grep 3306              # expect 0.0.0.0:3306 (or 10.0.3.10:3306)

Create the database, a user that may connect only from the app subnet, and sample data. Open the MariaDB shell with sudo mysql and run:

CREATE DATABASE IF NOT EXISTS appdb;
CREATE USER IF NOT EXISTS 'app'@'10.0.2.%' IDENTIFIED BY 'Society@Pass123';
GRANT SELECT, INSERT ON appdb.* TO 'app'@'10.0.2.%';
USE appdb;
CREATE TABLE IF NOT EXISTS students (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  city VARCHAR(50)  NOT NULL
);
INSERT INTO students (name, city) VALUES
  ('Aarav Patil','Pune'), ('Sneha Deshmukh','Nagpur'), ('Rohan Kulkarni','Nashik');
SELECT user, host FROM mysql.user;
EXIT;

'app'@'10.0.2.%' means user app connecting from any address that starts with 10.0.2., which is exactly the app subnet. Change the password to your own.

Step 2 — App server: PHP-FPM listening on port 9000

SSH to app (10.0.2.10). Install PHP-FPM and the MySQL driver:

sudo yum install php-fpm php-mysqlnd -y

By default PHP-FPM on Amazon Linux 2023 listens on a Unix socket (/run/php-fpm/www.sock), which only programs on the same server can use. Nginx runs on another server, so PHP-FPM must listen on a TCP port instead and accept connections from the Nginx server's IP:

sudo cp /etc/php-fpm.d/www.conf ~/www.conf.backup
sudo sed -i 's|^listen = .*|listen = 0.0.0.0:9000|' /etc/php-fpm.d/www.conf
sudo sed -i 's|^;*listen.allowed_clients = .*|listen.allowed_clients = 10.0.1.10|' /etc/php-fpm.d/www.conf
grep -E '^listen' /etc/php-fpm.d/www.conf

The grep must show listen = 0.0.0.0:9000 and listen.allowed_clients = 10.0.1.10, the web server's private IP. A listen.acl_users line may also appear; it only matters for the Unix socket and can stay. You can use the app server's own private IP instead of 0.0.0.0 (listen = 10.0.2.10:9000). Start PHP-FPM:

sudo service php-fpm start
sudo systemctl enable php-fpm
sudo ss -tlnp | grep 9000              # expect 0.0.0.0:9000 ... php-fpm

Now the application itself. With FastCGI, Nginx sends only the script path; PHP-FPM reads and runs the .php file from its own disk. So the PHP files live here, on the app server, in /var/www/html. Create the folder and the database connection file:

sudo mkdir -p /var/www/html
sudo nano /var/www/html/db.php

<?php
$dsn  = "mysql:host=10.0.3.10;dbname=appdb;charset=utf8mb4";
$user = "app";
$pass = "Society@Pass123";
try {
    $pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
} catch (PDOException $e) {
    http_response_code(500);
    die("Database connection failed: " . htmlspecialchars($e->getMessage()));
}

Then the page that shows the students (sudo nano /var/www/html/index.php):

<?php
require __DIR__ . "/db.php";
$rows = $pdo->query("SELECT id, name, city FROM students ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>Students – 3-tier on AWS</title>
<link rel="stylesheet" href="/style.css"></head>
<body>
<h1>Students list</h1>
<table>
<tr><th>ID</th><th>Name</th><th>City</th></tr>
<?php foreach ($rows as $r): ?>
<tr><td><?= $r["id"] ?></td><td><?= htmlspecialchars($r["name"]) ?></td><td><?= htmlspecialchars($r["city"]) ?></td></tr>
<?php endforeach; ?>
</table>
<p>PHP ran on <?= htmlspecialchars(gethostname()) ?>, the data came from MySQL at 10.0.3.10.</p>
</body></html>

Protect the file that holds the password:

sudo chown root:apache /var/www/html/db.php
sudo chmod 640 /var/www/html/db.php

PHP-FPM runs as the user apache on Amazon Linux 2023, so db.php is readable only by root and the apache group. The other files keep the normal 644, and folders 755.

Optional check from the app server that the database answers (install only the client):

sudo dnf install mariadb105 -y
mysql -h 10.0.3.10 -u app -p -e "SELECT * FROM appdb.students;"

Step 3 — Web server: Nginx forwards .php to the app server

SSH to web (10.0.1.10). Install Nginx only. Do not install php-fpm on the web server, because its package adds a default PHP config that points to a local socket:

sudo yum install nginx -y
sudo service nginx start
sudo systemctl enable nginx

The default server block in /etc/nginx/nginx.conf serves /usr/share/nginx/html and loads every file in /etc/nginx/default.d/. Add the PHP forwarding there with sudo nano /etc/nginx/default.d/php-app.conf:

index index.php index.html;

location ~ \.php$ {
    fastcgi_pass 10.0.2.10:9000;              # PHP-FPM on the app server (private IP)
    include fastcgi_params;
    fastcgi_param SCRIPT_FILENAME /var/www/html$fastcgi_script_name;   # path ON THE APP SERVER
}

Two more files belong on the web server: an empty placeholder index.php and the stylesheet.

sudo touch /usr/share/nginx/html/index.php
sudo nano /usr/share/nginx/html/style.css

body{font-family:Arial,sans-serif;max-width:700px;margin:40px auto}
table{border-collapse:collapse}td,th{border:1px solid #999;padding:6px 12px}
th{background:#0E7C86;color:#fff}

Test the configuration and reload:

sudo nginx -t
sudo service nginx reload

Why the empty index.php on the web server? The index directive checks Nginx's own disk to decide which file to show for /. When it finds index.php there, it switches the request to /index.php, and the location ~ \.php$ block sends it to the app server, which runs its real index.php. The static file style.css is served by Nginx directly from /usr/share/nginx/html. Rule to remember: PHP files on the PHP server, static files on the Nginx server.

Always run sudo nginx -t before reload. A failed test never touches the running site.

A simpler alternative: proxy_pass to Apache + PHP on the app server

If you prefer, run Apache with PHP on the app server and let Nginx forward everything over HTTP. Then all files, PHP and static, live on the app server:

# app server
sudo yum install httpd php php-mysqlnd -y
sudo service httpd start
# app-sg: allow HTTP TCP 80 from web-sg (instead of 9000)

# web server: /etc/nginx/default.d/proxy-app.conf (instead of php-app.conf)
location / {
    proxy_pass http://10.0.2.10;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}

FastCGI (above) is lighter and shows how PHP-FPM really works; proxy_pass is easier to reason about. Both are real-world patterns.