Ansible Role - MariaDB Database - Multi-Instance

This Ansible Role helps you set up one or more MariaDB databases on a Linux server.

Molecule Logs: Short, Full

Tested on:

• Debian 11

Install

To install the role, you can use any of the following commands:

# latest version
ansible-galaxy role install git+https://github.com/ansibleguy/infra_mariadb

# from galaxy
ansible-galaxy install ansibleguy.infra_mariadb

# or install to a custom directory
ansible-galaxy install ansibleguy.infra_mariadb --roles-path ./roles

# install required dependencies
ansible-galaxy install -r requirements.yml

Usage

If you need an easy web interface for Ansible, check out my Ansible WebUI.

Configure

You will need to set up your MariaDB instances in the 'mariadb' section of your configuration.

mariadb:
  service:
    SyslogIdentifier: 'mariadb_ag_%I'  # %I = Instance-Key
    
  instances:
    api:
      root_pwd: !vault |  # Use Ansible Vault to encrypt passwords
        ...

      dbs:
        api:
          encoding: 'utf8mb4'
        templates:
        deprecated:
          state: 'absent'
      
      users:
        app:
          priv: 'api.*:ALL'
          pwd: !vault ...
        guy:
          priv: 'api.*:SELECT,SHOW VIEW'
          state: 'absent'
          pwd: !vault ...

      settings:
        innodb_log_file_size: 5G
        max_connections: 2000

      backup:
        enabled: true
        dbs: ['api']
        time: '*-*-* 01:00'
        creds:
          create: true

    test:
      ansible_user: 'not_root'
      ansible_pwd: !vault ...
      dbs:
        test:
          
      users:
        dummy:
          priv: 'test.*:ALL'
          pwd: !vault ...
          update_pwd: 'always'

      settings:
        port: 3307
        log_warnings: 4
        long_query_time: 2
        max_allowed_packet: 2G

    old_instance:
      state: 'absent'

It's recommended to use 'ansible-vault' for password encryption:

ansible-vault encrypt_string

Run the Playbook

Execute your playbook with:

ansible-playbook -K -D -i inventory/hosts.yml playbook.yml --ask-vault-pass

Here are some useful tags you can use:

• base => configure basic settings; instances will stay untouched
• instances
• config => configuration (base and instances)
• dbs
• backup => backup jobs
• users

For debugging errors, you can enable the debug variable while running:

ansible-playbook -K -D -i inventory/hosts.yml playbook.yml -e debug=yes

Functionality

• Package installation

  • Minimal Ansible dependencies
  • MariaDB database

• Configuration

  • Create/delete multiple MariaDB instances

  • Create/delete databases and users

  • Startup check script for multiple instances

  • Default settings include:

    • Run secure installation tasks
    • Lower swappiness
    • Increase open file limit

  • Defaults not included:

    • Database backup jobs

  • Default configurations include:

    • Instance data directory: "/var/lib/mysql/instance_${KEY}/"
    • Instance config directory: "/etc/mysql/instance.conf.d/server_${KEY}.cnf"
    • Automatic restart on failure
    • Log to syslog
    • Bind to localhost only
    • SSL disabled
    • Slow query logs enabled
    • InnoDB file for each table
    • Database DNS lookups disabled

Important Notes

• CAUTION: This role assumes that the standard MariaDB/MySQL instance on your target system will not be used. It changes the socket and port settings.

• CAUTION: Don't run this role on both nodes simultaneously if you have an active-active configuration. It may cause sync issues with user and database creation.

• Note: This role currently only supports Debian-based systems.

• Note: Many features of this role can be included or excluded.

  To see all options, check the default configurations in the main defaults file.

• Note: We use official community modules for database and user configurations. Support for replication may be added later.

• Note: Certificate management might be added later. You must currently provide certificates manually in /etc/mysql/ssl/{INSTANCE_KEY}/[ca|cert|key].pem.

• Note: User privileges must match exactly what is returned by a SHOW GRANT statement.

• Tip: If you explicitly mount the data directory, consider setting mount options to improve performance, such as noatime,nodiratime.

• Warning: By default, TCP/IP connections to localhost/127.0.0.1 won't work if 'skip-name-resolve' is enabled.

Example

Configuration (example)

mariadb:
  instances:
    guydb:
      root_pwd: !vault ...

      dbs:
        nice:
        memes:

      users:
        django:
          priv: 'backup.creds.defaults_file is not nonmemes.*:ALL'
          pwd: !vault ...
        backup:
          priv: '*.*:SELECT,RELOAD,PROCESS,LOCK TABLES,BINLOG MONITOR,SHOW VIEW,EVENT,TRIGGER'
          pwd: !vault ...

Expected Result:

# Config directories
guy@ansible:~# tree /etc/mysql
> /etc/mysql/
> └── instance.conf.d
>     └── server_guydb.cnf  # instance server configuration

# Data directories
guy@ansible:~# tree /var/lib/mysql
> /var/lib/mysql/
> └── instance_guydb  # configured instance

# Service config
guy@ansible:~# cat /etc/systemd/system/mariadb@.service.d/override.conf 
> [Unit]
> Documentation=https://github.com/ansibleguy/infra_mariadb
  
# Service status
guy@ansible:~# systemctl status mariadb@guydb.service 

# List databases
guy@ansible:~# mysql --socket=/run/mysql/mysqld_guydb.sock -p -e "show databases;"

# List users
guy@ansible:~# mysql --socket=/run/mysql/mysqld_guydb.sock -p -e "select user, host from mysql.user;"