Install MariaDB

🐧 Linux Command Line

Core

Install MariaDB

# Add MariaDB repository
curl -sSL https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
dnf install -y MariaDB-server MariaDB-client
systemctl enable --now mariadb

Run Secure MariaDB script

[root@webserver ~]# mariadb-secure-installation

- Enter current password for root (enter for none): <enter>

- Switch to unix_socket authentication [Y/n] n

- Change the root password? [Y/n] Y

- New password: <enter-your-root-password>

- Re-enter new password: <enter-your-root-password>

- Remove anonymous users? [Y/n] Y

- Disallow root login remotely? [Y/n] Y

- Remove test database and access to it? [Y/n] Y

- Reload privilege tables now? [Y/n] Y

Configure MariaDB

/etc/my.cnf

[mysqld]
log_warnings=1
pid-file=/run/mariadb.pid
socket = /run/mysqld.sock
collation-server=utf8mb4_general_ci
character-set-server=utf8mb4
init_connect=SET NAMES utf8mb4
innodb_buffer_pool_size = 200M
query_cache_size = 8M
max_connections = 200
default_storage_engine = InnoDB
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow.log
long_query_time = 2
log_error = /var/log/mariadb/error.log
# skip-networking
bind-address = 127.0.0.1
skip-symbolic-links
local-infile=0

[client]
default-character-set=utf8mb4
socket = /run/mysqld.sock

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

Directory and Permissions

mkdir -p /var/log/mariadb
touch /var/log/mariadb/slow.log
touch /var/log/mariadb/error.log
chown -R mysql:mysql /var/log/mariadb/

Restart MariaDB

systemctl restart mariadb

Ansible Playbook

install-mariadb.yml

- name: Install and Secure MariaDB
  hosts: all
  remote_user: root
  vars:
    mariadb_root_password: "root-password"
    mariadb_bind_address: "127.0.0.1"

  tasks:
    - name: Add MariaDB repository
      get_url:
        url: https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
        dest: /tmp/mariadb_repo_setup
        mode: "0755"
      register: mariadb_repo_setup

    - name: Run MariaDB repository setup script
      command: /tmp/mariadb_repo_setup
      register: mariadb_repo_setup_result
      changed_when: mariadb_repo_setup_result.rc != 0

    - name: Install MariaDB server and client
      dnf:
        name:
          - MariaDB-server
          - MariaDB-client
        state: present

    - name: Enable and start MariaDB
      systemd:
        name: mariadb
        enabled: true
        state: started

    - name: Run MariaDB secure installation with expect
      expect:
        command: mariadb-secure-installation
        responses:
          "Enter current password for root": ""
          "Switch to unix_socket authentication": "n"
          "Change the root password": "Y"
          "New password": "{{ mariadb_root_password }}"
          "enter new password": "{{ mariadb_root_password }}"
          "Remove anonymous users": "Y"
          "Disallow root login remotely": "Y"
          "Remove test database and access to it": "Y"
          "Reload privilege tables now": "Y"
        timeout: 5

    - name: Deploy MariaDB configuration
      copy:
        dest: "/etc/my.cnf"
        owner: "mysql"
        group: "mysql"
        mode: "0644"
        content: |
          [mysqld]
          log_warnings=1
          pid-file=/run/mariadb.pid
          socket = /run/mysqld.sock
          collation-server=utf8mb4_general_ci
          character-set-server=utf8mb4
          init_connect=SET NAMES utf8mb4
          innodb_buffer_pool_size = 200M
          query_cache_size = 8M
          max_connections = 200
          default_storage_engine = InnoDB
          slow_query_log = 1
          slow_query_log_file = /var/log/mariadb/slow.log
          long_query_time = 2
          log_error = /var/log/mariadb/error.log
          # skip-networking
          bind-address = {{ mariadb_bind_address }}
          skip-symbolic-links
          local-infile=0

          [client]
          default-character-set=utf8mb4
          socket = /run/mysqld.sock

          #
          # This group is read both by the client and the server
          # use it for options that affect everything
          #
          [client-server]

          #
          # include *.cnf from the config directory
          #
          !includedir /etc/my.cnf.d

    - name: Create MariaDB log directory if not exists
      file:
        path: "/var/log/mariadb"
        state: directory
        owner: "mysql"
        group: "mysql"
        mode: "0750"

    - name: Create MariaDB log files if not exists
      file:
        path: "{{ item }}"
        state: touch
        owner: "mysql"
        group: "mysql"
        mode: "0644"
      loop:
        - "/var/log/mariadb/slow.log"
        - "/var/log/mariadb/error.log"

    - name: Restart MariaDB to apply changes
      systemd:
        name: mariadb
        state: restarted