Ravindra BagaleCourses & study guides मराठी Track your progress

Chapter 10: Dynamic Website Hosting (PHP, Python, Node.js with MySQL)

10.2 Installing MySQL / MariaDB

By Last updated:

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. It's the same at Mauli Dairy – the orders register stays with the database, and the app only asks for it.

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

Come on, now 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 give your application the database root user. Create a separate user for each application, with access to only its own database. If the app is ever hacked, the damage stays limited to that one database. Remember – least privilege everywhere.

Lab

Chala, tumchya server var MariaDB (kiwa MySQL) install kara, sudo mysql_secure_installation chalva aani appdb/appuser banva. Mag mysql -u appuser -p -h 127.0.0.1 -e "SELECT * FROM appdb.students;" ne teen rows distat ka te bagha, aani sudo ss -tlnp | grep 3306 ne database kontya address var aikto te check kara.

Got it? Database ready, a separate user for the app, and port 3306 closed – this is the safe foundation. Now let's move on to connecting PHP to this database.