How to Install and Secure MariaDB 11 on Rocky Linux 9 using Ansible

Introduction

Ansible is a powerful open-source automation tool for provisioning, configuration management, and application deployment. It enables infrastructure as code, making it easier to manage systems at scale.

MariaDB is a community-driven fork of MySQL, widely used in production environments as part of the LAMP/LEMP stack. With Rocky Linux 9 becoming a preferred enterprise-grade distribution, installing and securing MariaDB 11 properly is critical.

This guide walks you step by step on how to install, initialize, and secure MariaDB 11 on Rocky Linux 9 using Ansible.

Creating the Ansible Playbook

We begin by defining a playbook that installs and configures MariaDB 11 on our Rocky Linux 9 target host.

1
2
3
4
5
6
7
8
---
- name: Install and configure MariaDB 11 on Rocky Linux 9
  hosts: citizix-db-srv
  become: yes
  gather_facts: no

  vars:
    mariadb_root_password: "dnJd1QTQO9JAABF7iDlv"

Explanation

hosts: Defines the inventory group or server to run against.
become: yes: Ensures the tasks run with elevated privileges.
vars: Stores variables such as the MariaDB root password.

Updating Packages and Adding MariaDB Repository

First, ensure the system is up to date and add the official MariaDB 11 repository.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
- name: Ensure dnf cache is up to date
  ansible.builtin.dnf:
    update_cache: yes
    state: latest

- name: Download MariaDB repository setup script
  ansible.builtin.get_url:
    url: https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
    dest: /tmp/mariadb_repo_setup
    mode: '0755'

- name: Add MariaDB 11 repositories
  ansible.builtin.command: /tmp/mariadb_repo_setup --mariadb-server-version=11.8
  args:
    creates: /etc/yum.repos.d/mariadb.repo

- name: Clean up temporary script
  ansible.builtin.file:
    path: /tmp/mariadb_repo_setup
    state: absent

- name: Disable AppStream MariaDB module
  ansible.builtin.shell: |
    dnf -qy module disable mariadb
    dnf module reset mariadb -y

This ensures we’re pulling MariaDB 11.8 from the official repository instead of the older AppStream module.

Installing MariaDB Server

Now install the database server and required dependencies.

1
2
3
4
5
6
7
8
- name: Install MariaDB server and dependencies
  ansible.builtin.dnf:
    name:
      - mariadb-server
      - MariaDB-client
      - MariaDB-backup
      - python3-PyMySQL
    state: present

The python3-PyMySQL package is required for Ansible to manage MariaDB users and databases.

Starting and Enabling MariaDB

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
- name: Enable and start MariaDB service
  ansible.builtin.systemd:
    name: mariadb
    state: started
    enabled: yes

- name: Wait for MariaDB to be ready
  ansible.builtin.wait_for:
    port: 3306
    delay: 10
    timeout: 60

This ensures MariaDB is running and ready to accept connections.

Securing MariaDB Installation

By default, MariaDB ships with insecure defaults. We’ll configure secure access by:

Restricting root login to localhost.
Removing anonymous users.
Disallowing remote root logins.
Removing the test database.
Flushing privileges to apply changes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
- name: Ensure root user can only login from localhost
  mysql_user:
    login_password: "{{ mariadb_root_password }}"
    check_implicit_admin: yes
    name: root
    host: "{{ item }}"
    password: "{{ mariadb_root_password }}"
    state: present
  with_items:
    - localhost
    - 127.0.0.1
    - ::1

Apply hardening steps

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
- name: Remove anonymous users
  command: mysql -p{{ mariadb_root_password }} -ne "DELETE FROM mysql.user WHERE User=''"
  changed_when: false

- name: Disallow root login remotely
  command: mysql -p{{ mariadb_root_password }} -ne "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1')"
  changed_when: false

- name: Remove test database and access to it
  command: |
    mysql -p{{ mariadb_root_password }} -ne "{{ item }}"
  with_items:
    - DROP DATABASE IF EXISTS test
    - DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'
  changed_when: false

- name: Reload privilege tables
  command: mysql -p{{ mariadb_root_password }} -ne "FLUSH PRIVILEGES"
  changed_when: false

Firewall Configuration

Since MariaDB should not be exposed to the internet in most cases, disable the database port in firewalld:

1
2
3
4
5
- name: Configure firewall for MariaDB
  ansible.builtin.firewalld:
    port: 3306/tcp
    permanent: yes
    state: disabled

