Install and configure MariaDB 11.6 with performance optimization and security hardening

Update system packages

Start by updating your package manager to ensure you get the latest security updates and dependencies.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Add MariaDB official repository

Add the official MariaDB repository to get the latest 11.6 version with security updates and performance improvements.

sudo apt install -y software-properties-common dirmngr apt-transport-https
curl -o /tmp/mariadb_repo_setup https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "2c3b4f28d37cfef9d69e5e0ac62df8a8ebc88e6e mariadb_repo_setup" | sha1sum -c -
sudo bash /tmp/mariadb_repo_setup --mariadb-server-version="mariadb-11.6"
sudo apt update

curl -o /tmp/mariadb_repo_setup https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "2c3b4f28d37cfef9d69e5e0ac62df8a8ebc88e6e mariadb_repo_setup" | sha1sum -c -
sudo bash /tmp/mariadb_repo_setup --mariadb-server-version="mariadb-11.6"
sudo dnf update -y

Install MariaDB 11.6 server and client

Install the complete MariaDB server package with client tools and development libraries for application connectivity.

sudo apt install -y mariadb-server mariadb-client mariadb-backup

sudo dnf install -y MariaDB-server MariaDB-client MariaDB-backup

Enable and start MariaDB service

Enable MariaDB to start automatically on boot and start the service immediately for configuration.

sudo systemctl enable --now mariadb
sudo systemctl status mariadb

Run initial security configuration

Execute the mysql_secure_installation script to remove insecure defaults, set root password, and configure basic security settings.

sudo mysql_secure_installation

When prompted, choose these security settings:

• Set root password: Yes (use a strong password)
• Remove anonymous users: Yes
• Disallow root login remotely: Yes
• Remove test database: Yes
• Reload privilege tables: Yes

Create optimized MariaDB configuration

Configure InnoDB storage engine settings, query cache, and connection limits for optimal performance based on available system memory.

[mysqld]
Basic settings
bind-address = 127.0.0.1
port = 3306
max_connections = 200
max_connect_errors = 1000000

InnoDB settings for performance
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_io_capacity_max = 2000

Query cache settings
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Security
local_infile = 0
skip_show_database

Adjust buffer pool size based on system memory

Set innodb_buffer_pool_size to 70-80% of available RAM for dedicated database servers. Check your system memory first.

free -h
For 4GB RAM system: innodb_buffer_pool_size = 3G
For 8GB RAM system: innodb_buffer_pool_size = 6G
For 16GB RAM system: innodb_buffer_pool_size = 12G

Create log directory with correct permissions

Create the slow query log directory and set appropriate ownership for the mysql user to write log files.

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

Restart MariaDB with new configuration

Restart the service to apply performance optimizations and verify the configuration is valid.

sudo systemctl restart mariadb
sudo systemctl status mariadb

Generate SSL certificates for MariaDB

Create self-signed SSL certificates for encrypted client connections and replication security.

sudo mkdir -p /etc/mysql/ssl
sudo openssl genrsa 2048 > /etc/mysql/ssl/ca-key.pem
sudo openssl req -new -x509 -nodes -days 365000 -key /etc/mysql/ssl/ca-key.pem -out /etc/mysql/ssl/ca-cert.pem -subj "/C=US/ST=State/L=City/O=Organization/CN=MariaDB-CA"
sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-req.pem -subj "/C=US/ST=State/L=City/O=Organization/CN=MariaDB-Server"
sudo openssl x509 -req -in /etc/mysql/ssl/server-req.pem -days 365000 -CA /etc/mysql/ssl/ca-cert.pem -CAkey /etc/mysql/ssl/ca-key.pem -set_serial 01 -out /etc/mysql/ssl/server-cert.pem

Set SSL certificate permissions

Configure secure file permissions for SSL certificates to prevent unauthorized access while allowing MariaDB to read them.

