Adding a Database

Chapter 3 — Database

Every dynamic website needs somewhere to store data — user accounts, content, configuration. This chapter covers all three major database options available on Debian 12 Bookworm: MySQL, MariaDB, and PostgreSQL. Each section covers installation, initial security hardening, creating a database and user, and verifying the connection. For Philip's Learning Blog the setup_database.sql script generated earlier is ready to run immediately after installation.

Which should I install? For a new PHP-based site on Debian, MariaDB is the simplest choice — it's in Debian's main repository (no extra repo needed), is a true drop-in replacement for MySQL, and is fully compatible with the setup_database.sql and all PDO queries in the existing codebase. MySQL requires adding a third-party repository first. PostgreSQL is the right choice if you need advanced query features, but requires adjusting the PHP connection setup.

MariaDB

⬡ MariaDB

Installation

Terminal

$ sudo apt update $ sudo apt install -y mariadb-server Setting up mariadb-server (1:10.11.6-0+deb12u1) ... # MariaDB starts automatically. Verify: $ sudo systemctl status mariadb ● mariadb.service - MariaDB Database Server Active: active (running) $ mariadb --version mariadb Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64)

Security Hardening

Always run the security script immediately after installation. It removes anonymous users, disables remote root login, removes the test database, and sets a root password.

Terminal — security hardening

$ sudo mariadb-secure-installation Enter current password for root (enter for none): [press Enter] Switch to unix_socket authentication [Y/n]: n Change the root password? [Y/n]: Y New password: [enter a strong root password] Re-enter new password: [repeat] Remove anonymous users? [Y/n]: Y Disallow root login remotely? [Y/n]: Y Remove test database and access to it? [Y/n]: Y Reload privilege tables now? [Y/n]: Y All done!

Creating a Database and User

Terminal — connect as root

$ sudo mariadb -u root -p Enter password: Welcome to the MariaDB monitor. MariaDB [(none)]>

-- Run these inside the MariaDB prompt

-- Create the database
CREATE DATABASE learning_blog
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Create the application user
CREATE USER 'philip'@'localhost' IDENTIFIED BY 'AsT@1sAd3mon';

-- Grant full access to the database
GRANT ALL PRIVILEGES ON learning_blog.* TO 'philip'@'localhost';

FLUSH PRIVILEGES;
EXIT;

For Philip's Learning Blog: The full setup_database.sql script already contains these statements plus all the CREATE TABLE definitions. Run it directly instead: sudo mariadb -u root -p < setup_database.sql

Verify the Connection

Terminal

MariaDB Management Commands

Task	Command
Start MariaDB	sudo systemctl start mariadb
Stop MariaDB	sudo systemctl stop mariadb
Restart MariaDB	sudo systemctl restart mariadb
Enable at boot	sudo systemctl enable mariadb
Connect as root	sudo mariadb -u root -p
Connect as user	mariadb -u philip -p dbname
Run a SQL file	mariadb -u root -p < file.sql
Dump a database	mariadb-dump -u root -p learning_blog > backup.sql
Check version	mariadb --version

MySQL

⬡ MySQL 8

MySQL requires a third-party repository. It is not in Debian's main repo. You must add the official MySQL APT repository before installing. Choose the Bookworm package when prompted.

Adding the MySQL Repository

Terminal — add MySQL APT repo

# Download the MySQL APT repository config package $ wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb # Install it — a dialog will appear asking which MySQL version to use # Select "MySQL 8.0" then "Ok" $ sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb $ sudo apt update

Installation

Terminal

$ sudo apt install -y mysql-server Setting up mysql-server (8.0.36-1debian12) ... # Verify: $ sudo systemctl status mysql ● mysql.service - MySQL Community Server Active: active (running) $ mysql --version mysql Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

Security Hardening

Terminal

$ sudo mysql_secure_installation VALIDATE PASSWORD component — would you like to setup? Y Password validation policy: 1 (MEDIUM — recommended) New root password: [enter strong password] Remove anonymous users? Y Disallow root login remotely? Y Remove test database? Y Reload privilege tables? Y All done!

Creating a Database and User

Terminal

$ sudo mysql -u root -p mysql>

-- MySQL 8 uses caching_sha2_password by default.
-- Specify mysql_native_password for compatibility with older PHP drivers.

CREATE DATABASE learning_blog
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

CREATE USER 'philip'@'localhost'
    IDENTIFIED WITH mysql_native_password
    BY 'AsT@1sAd3mon';

GRANT ALL PRIVILEGES ON learning_blog.* TO 'philip'@'localhost';

FLUSH PRIVILEGES;
EXIT;

mysql_native_password vs caching_sha2_password: MySQL 8 defaults to caching_sha2_password which requires an SSL connection or RSA key exchange. Specifying mysql_native_password avoids "Authentication plugin not supported" errors with PHP PDO over a standard local socket connection.

