MariaDB

export DB_PWD="<mariadb-root-password>"
export DB_REPLICA=$(openssl rand -base64 24)
export INIT_PASS=$(openssl rand -base64 24)

export CURRENT_NODE="node01"
export CURRENT_IP="192.0.2.111"
export IP_NODE01="192.0.2.111"
export IP_NODE02="192.0.2.112"
export IP_NODE03="192.0.2.113"

# LVM setup
pvcreate /dev/sdb
vgcreate database /dev/sdb
lvcreate -l 100%FREE -n data database
mkfs.xfs -f -i size=512 /dev/mapper/database-data

# Persistent mounts
cat >> /etc/fstab <<'EOF'
# MariaDB data volume
/dev/mapper/database-data  /database         xfs  defaults  1 2
/database/mysql            /var/lib/mysql    none bind      0 0
EOF

mkdir -p /database/mysql /var/lib/mysql
mount -a

dnf install -y MariaDB-server MariaDB-client MariaDB-backup galera-4

firewall-cmd --permanent --add-service=mysql
firewall-cmd --permanent --add-port={4444,4567,4568}/tcp
firewall-cmd --reload

systemctl enable --now mariadb

apt install -y mariadb-server mariadb-client mariadb-backup galera-4

systemctl enable --now mariadb

mariadb-secure-installation

-- Restrict root to localhost with strong password
ALTER USER 'root'@'localhost' IDENTIFIED BY '<strong-password>';

-- Remove any wildcard host users
SELECT User, Host FROM mysql.user WHERE Host = '%';
DROP USER IF EXISTS ''@'%';

-- Disable file loading (prevents LOAD DATA LOCAL INFILE attacks)
SET GLOBAL local_infile = 0;

-- Audit installed plugins
SELECT plugin_name, plugin_status FROM information_schema.plugins
WHERE plugin_status = 'ACTIVE';

[mysqld]
# Disable symbolic links
symbolic-links=0

# Disable local infile
local-infile=0

# Require secure transport for all connections
# (enable after configuring SSL)
# require_secure_transport=ON

# Limit connection errors before blocking host
max_connect_errors=10

# Disable LOAD DATA LOCAL
local-infile=0

# Log all connections and queries (for audit; disable in high-throughput production)
# general_log=1
# general_log_file=/var/log/mysql/general.log

# Enable error log
log_error=/var/log/mysql/error.log

# Only allow database traffic from application subnets
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.0.2.0/24" port port="3306" protocol="tcp" accept'
firewall-cmd --reload

-- Install the audit plugin for compliance logging
INSTALL SONAME 'server_audit';

SET GLOBAL server_audit_logging=ON;
SET GLOBAL server_audit_events='CONNECT,QUERY_DDL,QUERY_DML';
SET GLOBAL server_audit_file_path='/var/log/mysql/audit.log';
SET GLOBAL server_audit_file_rotate_size=10000000;
SET GLOBAL server_audit_file_rotations=10;

[mysqld]
# InnoDB buffer pool - set to ~70-80% of available RAM
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4

# Log file size - larger = better write performance, longer recovery
innodb_log_file_size=1G
innodb_log_buffer_size=64M

# Flush method - O_DIRECT avoids double buffering
innodb_flush_method=O_DIRECT

# Flush at commit: 1=safe, 2=faster (flush every second)
innodb_flush_log_at_trx_commit=1

# I/O capacity
innodb_io_capacity=2000
innodb_io_capacity_max=4000

# File per table for easier management
innodb_file_per_table=ON

# Thread concurrency (0=automatic)
innodb_thread_concurrency=0

# Read-ahead
innodb_read_ahead_threshold=56

[mysqld]
# Connection limits
max_connections=500
max_user_connections=450

# Thread pool (MariaDB-specific)
thread_handling=pool-of-threads
thread_pool_size=16
thread_pool_max_threads=1000

# Temporary tables
tmp_table_size=256M
max_heap_table_size=256M

# Query cache (disable for write-heavy workloads)
query_cache_type=0
query_cache_size=0

# Sort and join buffers
sort_buffer_size=4M
join_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=8M

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1
log_queries_not_using_indexes=1

mkdir -p /etc/mysql/ssl && cd /etc/mysql/ssl

# CA
openssl genrsa -out ca-key.pem 4096
openssl req -new -x509 -days 3650 -key ca-key.pem -out ca.pem \
  -subj "/CN=MariaDB CA"

# Server certificate
openssl genrsa -out server-key.pem 4096
openssl req -new -key server-key.pem -out server-req.pem \
  -subj "/CN=$(hostname)"
openssl x509 -req -days 3650 -in server-req.pem \
  -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem

# Client certificate
openssl genrsa -out client-key.pem 4096
openssl req -new -key client-key.pem -out client-req.pem \
  -subj "/CN=MariaDB Client"
openssl x509 -req -days 3650 -in client-req.pem \
  -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out client-cert.pem

