MariaDB Role¶

Overview¶

The solti-ensemble MariaDB role automates the installation, configuration, and lifecycle management of MariaDB, a production-ready relational database. Its purpose is to provide secure database services for various applications, with automated backup functionality and cross-platform support.

Requirements¶

Ansible 2.9+
Target system with Debian 12, Rocky Linux 9, or Ubuntu 24.

Installation / Quick Start¶

Basic Installation¶

- hosts: database_servers
  become: true
  roles:
    - role: jackaltx.solti_ensemble.mariadb
      vars:
        mariadb_mysql_root_password: "{{ vault_mariadb_root }}" # Store in Ansible Vault
        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

Role Variables / Configuration¶

Required Variables¶

Name	Description
`mariadb_mysql_root_password`	The root password for MariaDB (should be stored in Ansible Vault).

Optional Variables¶

Name	Description	Default
`mariadb_state`	`present` to install, `absent` to remove.	`present`
`mariadb_remove_data`	`true` to delete data during removal.	`false`
`mariadb_bind_address`	The address MariaDB binds to.	`127.0.0.1`
`mariadb_port`	The port MariaDB listens on.	`3306`
`mariadb_security`	Enable security hardening.	`true`
`mariadb_remove_anonymous`	Remove anonymous users.	`yes`
`mariadb_remove_test_db`	Remove the test database.	`yes`
`mariadb_backup_dir`	Directory for backups.	`/var/backup/mysql`
`mariadb_backup_enabled`	Enable automated backups.	`true`
`mariadb_backup_schedule`	Cron schedule for automated backups.	`0 2 * * *`

Usage¶

Connect to Database¶

mysql -u root -p

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

Note: For remote users, replace localhost with the appropriate host or % for any host. Ensure mariadb_bind_address is set to 0.0.0.0 for remote access.

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

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 in use, corrupted InnoDB files.

Can't Connect¶

Check service status: systemctl status mariadb
Check bind address in configuration file.
Check firewall rules if remote access is intended.

Authentication Errors¶

Verify user existence: mysql -u root -p -e "SELECT user, host FROM mysql.user;"
Root password can be reset by starting MariaDB in safe mode (mysqld_safe --skip-grant-tables &).

Role-Specific Sections¶

Security Hardening¶

The role performs several security hardening steps when mariadb_security: true, including removing anonymous users, removing the test database, disabling remote root login, and setting a secure root password.

Backup and Recovery¶

Manual and automated backup options are available. Automated backups are configured via mariadb_backup_enabled and mariadb_backup_schedule.

Application Integration¶

The role facilitates integration with applications like Gitea and Ghost by providing database user and schema setup.

Network Configuration¶

The mariadb_bind_address can be configured for 127.0.0.1 (localhost only, default) or 0.0.0.0 (network access). Strong passwords and firewall rules are recommended for network access. Consider using a WireGuard VPN for secure remote access.

Removal¶

The role supports both removing the service while keeping data (mariadb_remove_data: false) or a complete removal including data (mariadb_remove_data: true).