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=rootpassword={{ 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.

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=rootpassword={{ mysql_root_password }}

References

• The original answer by Lorin Hochstein
• https://github.com/gaspaio/ansible-devbox/blob/master/roles/mysql/tasks/server.yml

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: foobarnew_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=buzzznew_password={{ current_password }}