Database

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

Install and secure MariaDB 11 on Rocky Linux 9 using Ansible, covering the official repository, service setup, root password, and hardening in one playbook.

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.

---
- 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.

- 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.

- 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

- 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.

Restrict root login

- 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

- 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:

- 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:

---
- 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:

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.

comments powered by Disqus
© 2020 - 2026 Citizix, Ltd.
Citizix Ltd
Built with Hugo
Theme Stack designed by Jimmy