Ansible mysql_secure_installation playbook

Right after installing MySQL/MariaDB server it is mandatory to run mysql_secure_installation tool that comes with the default server installation. This tool mainly removes the test database, test and anonymous users and set a new password for user root.

Following is an Ansible playbook to achieve the same results:

# Ansible mysql_secure_installation playbook

- name: delete anonymous MySQL server user for {{ ansible_hostname }}
  action: mysql_user user="" host="{{ ansible_hostname }}" state="absent"

- name: delete anonymous MySQL server user for localhost
  action: mysql_user user="" state="absent"

- name: remove the MySQL test database
  action: mysql_db db=test state=absent
# 'localhost' needs to be the last item for idempotency, see
- name: Change root user password on first run
  mysql_user: login_user=root
              password={{ mysql_root_password }}
              host={{ item }}
    - "{{ ansible_hostname }}"
    - ::1
    - localhost

As it is not very recommended to store the MySQL root password anywhere, you will have to pass it as an Ansible variable:

$ ansible-playbook -i <your inventary> mysql_secure_installation.yml --extra-vars "mysql_root_password=<your password>"

2 thoughts on “Ansible mysql_secure_installation playbook

  1. Cheers, very helpful.

    One issue I cam across is that {{ ansible_hostname }} comes from the machine hostname – unfortunately the script preserves case but the default MariaDB install does not. So if the target hostname was MyServer the above would try to try to update user ‘root’@’MyServer’ but the actual user entry in MariaDB’s user table is ‘root’@’myserver’. For now I’ve just decided to set the hostname on every server to be all lowercase but I’d love to hear a better solution as this will not work well for existing systems.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s