Rocky Linux Database Servers

MySQL 8.4 database server installation on Rocky Linux 9

Database · MySQL

MySQL Community Server

Install MySQL 8.x from the official MySQL DNF repository — not the AppStream module — to get the latest version with full InnoDB optimisation, JSON support, and window functions.

Install MySQL 8 from official repo

# Add MySQL community repo

[root@rocky ~]$ dnf install -y https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm

[root@rocky ~]$ dnf install -y mysql-community-server

[root@rocky ~]$ systemctl enable --now mysqld

# Get temporary root password

[root@rocky ~]$ grep 'temporary password' /var/log/mysqld.log

[root@rocky ~]$ mysql_secure_installation

/etc/my.cnf — performance tuning

[mysqld]

innodb_buffer_pool_size = 2G # 70% of RAM

innodb_log_file_size = 512M

innodb_flush_method = O_DIRECT

max_connections = 300

query_cache_type = 0 # disable in 8.x

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 2

server-id = 1 # for replication

log_bin = /var/lib/mysql/mysql-bin

binlog_format = ROW

require_secure_transport = ON

Create app user and allow through firewall

mysql> CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'StrongPass!';

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'192.168.1.%';

mysql> FLUSH PRIVILEGES;

[root@rocky ~]$ firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port port=3306 protocol=tcp accept'

Database · PostgreSQL

PostgreSQL 16

PostgreSQL is the most feature-rich open-source RDBMS — JSONB, full-text search, table partitioning, logical replication, and ACID compliance. Install from the official PGDG repository for the latest stable release on Rocky Linux 9.

Install PostgreSQL 16 from PGDG

# Disable built-in PostgreSQL module first

[root@rocky ~]$ dnf -qy module disable postgresql

[root@rocky ~]$ dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

[root@rocky ~]$ dnf install -y postgresql16-server postgresql16

[root@rocky ~]$ /usr/pgsql-16/bin/postgresql-16-setup initdb

[root@rocky ~]$ systemctl enable --now postgresql-16

postgresql.conf — key settings

listen_addresses = '*' # allow remote

max_connections = 200

shared_buffers = 1GB # 25% of RAM

effective_cache_size = 3GB # 75% of RAM

work_mem = 16MB

wal_level = replica # for replication

max_wal_senders = 5

log_min_duration_statement = 1000 # log queries > 1s

Create role and database

postgres=# CREATE ROLE appuser LOGIN PASSWORD 'SecurePass!' CREATEDB;

postgres=# CREATE DATABASE appdb OWNER appuser;

postgres=# \c appdb appuser

MySQL vs PostgreSQL

Feature	MySQL 8	PostgreSQL 16
JSON Support	✔ JSON	✔ JSONB (indexed)
Full-Text Search	✔ Basic	✔ Advanced
Table Partitioning	✔ Yes	✔ Declarative
Logical Replication	✔ Yes	✔ Yes
Extensions	Limited	✔ PostGIS, pgvector…
Popularity (web apps)	✔ Very High	High

MariaDB Galera Cluster flow

          Node 1
          Primary — receives WRITE
        
          wsrep
          Replicates via Galera protocol
        
          Node 2
          Sync replica — can serve READs
        
          Node 3
          Sync replica — failover ready
        
          HAProxy
          Load-balances READ queries

Database · MariaDB

MariaDB — MySQL-Compatible Alternative

MariaDB is a drop-in MySQL replacement with additional storage engines (Aria, ColumnStore), Galera Cluster for synchronous multi-master replication, and a more permissive open-source licence. Available from the AppStream module on Rocky Linux 9.

Install MariaDB from AppStream

[root@rocky ~]$ dnf module list mariadb

[root@rocky ~]$ dnf module enable mariadb:10.11

[root@rocky ~]$ dnf install -y mariadb-server

[root@rocky ~]$ systemctl enable --now mariadb

[root@rocky ~]$ mariadb-secure-installation

💡

Galera Cluster gives you synchronous multi-master replication across 3+ nodes. Every write is committed on all nodes before the transaction is confirmed — no async lag, no data loss on failover.

Database · Backup Strategy

Backup Strategy for Rocky Linux Databases

A 3-2-1 backup strategy: 3 copies of data, 2 different storage media, 1 offsite. Automate it with cron.

💾

mysqldump (Logical)

Portable SQL dump. Slow on large DBs but human-readable. Best for dev/staging or small datasets.

mysqldump -u root -p --all-databases --single-transaction > /backup/full-$(date +%F).sql

⚡

Percona XtraBackup

Hot physical backup for InnoDB — no table locks, incremental, stream to S3. Best for large production MySQL.

xtrabackup --backup --stream=xbstream --compress | aws s3 cp - s3://bucket/backup.xbs

🐘

pg_dump / pg_basebackup

pg_dump for logical, pg_basebackup for physical streaming backup with WAL archiving to support PITR.

pg_basebackup -h localhost -U replicator -D /backup/pgbase -Ft -z -P

🔄

Automated Cron + Retention

Wrap backups in a cron job. Keep 7 daily, 4 weekly, 12 monthly. Alert on failure with systemd OnCalendar.

0 2 * * * /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1

MySQL Community Server

PostgreSQL 16

MySQL vs PostgreSQL

MariaDB — MySQL-Compatible Alternative

Backup Strategy for Rocky Linux Databases

mysqldump (Logical)

Percona XtraBackup

pg_dump / pg_basebackup

Automated Cron + Retention

Need a managed database server?