sudo chown -R mysql:mysql /etc/mysql/ssl
sudo chmod 600 /etc/mysql/ssl/*-key.pem
sudo chmod 644 /etc/mysql/ssl/*-cert.pem
sudo chmod 644 /etc/mysql/ssl/ca-cert.pem

Configure SSL in MariaDB

Enable SSL encryption for client connections and configure certificate paths in the MariaDB configuration.

[mysqld]
SSL Configuration
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON

[client]
ssl-ca = /etc/mysql/ssl/ca-cert.pem

Restart MariaDB and verify SSL

Restart the service to enable SSL encryption and verify that SSL is working correctly.

sudo systemctl restart mariadb
sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'have_ssl';"
sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'ssl_%';"

Create application database

Create a dedicated database for your application with proper character set and collation for international support.

sudo mysql -u root -p

CREATE DATABASE myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;

Create application user with limited privileges

Create a dedicated database user with minimum required privileges following the principle of least privilege for security.

CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'SecureP@ssw0rd123!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON myapp_production.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'myapp_user'@'localhost';
EXIT;

Test user connection and permissions

Verify that the new user can connect and access only the intended database with the granted privileges.

mysql -u myapp_user -p -h localhost myapp_production
Test in MariaDB console:
USE myapp_production;
CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test_table VALUES (1, 'test');
SELECT * FROM test_table;
DROP TABLE test_table;

Create backup script

Create an automated backup script using mariadb-dump for consistent database backups with compression and rotation.

#!/bin/bash

MariaDB backup configuration
DB_USER="root"
DB_HOST="localhost"
BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +"%Y%m%d_%H%M%S")
RETENTION_DAYS=7

Create backup directory
mkdir -p $BACKUP_DIR

Database list (exclude system databases for application backups)
DATABASES="myapp_production"

Create backup
for DB in $DATABASES; do
    echo "Backing up database: $DB"
    mariadb-dump --user=$DB_USER --password --host=$DB_HOST \
        --single-transaction --routines --triggers \
        $DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz
    
    if [ $? -eq 0 ]; then
        echo "Backup completed: ${DB}_${DATE}.sql.gz"
    else
        echo "Backup failed for database: $DB"
        exit 1
    fi
done

Remove old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup process completed at $(date)"

Set backup script permissions

Make the backup script executable and secure it with appropriate permissions to prevent unauthorized access.

sudo chmod 750 /usr/local/bin/mariadb-backup.sh
sudo chown root:mysql /usr/local/bin/mariadb-backup.sh

Create backup directory

Create the backup directory with secure permissions and ensure adequate disk space for backup retention.

sudo mkdir -p /var/backups/mariadb
sudo chown mysql:mysql /var/backups/mariadb
sudo chmod 750 /var/backups/mariadb

Schedule automated backups

Configure daily automated backups using cron to run during low-traffic periods for minimal performance impact.

sudo crontab -e

# Daily MariaDB backup at 2:30 AM
30 2   * /usr/local/bin/mariadb-backup.sh >> /var/log/mariadb-backup.log 2>&1

Test backup script

Run the backup script manually to verify it works correctly and check backup file creation.

sudo /usr/local/bin/mariadb-backup.sh
sudo ls -la /var/backups/mariadb/
sudo zcat /var/backups/mariadb/myapp_production_*.sql.gz | head -20

Configure firewall rules

Set up firewall rules to allow MariaDB access only from trusted sources and block unauthorized connection attempts.

# Allow MySQL/MariaDB from localhost only
sudo ufw allow from 127.0.0.1 to any port 3306
For remote access from specific IP:
sudo ufw allow from 203.0.113.10 to any port 3306
sudo ufw status

# Allow MySQL/MariaDB from localhost only
sudo firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='127.0.0.1' port protocol='tcp' port='3306' accept"
For remote access from specific IP:
sudo firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='203.0.113.10' port protocol='tcp' port='3306' accept"
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

Configure connection limits and timeouts

Add additional security configurations to prevent connection abuse and improve security hardening.

[mysqld]
Connection security
max_user_connections = 50
max_connections = 200
connect_timeout = 10
wait_timeout = 600
interactive_timeout = 600

Disable dangerous functions
local_infile = 0

Enable logging for security monitoring
log_warnings = 2
general_log = 0
general_log_file = /var/log/mysql/general.log

Apply final security configuration

Restart MariaDB to apply all security configurations and verify the service is running with new settings.

sudo systemctl restart mariadb
sudo systemctl status mariadb