Install MySQL with ansible on ubuntu

When mysql-server is installed headlessly, there's no password. Therefore to make .my.cnf work, it should have a blank password line. Here's what I tested with for a .my.cnf:

[client]
user=root
password=

It's also slightly strange to put .my.cnf in your vagrant user directory as owned by root and only readable as root.

After ensuring the password was blank in .my.cnf, I was able to properly set the password for root in those four contexts. Note that it fails to run after that, since .my.cnf would need to be updated, so it fails the idempotency test.

There's a note on the ansible mysql_user module page that suggests writing the password and then writing the .my.cnf file. If you do that, you need a where clause to the mysql_user action (probably with a file stat before that).

Even more elegant is to use check_implicit_admin along with login_user and login_password. That's beautifully idempotent.

As a third way, perhaps check_implicit_admin makes it even easier.

Here's my successful playbook showing the above, tested with a few fresh servers. Kinda proud of this. Note .my.cnf is unnecessary for all of this.

---
- hosts: mysql
  vars:
    mysql_root_password: fart
  tasks:
  - name: Install MySQL
    apt: name={{ item }} update_cache=yes cache_valid_time=3600 state=present
    sudo: yes
    with_items:
    - python-mysqldb
    - mysql-server
  #- name: copy cnf
  #  copy: src=.my.cnf dest=~/.my.cnf owner=ubuntu mode=0644
  #  sudo: yes
  - name: Start the MySQL service
    sudo: yes
    service: 
      name: mysql 
      state: started
      enabled: true
  - name: update mysql root password for all root accounts
    sudo: yes
    mysql_user: 
      name: root 
      host: "{{ item }}" 
      password: "{{ mysql_root_password }}"
      login_user: root
      login_password: "{{ mysql_root_password }}"
      check_implicit_admin: yes
      priv: "*.*:ALL,GRANT"
    with_items:
      - "{{ ansible_hostname }}"
      - 127.0.0.1
      - ::1
      - localhost 

(edit- removed my.cnf)

Here is my complete working MySQL role, that might help you.

vars/main.yml :

mysql_root_pass: mypassword #MySQL Root Password

asks/main.yml :

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

 - name: Update MySQL root password for all root accounts
   mysql_user: name=root host={{ item }} password={{ mysql_root_pass }} state=present
   with_items:
     - "{{ ansible_hostname }}"
     - 127.0.0.1
     - ::1
     - localhost

 - name: Copy the root credentials as .my.cnf file
   template: src=root.cnf.j2 dest=~/.my.cnf mode=0600

 - name: Ensure Anonymous user(s) are not in the database
   mysql_user: name='' host={{ item }} state=absent
   with_items:
     - localhost
     - "{{ ansible_hostname }}"

 - name: Remove the test database
   mysql_db: name=test state=absent
   notify:
     - Restart MySQL

templates/root.cnf.j2

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

handlers/main.yml

---
 - name: Restart MySQL
   service: name=mysql state=restarted

site.yml

---
- hosts: all
  become: yes
  gather_facts: yes
  roles:
    - mysql

If you need any help, please check this github link. Thanks

On ubuntu 20.04, it requires login_unix_socket now:

- name: setting default root password
  mysql_user:
    name: root
    password: "{{ mysql_root_password }}"
    check_implicit_admin: true
    login_unix_socket: /var/run/mysqld/mysqld.sock

Cheers