Ansible idempotent MySQL installation Playbook
Ansible version for a secure MySQL installation.
mysql_secure_installation.yml
- hosts: staging_mysql
user: ec2-user
sudo: yes
tasks:
- name: Install MySQL
action: yum name={{ item }}
with_items:
- MySQL-python
- mysql
- mysql-server
- name: Start the MySQL service
action: service name=mysqld state=started
# 'localhost' needs to be the last item for idempotency, see
# http://ansible.cc/docs/modules.html#mysql-user
- name: update mysql root password for all root accounts
mysql_user: name=root host={{ item }} password={{ mysql_root_password }}
with_items:
- "{{ ansible_hostname }}"
- 127.0.0.1
- ::1
- localhost
- name: copy .my.cnf file with root password credentials
template: src=templates/root/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600
- name: delete anonymous MySQL server user for $server_hostname
action: mysql_user user="" host="{{ server_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
templates/root/my.cnf.j2
[client]
user=root
password={{ mysql_root_password }}
References
- The original answer by Lorin Hochstein
- https://github.com/gaspaio/ansible-devbox/blob/master/roles/mysql/tasks/server.yml
I posted about this on coderwall, but I'll reproduce dennisjac's improvement in the comments of my original post.
The trick to doing it idempotently is knowing that the mysql_user module will load a ~/.my.cnf file if it finds one.
I first change the password, then copy a .my.cnf file with the password credentials. When you try to run it a second time, the myqsl_user ansible module will find the .my.cnf and use the new password.
- hosts: staging_mysql
user: ec2-user
sudo: yes
tasks:
- name: Install MySQL
action: yum name={{ item }}
with_items:
- MySQL-python
- mysql
- mysql-server
- name: Start the MySQL service
action: service name=mysqld state=started
# 'localhost' needs to be the last item for idempotency, see
# http://ansible.cc/docs/modules.html#mysql-user
- name: update mysql root password for all root accounts
mysql_user: name=root host={{ item }} password={{ mysql_root_password }} priv=*.*:ALL,GRANT
with_items:
- "{{ ansible_hostname }}"
- 127.0.0.1
- ::1
- localhost
- name: copy .my.cnf file with root password credentials
template: src=templates/root/.my.cnf dest=/root/.my.cnf owner=root mode=0600
The .my.cnf template looks like this:
[client]
user=root
password={{ mysql_root_password }}
Edit: Added privileges as recommended by Dhananjay Nene in the comments, and changed variable interpolation to use braces instead of dollar sign.
This is an alternative solution to the one proposed by @LorinHochStein
One of my constraints was to ensure that no passwords are stored in plain text files anywhere on the server. Thus .my.cnf was not a practical proposition
Solution :
- name: update mysql root password for all root accounts from local servers
mysql_user: login_user=root
login_password={{ current_password }}
name=root
host=$item
password={{ new_password }}
priv=*.*:ALL,GRANT
with_items:
- $ansible_hostname
- 127.0.0.1
- ::1
- localhost
And in the vars file
current_password: foobar
new_password: "{{ current_password }}"
When not changing the mysql password run ansible playbook on command line as usual.
When changing the mysql password, add the following to the command line. Specifying it on the commandline allows the parameter set on the command line to take precedence over the one defaulted to in the vars file.
$ ansible-playbook ........ --extra-vars "new_password=buzzz"
After running the command change the vars file as follows
current_password=buzzz
new_password={{ current_password }}