yez9yw
Last Updated: February 25, 2016
·
20.67K
· lorin
Me pixel

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=absent

8 Responses
Add your response

6087
3cb894b48ee4742e47a5716f74fc1320

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
over 1 year ago ·
7594
Avatar

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=0600

This returns the error:

msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials

However, 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

over 1 year ago ·
7642
Me pixel

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?

over 1 year ago ·
8247
428942eac7fea24d525accc0c229b0ee

I am having the same problem and I am sudo'ing

over 1 year ago ·
10731
06e948c99df9521afa577e2f7e59c05b

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.

over 1 year ago ·
13139
0 hc1ylgyxpfxnn1vqcx sl07ky2rvq1uqghf3l0shtdxiibunukitwxjakckxbqs43ta8ozx0p4x9

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.

over 1 year ago ·
13193
Me pixel

@quyennt Thanks, I added your line to the tip..

over 1 year ago ·
15828
525068f3a53bff91de72a0b0cc615987 normal

I had to use 'login_host=...' rather than a simple 'host=' to get this to work. Thanks for the tips though!

over 1 year ago ·