chown mysql:mysql /etc/mysql/ssl/*
chmod 600 /etc/mysql/ssl/*-key.pem

[mysqld]
ssl_ca=/etc/mysql/ssl/ca.pem
ssl_cert=/etc/mysql/ssl/server-cert.pem
ssl_key=/etc/mysql/ssl/server-key.pem
tls_version=TLSv1.2,TLSv1.3

# Enforce TLS for all remote connections
require_secure_transport=ON

[client]
ssl_ca=/etc/mysql/ssl/ca.pem
ssl_cert=/etc/mysql/ssl/client-cert.pem
ssl_key=/etc/mysql/ssl/client-key.pem

SHOW VARIABLES LIKE '%ssl%';
-- Check current connection
STATUS;
-- Look for "SSL: Cipher in use is TLS_AES_256_GCM_SHA384"

[mariadb]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera provider
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

# Cluster identity
wsrep_cluster_name="<cluster-name>"
wsrep_cluster_address="gcomm://<ip-node01>,<ip-node02>,<ip-node03>"

# SST method - mariabackup is non-blocking (replaces deprecated rsync)
wsrep_sst_method=mariabackup
wsrep_sst_auth="mariabackup:<sst-password>"

# Node identity
wsrep_node_address="<current-ip>"
wsrep_node_name="<current-node>"

# Parallel replication
wsrep_slave_threads=4

# Flow control tuning
wsrep_provider_options="gcache.size=1G; gcs.fc_limit=256; gcs.fc_factor=0.8"

CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY '<sst-password>';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
FLUSH PRIVILEGES;

# Stop MariaDB on all nodes first
systemctl stop mariadb

# On the FIRST node only
galera_new_cluster

# Verify
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

# Start remaining nodes one at a time
# On node02:
systemctl start mariadb
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

# On node03:
systemctl start mariadb
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

-- Key status variables
SHOW STATUS LIKE 'wsrep_%';

-- Most important variables
SHOW STATUS WHERE Variable_name IN (
  'wsrep_cluster_size',
  'wsrep_cluster_status',
  'wsrep_connected',
  'wsrep_ready',
  'wsrep_local_state_comment',
  'wsrep_flow_control_paused',
  'wsrep_local_recv_queue_avg',
  'wsrep_local_send_queue_avg'
);

# Full backup with routines and triggers
mysqldump -u root -p --all-databases --routines --triggers \
  --single-transaction --quick > /backup/full-$(date +%Y%m%d).sql

# Single database
mysqldump -u root -p --single-transaction <database> > /backup/<database>.sql

# Restore
mysql -u root -p < /backup/full-20260322.sql
mysql -u root -p <database> < /backup/<database>.sql

# Full backup
mariabackup --backup --target-dir=/backup/full \
  --user=mariabackup --password=<sst-password>

# Prepare the backup (apply redo log)
mariabackup --prepare --target-dir=/backup/full

# Incremental backup (based on last full)
mariabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=mariabackup --password=<sst-password>

# Prepare incremental
mariabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

systemctl stop mariadb
rm -rf /var/lib/mysql/*

mariabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql

systemctl start mariadb

[mysqld]
log_bin=/var/log/mysql/mariadb-bin
binlog_format=ROW
expire_logs_days=14
max_binlog_size=256M

# List binary logs
mysqlbinlog --list /var/log/mysql/mariadb-bin.index

# Point-in-time recovery
mysqlbinlog --start-datetime="2026-03-22 10:00:00" \
  --stop-datetime="2026-03-22 11:00:00" \
  /var/log/mysql/mariadb-bin.000042 | mysql -u root -p

#!/bin/bash
# /usr/local/bin/mariadb-backup.sh
BACKUP_DIR="/backup/mariadb"
RETENTION_DAYS=14
DATE=$(date +%Y%m%d-%H%M%S)

mkdir -p "${BACKUP_DIR}"

# Full physical backup
mariabackup --backup --target-dir="${BACKUP_DIR}/${DATE}" \
  --user=mariabackup --password="<sst-password>"

mariabackup --prepare --target-dir="${BACKUP_DIR}/${DATE}"

# Compress
tar -czf "${BACKUP_DIR}/${DATE}.tar.gz" -C "${BACKUP_DIR}" "${DATE}"
rm -rf "${BACKUP_DIR}/${DATE}"

# Retention
find "${BACKUP_DIR}" -name "*.tar.gz" -mtime +${RETENTION_DAYS} -delete

-- Enable binary logging (in my.cnf: log_bin, server_id)
CREATE USER 'replication'@'<replica-ip>' IDENTIFIED BY '<repl-password>';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<replica-ip>';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;
-- Note File and Position

CHANGE MASTER TO
  MASTER_HOST='<primary-ip>',
  MASTER_USER='replication',
  MASTER_PASSWORD='<repl-password>',
  MASTER_LOG_FILE='<binlog_file>',
  MASTER_LOG_POS=<position>;

START SLAVE;
SHOW SLAVE STATUS\G

-- On replica
SHOW SLAVE STATUS\G
-- Key fields:
--   Seconds_Behind_Master
--   Slave_IO_Running
--   Slave_SQL_Running
--   Last_Error

-- Galera-specific lag
SHOW STATUS LIKE 'wsrep_local_recv_queue_avg';

CREATE DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER '<username>'@'<host>' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON <database_name>.* TO '<username>'@'<host>';
FLUSH PRIVILEGES;

# Export
mysqldump -u <username> -p <database_name> > <database_name>.sql

# Import
mysql -u <username> -p <database_name> < <database_name>.sql

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE ON <database>.<table> TO '<user>'@'<host>';

-- Revoke privileges
REVOKE DELETE ON <database>.<table> FROM '<user>'@'<host>';

-- View grants
SHOW GRANTS FOR '<user>'@'<host>';

-- List all users
SELECT User, Host, plugin FROM mysql.user;

-- Active connections
SHOW STATUS LIKE 'Threads_connected';

-- Buffer pool hit ratio (should be > 99%)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Hit ratio = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

-- Table locks
SHOW STATUS LIKE 'Table_locks%';

-- Slow queries
SHOW STATUS LIKE 'Slow_queries';

-- Cluster health at a glance
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN (
  'wsrep_cluster_size',
  'wsrep_cluster_status',
  'wsrep_ready',
  'wsrep_connected',
  'wsrep_local_state_comment',
  'wsrep_flow_control_paused',
  'wsrep_cert_deps_distance'
);