Automating MySQL Secure Installation with Ansible Playbook: Best Practices


4 views

When setting up MySQL/MariaDB through Ansible, the interactive mysql_secure_installation process presents a unique automation challenge. This security script typically:

  • Sets root password
  • Removes anonymous users
  • Disallows remote root login
  • Removes test database
  • Reloads privilege tables

Here's a complete playbook implementing all security measures:


- name: Secure MySQL installation
  hosts: dbservers
  become: yes
  vars:
    mysql_root_password: "YourSecurePassword123!"
    
  tasks:
    - name: Set root password
      community.mysql.mysql_user:
        name: root
        password: "{{ mysql_root_password }}"
        check_implicit_admin: yes
        login_user: root
        login_password: ""
        state: present
        
    - name: Remove anonymous users
      community.mysql.mysql_user:
        name: ''
        host_all: yes
        state: absent
        login_user: root
        login_password: "{{ mysql_root_password }}"
        
    - name: Disallow root remote login
      community.mysql.mysql_user:
        name: root
        host: "{{ item }}"
        state: absent
        login_user: root
        login_password: "{{ mysql_root_password }}"
      loop:
        - "%"
        - "127.0.0.1"
        - "::1"
        - "localhost.localdomain"
        
    - name: Remove test database
      community.mysql.mysql_db:
        name: test
        state: absent
        login_user: root
        login_password: "{{ mysql_root_password }}"
        
    - name: Reload privileges
      community.mysql.mysql_query:
        query: "FLUSH PRIVILEGES"
        login_user: root
        login_password: "{{ mysql_root_password }}"

Password Security

For production environments, never store passwords in plaintext:


- name: Load MySQL password from vault
  ansible.builtin.include_vars:
    file: secrets/mysql_vault.yml

Idempotency Checks

Add these tasks to verify security measures:


- name: Verify no anonymous users exist
  community.mysql.mysql_query:
    query: "SELECT user FROM mysql.user WHERE user = ''"
    login_user: root
    login_password: "{{ mysql_root_password }}"
  register: anonymous_users
  failed_when: anonymous_users.results | length > 0

Alternative: Expect Script

For scenarios requiring the actual script:


- name: Run mysql_secure_installation via expect
  community.general.expect:
    command: mysql_secure_installation
    responses:
      'Enter current password for root': "\\n"
      'Set root password?': "Y"
      'New password:': "{{ mysql_root_password }}"
      'Re-enter new password:': "{{ mysql_root_password }}"
      'Remove anonymous users?': "Y"
      'Disallow root login remotely?': "Y"
      'Remove test database and access to it?': "Y"
      'Reload privilege tables now?': "Y"
  when: ansible_os_family == 'RedHat'

Create a dedicated task to validate security configuration:


- name: Validate MySQL security configuration
  block:
    - name: Check root can authenticate
      community.mysql.mysql_query:
        query: "SELECT 1"
        login_user: root
        login_password: "{{ mysql_root_password }}"
        
    - name: Verify test database removal
      community.mysql.mysql_query:
        query: "SHOW DATABASES LIKE 'test'"
        login_user: root
        login_password: "{{ mysql_root_password }}"
      register: test_db
      failed_when: test_db.results | length > 0

When setting up MySQL/MariaDB servers, running mysql_secure_installation is crucial for production security. This interactive script:

  • Sets root password
  • Removes anonymous users
  • Disallows remote root login
  • Removes test database
  • Reloads privilege tables

Manual execution becomes tedious when provisioning multiple servers. Ansible provides an elegant solution.

We'll leverage these key modules:

mysql_user - Manage users
mysql_query - Execute SQL statements
mysql_db - Manage databases
command - Run shell commands when needed

Here's a production-ready playbook that replicates all mysql_secure_installation steps:

---
- name: Secure MySQL installation
  hosts: db_servers
  become: yes
  vars:
    mysql_root_password: "YourSecurePassword123!"
  
  tasks:
    - name: Set root password
      mysql_user:
        name: root
        password: "{{ mysql_root_password }}"
        host: localhost
        state: present
        login_user: root
        login_password: "" # Assuming fresh install with blank password
    
    - name: Remove anonymous users
      mysql_query:
        query: "DELETE FROM mysql.user WHERE User='';"
        login_user: root
        login_password: "{{ mysql_root_password }}"
    
    - name: Disable remote root login
      mysql_query:
        query: "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
        login_user: root
        login_password: "{{ mysql_root_password }}"
    
    - name: Remove test database
      mysql_db:
        name: test
        state: absent
        login_user: root
        login_password: "{{ mysql_root_password }}"
    
    - name: Reload privilege tables
      mysql_query:
        query: "FLUSH PRIVILEGES;"
        login_user: root
        login_password: "{{ mysql_root_password }}"

For fresh installations where root password isn't blank:

- name: Check if root password is set
  command: mysqladmin -uroot -p"{{ current_root_password }}" status
  register: mysql_status
  ignore_errors: yes
  changed_when: false
  
- name: Set root password (conditional)
  mysql_user:
    name: root
    password: "{{ mysql_root_password }}"
    host: localhost
    state: present
    login_user: root
    login_password: "{{ current_root_password if mysql_status.rc == 0 else '' }}"
  • Store passwords in Ansible Vault, not plaintext
  • Use no_log: true for tasks handling sensitive data
  • Consider separate playbooks for initial setup vs ongoing maintenance
  • Test thoroughly in staging before production

For complex interactive scenarios, you might use:

- name: Run mysql_secure_installation via expect
  expect:
    command: mysql_secure_installation
    responses:
      "Enter current password for root": ""
      "Set root password?": "Y"
      "New password:": "{{ mysql_root_password }}"
      "Re-enter new password:": "{{ mysql_root_password }}"
      "Remove anonymous users?": "Y"
      "Disallow root login remotely?": "Y"
      "Remove test database and access to it?": "Y"
      "Reload privilege tables now?": "Y"