Secure mysql with ansible

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

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!