Skip to content

MariaDB

Overview

MariaDB is a community-developed fork of MySQL, providing a production-ready relational database with enterprise features. The solti-ensemble MariaDB role automates installation, configuration, and lifecycle management.

Purpose: - Provide database services for applications (Gitea, Ghost, ISPConfig) - Security-focused deployment - Automated backup functionality - Cross-platform support (Debian 12, Rocky Linux 9, Ubuntu 24)

Key Features

Installation

  • Automated package installation
  • Platform-specific repository configuration
  • Dependency resolution

Security

  • Removes anonymous users
  • Removes test database
  • Disables remote root login
  • Secure password configuration
  • Minimal privilege principle

Configuration

  • Customizable bind address (localhost or network)
  • Port configuration
  • Character set defaults
  • Performance tuning options

Lifecycle Management

  • Clean installation (state: present)
  • Complete removal (state: absent)
  • Optional data cleanup (remove_data: true)
  • Idempotent operations

Installation

Basic Installation

- hosts: database_servers
  become: true
  roles:
    - role: jackaltx.solti_ensemble.mariadb
      vars:
        mariadb_mysql_root_password: "{{ vault_mariadb_root }}"
        mariadb_state: present
        mariadb_security: true

Installation with Network Access

- hosts: database_servers
  become: true
  roles:
    - role: jackaltx.solti_ensemble.mariadb
      vars:
        mariadb_mysql_root_password: "{{ vault_mariadb_root }}"
        mariadb_bind_address: "0.0.0.0"  # Allow network connections
        mariadb_port: 3306
        mariadb_security: true

Configuration Options

Required Variables

# Root password (store in Ansible Vault)
mariadb_mysql_root_password: ""

Optional Variables

# State management
mariadb_state: present              # 'present' to install, 'absent' to remove
mariadb_remove_data: false          # 'true' to delete data during removal

# Network configuration
mariadb_bind_address: "127.0.0.1"   # Bind to localhost only (secure default)
mariadb_port: 3306                  # Standard MySQL/MariaDB port

# Security options
mariadb_security: true              # Enable security hardening
mariadb_remove_anonymous: yes       # Remove anonymous users
mariadb_remove_test_db: yes         # Remove test database

# Backup options
mariadb_backup_dir: "/var/backup/mysql"
mariadb_backup_enabled: true

Security Hardening

When mariadb_security: true, the role performs:

1. Remove Anonymous Users

DELETE FROM mysql.user WHERE User='';

Why: Anonymous users are security risks allowing unauthenticated access.

2. Remove Test Database

DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

Why: Test database has lax permissions and no production purpose.

3. Disable Remote Root Login

DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

Why: Root should only be accessible locally.

4. Set Root Password

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_secure_password');

Why: Ensure strong authentication.

5. Flush Privileges

FLUSH PRIVILEGES;

Why: Apply all permission changes immediately.

Service Management

Check Status

systemctl status mariadb

Start/Stop/Restart

systemctl start mariadb
systemctl stop mariadb
systemctl restart mariadb

View Logs

journalctl -u mariadb -f

Connect to Database

mysql -u root -p

Database Operations

Create Database

mysql -u root -p << EOF
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
EOF

Create User

mysql -u root -p << EOF
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
EOF

Create Remote User

mysql -u root -p << EOF
CREATE USER 'myapp_user'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'%';
FLUSH PRIVILEGES;
EOF

Note: Only allow remote users if mariadb_bind_address: "0.0.0.0"

Backup and Recovery

Manual Backup

# Backup single database
mysqldump -u root -p myapp_db > /var/backup/mysql/myapp_db_$(date +%Y%m%d).sql

# Backup all databases
mysqldump -u root -p --all-databases > /var/backup/mysql/all_databases_$(date +%Y%m%d).sql

Restore Database

mysql -u root -p myapp_db < /var/backup/mysql/myapp_db_20260111.sql

Automated Backup

The role can configure automated backups:

mariadb_backup_enabled: true
mariadb_backup_dir: "/var/backup/mysql"
mariadb_backup_schedule: "0 2 * * *"  # Daily at 2 AM

Application Integration

Gitea Integration

- hosts: git_servers
  roles:
    - role: jackaltx.solti_ensemble.mariadb
      vars:
        mariadb_mysql_root_password: "{{ vault_mariadb_root }}"

    - role: jackaltx.solti_ensemble.gitea
      vars:
        gitea_db_type: mysql
        gitea_db_host: "localhost"
        gitea_db_name: "gitea"
        gitea_db_user: "gitea"
        gitea_db_password: "{{ vault_gitea_db_pass }}"

Ghost Integration

- hosts: blog_servers
  roles:
    - role: jackaltx.solti_ensemble.mariadb
      vars:
        mariadb_mysql_root_password: "{{ vault_mariadb_root }}"

    - role: jackaltx.solti_ensemble.ghost
      vars:
        ghost_db_host: "localhost"
        ghost_db_user: "ghost"
        ghost_db_password: "{{ vault_ghost_db_pass }}"
        ghost_db_database: "ghost_production"

Network Configuration

Localhost Only (Default, Most Secure)

mariadb_bind_address: "127.0.0.1"

Use When: - Application on same host - Security is paramount - No remote access needed

Network Access

mariadb_bind_address: "0.0.0.0"