If you do need remote access for a specific application or network, adjust firewall rules accordingly.

Full Playbook

Here’s the complete playbook for convenience:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
---
- name: Install and configure MariaDB 11 on Rocky Linux 9
  hosts: citizix-db-srv
  become: yes
  gather_facts: no

  vars:
    mariadb_root_password: "dnJd1QTQO9JAABF7iDlv"

  pre_tasks:
    - name: Ensure dnf cache is up to date
      ansible.builtin.dnf:
        update_cache: yes
        state: latest

  tasks:
    - name: Download MariaDB repository setup script
      ansible.builtin.get_url:
        url: https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
        dest: /tmp/mariadb_repo_setup
        mode: '0755'
        validate_certs: yes

    - name: Add MariaDB 11 repositories
      ansible.builtin.command: /tmp/mariadb_repo_setup --mariadb-server-version=11.8
      args:
        creates: /etc/yum.repos.d/mariadb.repo

    - name: Clean up temporary script
      ansible.builtin.file:
        path: /tmp/mariadb_repo_setup
        state: absent

    - name: Disable AppStream MariaDB module
      ansible.builtin.shell: |
        dnf -qy module disable mariadb
        dnf module reset mariadb -y

    - name: Install MariaDB server and dependencies
      ansible.builtin.dnf:
        name:
          - mariadb-server
          - MariaDB-client
          - MariaDB-backup
          - python3-PyMySQL     # required by mysql_* modules
        state: present
      tags: install

    - name: Verify MariaDB installation
      ansible.builtin.command: mariadb -V
      register: mariadb_version
      changed_when: false

    - name: Display MariaDB version
      ansible.builtin.debug:
        var: mariadb_version.stdout

    - name: Enable and start MariaDB service
      ansible.builtin.systemd:
        name: mariadb
        state: started
        enabled: yes

    - name: Wait for MariaDB to be ready
      ansible.builtin.wait_for:
        port: 3306
        delay: 10
        timeout: 60

    - name: Ensure root user can only login from localhost
      mysql_user:
        login_password: "{{ mariadb_root_password }}"
        check_implicit_admin: yes
        name: root
        host: "{{ item }}"
        password: "{{ mariadb_root_password }}"
        state: present
      with_items:
        - localhost
        - 127.0.0.1
        - ::1

    - name: Create MySQL configuration file for root access
      ansible.builtin.copy:
        content: |
          [client]
          user=root
          password={{ mariadb_root_password }}

          [mysql]
          user=root
          password={{ mariadb_root_password }}
        dest: /root/.my.cnf
        mode: '0600'

    - name: Reload privilege tables
      command: |
        mysql -p{{ mariadb_root_password }} -ne "{{ item }}"
      with_items:
        - FLUSH PRIVILEGES
      changed_when: False

    - name: Remove anonymous users
      command: |
        mysql -p{{ mariadb_root_password }} -ne "{{ item }}"
      with_items:
        - DELETE FROM mysql.user WHERE User=''
      changed_when: False

    - name: Disallow root login remotely
      command: |
        mysql -p{{ mariadb_root_password }} -ne "{{ item }}"
      with_items:
        - DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1')
      changed_when: False

    - name: Remove test database and access to it
      command: |
        mysql -p{{ mariadb_root_password }} -ne "{{ item }}"
      with_items:
        - DROP DATABASE IF EXISTS test
        - DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'
      changed_when: False

    - name: Reload privilege tables
      command: |
        mysql -p{{ mariadb_root_password }} -ne "{{ item }}"
      with_items:
        - FLUSH PRIVILEGES
      changed_when: False

    - name: Remove MySQL configuration file (cleanup)
      ansible.builtin.file:
        path: /root/.my.cnf
        state: absent

    - name: Configure firewall for MariaDB
      ansible.builtin.firewalld:
        port: 3306/tcp
        permanent: yes
        state: disabled

  handlers:
    - name: restart mariadb
      ansible.builtin.systemd:
        name: mariadb
        state: restarted

Running the Playbook

Save the playbook as setup-mariadb11.yaml and your inventory as hosts.yaml. Then run:

1
ansible-playbook -i hosts.yaml setup-mariadb11.yaml -vv

Conclusion

In this guide, we used Ansible to install and secure MariaDB 11 on Rocky Linux 9. By automating the installation and hardening steps, you ensure a consistent, secure, and repeatable setup for your database servers.

This method is scalable and works across multiple Rocky Linux 9 servers, making it ideal for production environments.