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

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

  2. passing the password via command line variable will store it in .bash_history file, one of the first file any “hacker” will look at. The safiest way is to create the variable in your playbook and keep the variable empty and populate it only when you run the script and erase it afther.

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