Ansible idempotent MySQL installation Playbook

I want to setup a MySQL server on AWS, using Ansible for the configuration management. I am using the default AMI from Amazon (ami-3275ee5b), which uses yum for package management.

When the Playbook below is executed, all goes well. But when I run it for a second time, the task Configure the root credentials fails, because the old password of MySQL doesn't match anymore, since it has been updated the last time I ran this Playbook.

This makes the Playbook non-idempotent, which I don't like. I want to be able to run the Playbook as many times as I want.

- 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

    - name: Configure the root credentials
      action: command mysqladmin -u root -p $mysql_root_password

What would be the best way to solve this, which means make the Playbook idempotent? Thanks in advance!

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.

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

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

Adding to the previous answers, I didn't want a manual step before running the command, ie I want to spin up a new server and just run the playbook without having to manually change the root password first time. I don't believe {{ mysql_password }} will work the first time, when root password is null, because mysql_password still has to be defined somewhere (unless you want to override it with -e).

So I added a rule to do that, which is ignored if it fails. This is in addition to, and appears before, any of the other commands here.

- name: Change root user password on first run
  mysql_user: login_user=root
              login_password=''
              name=root
              password={{ mysql_root_password }}
              priv=*.*:ALL,GRANT
              host={{ item }}
      with_items:
        - $ansible_hostname
        - 127.0.0.1
        - ::1
        - localhost
      ignore_errors: true