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
# http://ansible.cc/docs/modules.html#mysql-user   
- name: Change root user password on first run
  mysql_user: login_user=root
              login_password=''
              name=root
              password={{ mysql_root_password }}
              priv=*.*:ALL,GRANT
              host={{ item }}
  with_items:
    - "{{ ansible_hostname }}"
    - 127.0.0.1
    - ::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>"
Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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