Chapter 10: Dynamic Website Hosting (PHP, Python, Node.js with MySQL)
10.2 Installing MySQL / MariaDB
Think of the database like the college office register where every student's record is kept — the web app only asks the office for data; it does not keep the register itself. Technically, the app opens a TCP (or unix socket) connection to the database server on port 3306 and sends SQL queries.
MariaDB is a community fork of MySQL and is fully compatible for our purposes (same mysql client, same SQL, same port 3306).
| OS | Package | Service | Admin login |
|---|---|---|---|
| Amazon Linux 2023 | mariadb105-server |
mariadb |
sudo mysql |
| Ubuntu 22.04/24.04 | mysql-server (MySQL 8.x) or mariadb-server |
mysql (or mariadb) |
sudo mysql (root uses auth_socket) |
| CentOS Stream 9 | mariadb-server (or mysql-server for MySQL 8) |
mariadb (or mysqld) |
sudo mysql |
| Amazon Linux 2 (older) | mariadb-server |
mariadb |
sudo mysql |
Amazon Linux 2023
sudo yum install -y mariadb105-server
sudo service mariadb start
sudo systemctl enable mariadb
sudo service mariadb status
Ubuntu
sudo apt update
sudo apt install -y mysql-server
sudo service mysql start
sudo systemctl enable mysql
sudo service mysql status
CentOS Stream 9
sudo yum install -y mariadb-server
sudo service mariadb start
sudo systemctl enable mariadb
sudo service mariadb status
Securing the installation
sudo mysql_secure_installation
Recommended answers: switch to unix_socket authentication → n (keep current) or accept default; set/change root password → optional (root can already log in with sudo mysql); remove anonymous users → Y; disallow root login remotely → Y; remove test database → Y; reload privilege tables → Y. On Ubuntu MySQL you will first be asked about the VALIDATE PASSWORD component — choose Y and level 1 (MEDIUM) for practice.
Ubuntu MySQL root login
On Ubuntu, MySQL's root@localhost uses the auth_socket plugin: it logs in only via sudo mysql (no password). If mysql_secure_installation complains while setting the root password, simply skip that step. Applications should never use root — create a dedicated user as shown below.
Create the application database and user
Chala, aata database tayar karuya (let's set up the database). This creates database appdb, user appuser with password App@Pass123 (change it!), and a sample students table used by all three apps in this chapter:
sudo mysql <<'EOF'
CREATE DATABASE IF NOT EXISTS appdb;
CREATE USER IF NOT EXISTS 'appuser'@'localhost' IDENTIFIED BY 'App@Pass123';
CREATE USER IF NOT EXISTS 'appuser'@'127.0.0.1' IDENTIFIED BY 'App@Pass123';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'127.0.0.1';
FLUSH PRIVILEGES;
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');
EOF
# verify as the new user (enter App@Pass123 when prompted)
mysql -u appuser -p -h 127.0.0.1 -e "SELECT * FROM appdb.students;"
Useful SQL/admin commands:
SHOW DATABASES;
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'appuser'@'localhost';
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewPass@456';
DROP USER 'appuser'@'127.0.0.1';
# backup and restore
mysqldump -u appuser -p appdb > appdb-backup.sql
mysql -u appuser -p appdb < appdb-backup.sql
Keep the database private
MySQL/MariaDB should listen only on 127.0.0.1 (check with sudo ss -tlnp | grep 3306) and port 3306 must not be open in the security group. If the database is on a separate EC2/RDS, allow 3306 only from the web server's security group, not from 0.0.0.0/0.
Ravindra Bagale's Tip
Never let your application log in as the database root user. Create one user per application with access to only its own database. If the app is ever hacked, the damage stays limited to that one database. Lakshat theva — least privilege everywhere.