MySQL Management Commands

Task	Command
Start MySQL	sudo systemctl start mysql
Stop MySQL	sudo systemctl stop mysql
Restart MySQL	sudo systemctl restart mysql
Enable at boot	sudo systemctl enable mysql
Connect as root	sudo mysql -u root -p
Connect as user	mysql -u philip -p dbname
Run a SQL file	mysql -u root -p < file.sql
Dump a database	mysqldump -u root -p learning_blog > backup.sql
Check version	mysql --version

PostgreSQL

⬡ PostgreSQL 15

Installation

Terminal

# PostgreSQL 15 is in Debian's main repo — no extra repository needed $ sudo apt update $ sudo apt install -y postgresql postgresql-contrib Setting up postgresql-15 (15.6-0+deb12u1) ... $ sudo systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Active: active (running) $ psql --version psql (PostgreSQL) 15.6 (Debian 15.6-0+deb12u1)

How PostgreSQL Authentication Works

PostgreSQL uses peer authentication by default for local connections — it maps the Linux system user to a database user of the same name. The database superuser is postgres, matching the postgres Linux user created during installation. To connect, you switch to that user first:

Terminal — connect as postgres superuser

$ sudo -u postgres psql psql (15.6) Type "help" for help. postgres=#

Creating a Database and User

-- Inside the psql prompt

-- Create the application user with a password
CREATE USER philip WITH PASSWORD 'AsT@1sAd3mon';

-- Create the database owned by that user
CREATE DATABASE learning_blog
    OWNER philip
    ENCODING 'UTF8'
    LC_COLLATE 'en_GB.UTF-8'
    LC_CTYPE   'en_GB.UTF-8'
    TEMPLATE   template0;

-- Grant all privileges
GRANT ALL PRIVILEGES ON DATABASE learning_blog TO philip;

\q

Allowing Password Authentication

To connect with a username and password (as PHP will), you need to allow md5 or scram-sha-256 authentication in pg_hba.conf:

Terminal

$ sudo vi /etc/postgresql/15/main/pg_hba.conf # Find the line for local connections and change "peer" to "md5": # TYPE DATABASE USER ADDRESS METHOD local all all md5 ← change from "peer" $ sudo systemctl restart postgresql # Now connect with password authentication $ psql -U philip -d learning_blog -W Password: psql (15.6) learning_blog=>

PHP PDO Connection String for PostgreSQL

PostgreSQL uses a different PDO driver than MySQL/MariaDB. Update config.php if switching to PostgreSQL:

// config.php — PostgreSQL version
define('DB_HOST',    'localhost');
define('DB_NAME',    'learning_blog');
define('DB_USER',    'philip');
define('DB_PASS',    'AsT@1sAd3mon');

// DSN changes from mysql: to pgsql:
$dsn = 'pgsql:host=' . DB_HOST . ';dbname=' . DB_NAME;
$pdo = new PDO($dsn, DB_USER, DB_PASS);

PostgreSQL Management Commands

Task	Command
Start PostgreSQL	sudo systemctl start postgresql
Stop PostgreSQL	sudo systemctl stop postgresql
Restart PostgreSQL	sudo systemctl restart postgresql
Enable at boot	sudo systemctl enable postgresql
Connect as superuser	sudo -u postgres psql
Connect as user	psql -U philip -d learning_blog -W
Run a SQL file	psql -U philip -d learning_blog -f file.sql
Dump a database	pg_dump -U philip learning_blog > backup.sql
List databases	\l (inside psql)
List tables	\dt (inside psql)
Check version	psql --version

Side-by-Side Quick Reference

Task	MariaDB / MySQL	PostgreSQL
Connect as superuser	sudo mariadb -u root -p	sudo -u postgres psql
Create database	CREATE DATABASE name;	CREATE DATABASE name OWNER user;
Create user	CREATE USER 'u'@'localhost' IDENTIFIED BY 'pw';	CREATE USER u WITH PASSWORD 'pw';
Grant access	GRANT ALL ON db.* TO 'u'@'localhost';	GRANT ALL PRIVILEGES ON DATABASE db TO u;
List databases	SHOW DATABASES;	\l
List tables	SHOW TABLES;	\dt
Dump database	mysqldump / mariadb-dump -u root -p db > bk.sql	pg_dump -U user db > bk.sql
Restore database	mysql / mariadb -u root -p db < bk.sql	psql -U user -d db -f bk.sql
PHP PDO prefix	mysql:host=...;dbname=...	pgsql:host=...;dbname=...
Config file	/etc/mysql/mariadb.conf.d/	/etc/postgresql/15/main/

Next: Chapter 4 — PHP
Chapter 4 covers installing PHP 8.2 on Debian 12, configuring php.ini for a production web server, installing the essential extensions your site needs (pdo_mysql, mbstring, curl, gd and more), and verifying everything works with a live test script.