Secure mysql with ansible
Here's a play I use to secure the default MySQL install as per the MySQL documentation
- name: MySQL setup
hosts: dbservers
gather_facts: true
tasks:
- name: ensure mysql is running and starts on boot
service: name=mysql state=started enabled=true
# Need to do this 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=localhost password={{ root_db_password }}
- name: copy .my.cnf file with root password credentials
template: src=templates/root/.my.cnf dest=/root/.my.cnf owner=root mode=0600
- name: update mysql root password for all root accounts
mysql_user: name=root host={{ item }} password={{ root_db_password }}
with_items:
- {{ ansible_hostname }}
- 127.0.0.1
- ::1
- name: ensure anonymous users are not in the database
mysql_user: name='' host={{ item }} state=absent
with_items:
- localhost
- {{ inventory_hostname }}
- name: remove the test database
mysql_db: name=test state=absentWritten by Lorin Hochstein
Related protips
8 Responses
This can be simplified by updating all root passwords with one action but making sure that 'localhost' is the last entry. That way the second invocation of the password changing action can be eliminated:
- name: MySQL setup
hosts: dbservers
gather_facts: True
tasks:
# '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=$root_db_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 dest=/root/.my.cnf owner=root mode=0600
- name: ensure anonymous users are not in the database
mysql_user: name='' host=$item state=absent
with_items:
- localhost
- $inventory_hostname
- name: remove the test database
mysql_db: name=test state=absent
This does not seem to work when the destination for the .my.cnf file is in user's home dir
template: src=.my.cnf dest=/home/myuser/.my.cnf owner=myuser mode=0600This returns the error:
msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentialsHowever, I am able to ssh into my server and login into mysql as root ( via the /home/myuser/.my.cnf ) without any problems. It seems like ansible is failing to read the correct location of the .my.cnf file
The ansible module looks for .my.cnf in the home directory of the ssh user, specifically, it does this:
mycnf = os.path.expanduser('~/.my.cnf')Is ansible ssh'ing in as root, or are you sudo'ing?
I am having the same problem and I am sudo'ing
I had the same problem (ansible not reading user deploy's ~/.my.cnf) and copying .my.cnf to /root/.my.cnf fixed it, so it seems ansible is only looking there.
You have to start the mysql server before running mysql_user:
<br/>
- name: Start the MySQL service<br/>
service: name=mysql state=started enabled=true
</code>
I had the same error, and starting the server solves it.
@quyennt Thanks, I added your line to the tip..
I had to use 'login_host=...' rather than a simple 'host=' to get this to work. Thanks for the tips though!
