Ansible setup mysql root password

Question

anyone of you have an idea to build a yml for mysql updating of root password and granting privileges? I have created my playbook and on the fresh install its working as expected and no issue at all. But when I do vagrant provision again it now fails to set the root password and I'm getting an error. Below are my codes

mysql.yml

---
- name: Install the MySQL packages
  apt: name={{ item }} state=installed update_cache=yes
  with_items:
    - mysql-server
    - mysql-client
    - python-mysqldb
    - libmysqlclient-dev

- name: drop database {{ dbname }}
  mysql_db:
    name: "{{ dbname }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    state: absent
  delegate_to: "{{ dbhost }}"
  run_once: true

- name: create database {{ dbname }}
  mysql_db:
    name: "{{ dbname }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    state: present
  delegate_to: "{{ dbhost }}"
  run_once: true

- name: ensure mysql is running and starts on boot
  service: name=mysql state=started enabled=true

- name: copy .my.cnf file with root password credentials
  template: src=my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600

- name: update mysql root password for all root accounts "{{ dbpass }}"
  mysql_user: name={{ dbuser }} host={{ item }} password="{{ dbpass }}" priv="{{ dbname }}.*:ALL,GRANT"
  with_items:
    - localhost
    - 127.0.0.1

- name: grant privilege on "{{ dbname }}" to "{{ dbuser }}"
  mysql_user:
    name: "{{ item.user }}"
    host: "{{ item.host }}"
    password: "{{ dbpass }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    priv: "{{ dbname }}.*:ALL"
    state: present
  with_items:
    - { user: "{{ dbuser }}" , host: localhost }
    - { user: "{{ dbuser }}" , host: 127.0.0.1 }
  delegate_to: "{{ dbhost }}"
  run_once: true

- name: ensure anonymous users are not in the database
  mysql_user: name='' host={{ item }} state=absent
  with_items:
    - 127.0.0.1
    - localhost

- name: remove the test database
  mysql_db: name=test state=absent

my.cnf.j2

[client]
user=root
password={{ dbpass }}

defaults/main.yml

---
dbhost: localhost
dbname: mydb
dbuser: root
dbpass: root

I'm able to do everything just fine if its fresh install but running it the second time around gives me the error below

Answer 1

Seems like you update .my.cnf with new password just before you attempt to change it with the next task.

And you may want to use host_all option when updating password, because with_items run module several times, and there is a possibility of same error: change password on the first item and can't connect on the second item.

Answer 2

Already figure out the correct answer for this. So I'll be adding my answer just a reference for those having the same trouble as me

===========================================================================

---
# Install the needed package of mysql
- name: Install MySQL packages
  apt: pkg={{ item }} state=installed
  with_items:
    - bundler
    - mysql-server
    - mysql-client
    - libmysqlclient-dev
    - python-mysqldb
    - build-essential

# Update the root password immediately. This should come first as ordering
# is very important
- name: Update mysql root password for all root accounts "{{ dbpass }}"
  mysql_user: name=root password="{{ dbpass }}" priv="*.*:ALL,GRANT"

# After we update the root password we are going to use this everytime
# we do an update or create something on mysql
# we will create a copy in /root/.my.cnf as this will be use to check
# the login or root credential. Meaning this should contain the latest
# password of the root (right after we update the root password)
- name: copy .my.cnf file with root password credentials
  template: src=my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600
  notify: Restart the MySQL service

# Remove the unnecessary db for now
- name: Remove the test database
  mysql_db: name=test state=absent

# Make sure no anonymous user in the db
- name: ensure anonymous users are not in the database
  mysql_user: name='' host={{ item }} state=absent
  with_items:
    - 127.0.0.1
    - localhost

# Delete the user if its existing so that we can create the user again
- name: Delete deploy DB user
  mysql_user: name={{ dbuser }} password={{ dbpass }} state=absent
  notify: Restart the MySQL service

# Create our own user aside from the root password
# here our root password and new user created will have the same password
- name: Add deploy DB user
  mysql_user: name={{ dbuser }} password={{ dbpass }} priv=*.*:ALL,GRANT state=present
  notify: Restart the MySQL service

# Delete databases. This should not be included in production.
# this is only on local so its fine.
- name: Drop databases
  mysql_db:
    name: "{{ item }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    state: absent
  with_items:
    - db1
    - db2
    - "{{ dbname }}"
  run_once: true

# Recreate the databases
- name: Create databases
  mysql_db:
    name: "{{ item }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    state: present
  with_items:
    - db1
    - db2
    - "{{ dbname }}"
  run_once: true

# Grant the privilege for the newly created user
- name: grant privilege on "{{ dbname }}" to "{{ dbuser }}"
  mysql_user:
    name: "{{ item.user }}"
    host: "{{ item.host }}"
    password: "{{ dbpass }}"
    priv: "*.*:ALL"
    state: present
  with_items:
    - { user: "{{ dbuser }}" , host: localhost }
    - { user: "{{ dbuser }}" , host: 127.0.0.1 }