Database Administration: MySQL and PostgreSQL Essentials

Most web applications rely on a relational database. This guide covers installation, user management, backups, basic tuning, and replication concepts for the two most popular open-source databases.

MySQL / MariaDB

Installation

# Debian/Ubuntu
sudo apt update
sudo apt install mysql-server

# RHEL/CentOS/Fedora (MariaDB)
sudo dnf install mariadb-server
sudo systemctl enable --now mariadb

Secure the Installation

sudo mysql_secure_installation

This interactive script will: - Set the root password - Remove anonymous users - Disallow remote root login - Remove the test database

Database and User Management

-- Connect as root
-- sudo mysql -u root -p

-- Create a database
CREATE DATABASE webapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a user with a password
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- Grant permissions
GRANT ALL PRIVILEGES ON webapp.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

-- Grant read-only access to a reporting user
CREATE USER 'reporter'@'10.0.1.%' IDENTIFIED BY 'ReadOnlyPass!';
GRANT SELECT ON webapp.* TO 'reporter'@'10.0.1.%';
FLUSH PRIVILEGES;

-- Show grants for a user
SHOW GRANTS FOR 'appuser'@'localhost';

Backup with mysqldump

# Dump a single database
mysqldump -u root -p webapp > webapp_backup.sql

# Dump all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Dump with compression
mysqldump -u root -p webapp | gzip > webapp_backup.sql.gz

# Restore
mysql -u root -p webapp < webapp_backup.sql

For large databases, consider mysqldump --single-transaction (InnoDB) to avoid locking tables during the backup.

Basic Tuning

Edit /etc/mysql/mysql.conf.d/mysqld.cnf (or /etc/my.cnf):

[mysqld]
# InnoDB buffer pool -- set to ~70% of available RAM on a dedicated DB server
innodb_buffer_pool_size = 2G

# Log slow queries for performance analysis
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# Connection limits
max_connections = 200

# Temp tables
tmp_table_size = 64M
max_heap_table_size = 64M

sudo systemctl restart mysql

PostgreSQL

Installation

# Debian/Ubuntu
sudo apt update
sudo apt install postgresql postgresql-client
sudo systemctl enable --now postgresql

# RHEL/CentOS/Fedora
sudo dnf install postgresql-server postgresql
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

Database and User Management

PostgreSQL uses its own authentication system. By default, the postgres OS user maps to the postgres superuser:

# Create a user (role)
sudo -u postgres createuser --interactive appuser

# Create a database owned by that user
sudo -u postgres createdb -O appuser webapp

# Or from the psql shell:
sudo -u postgres psql

-- Inside psql
CREATE USER appuser WITH PASSWORD 'StrongPassword123!';
CREATE DATABASE webapp OWNER appuser;

-- Grant specific privileges
GRANT CONNECT ON DATABASE webapp TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

-- List databases
\l

-- List users
\du

-- Connect to a database
\c webapp

Backup with pg_dump

# Dump a single database (custom format -- compressed, supports parallel restore)
sudo -u postgres pg_dump -Fc webapp > webapp.dump

# Dump in plain SQL
sudo -u postgres pg_dump webapp > webapp.sql

# Dump all databases
sudo -u postgres pg_dumpall > all_databases.sql

# Restore from custom format
sudo -u postgres pg_restore -d webapp webapp.dump

# Restore from plain SQL
sudo -u postgres psql webapp < webapp.sql

Basic Tuning

Edit /etc/postgresql/16/main/postgresql.conf (adjust version number):

# Shared buffers -- set to ~25% of available RAM
shared_buffers = 1GB

# Work memory for sorts and joins (per operation)
work_mem = 64MB

# Maintenance tasks (VACUUM, CREATE INDEX)
maintenance_work_mem = 256MB

# WAL settings for write-heavy workloads
wal_buffers = 64MB
checkpoint_completion_target = 0.9

# Connection limits
max_connections = 200

# Effective cache size (estimate of OS file cache -- ~75% of RAM)
effective_cache_size = 3GB

# Logging
log_min_duration_statement = 1000   # log queries taking > 1 second

sudo systemctl restart postgresql

Use PGTune for automated tuning recommendations based on your hardware.

Replication Concepts

Both MySQL and PostgreSQL support replication for high availability and read scaling:

Concept	MySQL	PostgreSQL
Streaming replication	Binary log replication (source/replica)	WAL streaming (primary/standby)
Read replicas	read_only = ON on replica	Hot standby
Failover	MySQL Group Replication, ProxySQL	Patroni, pg_auto_failover
Logical replication	binlog_format = ROW + GTID	Logical replication slots

Setting up replication is a significant topic. The key principle: the primary writes data, replicas receive and replay those writes, and your application routes reads to replicas and writes to the primary.

Monitoring

# MySQL -- check running queries
mysqladmin -u root -p processlist

# MySQL -- show engine status
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

# PostgreSQL -- check active queries
sudo -u postgres psql -c "SELECT pid, state, query FROM pg_stat_activity WHERE state != 'idle';"

# PostgreSQL -- check database sizes
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;"

---

Return to the Web Servers hub or continue to Performance Tuning and Nginx Guide.