Use When: - Applications on different hosts - Distributed architecture - With proper firewall rules

Security Considerations: - Use strong passwords - Limit user host access (e.g., 'user'@'10.10.0.%') - Consider WireGuard VPN for remote access - Enable SSL/TLS for production

Removal

Remove Service (Keep Data)

- hosts: database_servers
  become: true
  roles:
    - role: jackaltx.solti_ensemble.mariadb
      vars:
        mariadb_state: absent
        mariadb_remove_data: false
        mariadb_mysql_root_password: "{{ vault_mariadb_root }}"

Actions: - Stop mariadb service - Disable from boot - Remove packages - Keep /var/lib/mysql intact

Complete Removal (Delete Data)

- hosts: database_servers
  become: true
  roles:
    - role: jackaltx.solti_ensemble.mariadb
      vars:
        mariadb_state: absent
        mariadb_remove_data: true
        mariadb_mysql_root_password: "{{ vault_mariadb_root }}"

Actions: - Stop mariadb service - Disable from boot - Remove packages - Delete /var/lib/mysql - Delete configuration files - Delete log files

Warning: remove_data: true is irreversible. Backup first!

Troubleshooting

Service Won't Start

Check logs:

journalctl -u mariadb -n 100 --no-pager

Common causes: - Insufficient disk space - Permission issues on /var/lib/mysql - Port 3306 already in use - Corrupted InnoDB files

Solutions:

# Check disk space
df -h /var/lib/mysql

# Check permissions
ls -ld /var/lib/mysql
# Should be: drwxr-x--- mysql mysql

# Check port usage
ss -tulpn | grep 3306

# Check for existing mysqld processes
ps aux | grep mysqld

Can't Connect

Check service status:

systemctl status mariadb

Check bind address:

grep bind-address /etc/mysql/mariadb.conf.d/50-server.cnf  # Debian
grep bind-address /etc/my.cnf.d/mariadb-server.cnf         # Rocky

Check firewall (if remote access):

# Debian/Ubuntu
ufw status
ufw allow 3306/tcp

# Rocky Linux
firewall-cmd --list-all
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

Authentication Errors

Check user exists:

mysql -u root -p -e "SELECT user, host FROM mysql.user;"

Reset root password:

# Stop MariaDB
systemctl stop mariadb

# Start in safe mode
mysqld_safe --skip-grant-tables &

# Connect without password
mysql -u root

# Change password
USE mysql;
UPDATE user SET password=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;

# Kill safe mode and restart normally
killall mysqld
systemctl start mariadb

Performance Issues

Check slow query log:

mysql -u root -p -e "SET GLOBAL slow_query_log = 'ON';"
tail -f /var/lib/mysql/$(hostname)-slow.log

Check connections:

mysql -u root -p -e "SHOW PROCESSLIST;"

Check database size:

mysql -u root -p -e "
SELECT table_schema AS 'Database',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
GROUP BY table_schema;"

Platform-Specific Notes

Debian 12

Package: mariadb-server

Config Location: /etc/mysql/mariadb.conf.d/50-server.cnf

Service Name: mariadb

Rocky Linux 9

Package: mariadb-server

Config Location: /etc/my.cnf.d/mariadb-server.cnf

Service Name: mariadb

SELinux Considerations:

# Allow network binding (if needed)
setsebool -P mysqld_connect_any 1

Ubuntu 24.04

Package: mariadb-server

Config Location: /etc/mysql/mariadb.conf.d/50-server.cnf

Service Name: mariadb

Resource Requirements

Minimal Installation

  • CPU: 1 core
  • RAM: 512MB (1GB recommended)
  • Disk: 10GB minimum
  • Network: 100 Mbps

Production Guidelines

Small deployment (1-5 databases): - CPU: 2 cores - RAM: 2GB - Disk: 50GB - IOPS: 1000+

Medium deployment (5-20 databases): - CPU: 4 cores - RAM: 4-8GB - Disk: 100-250GB - IOPS: 3000+

Large deployment (20+ databases): - CPU: 8+ cores - RAM: 16GB+ - Disk: 500GB+ - IOPS: 5000+ - Consider dedicated storage (SAN, NFS)

Security Best Practices

  1. Always Use Ansible Vault for Passwords
# vars/vault.yml (encrypted)
vault_mariadb_root: "strong_random_password"
  1. Bind to Localhost by Default
mariadb_bind_address: "127.0.0.1"
  1. Enable Security Hardening
mariadb_security: true
  1. Use WireGuard for Remote Access

Instead of exposing port 3306 publicly:

- hosts: db_servers
  roles:
    - jackaltx.solti_ensemble.wireguard
    - jackaltx.solti_ensemble.mariadb
  1. Regular Backups
mariadb_backup_enabled: true
mariadb_backup_schedule: "0 2 * * *"
  1. Monitor Access Logs
tail -f /var/log/mysql/error.log

Reference Deployment

Production Example (ispconfig3-server.example.com): - MariaDB for ISPConfig database - Bind address: 127.0.0.1 (localhost only) - Security hardening enabled - Daily automated backups - Integrated with monitoring (solti-monitoring Telegraf)

Next Steps

  • NFS Client Role - Add network storage for database files
  • WireGuard Role - Secure remote database access
  • Backup Strategies - Advanced backup and recovery
  • Performance Tuning - Optimization for production workloads