Ansible Install MySql 5.7 - Set Root User Password

Question

I've recently upgraded my vagrant from ubuntu/trusty-64 to bento/ubuntu-16.04. With that MySQL was updated to 5.7. I've made several updates to my playbook, but I keep getting stuck when setting the root user's password.

In the past (before 5.7) the following was sufficient:

- name: MySQL | Set the root password.
  mysql_user: 
    name=root 
    host=localhost
    password={{ mysql_root_password }}
  become: true

In my playbook this is tested by attempting to delete an anonymous user.

- name: MySQL | Delete anonymous MySQL server user for {{ server_hostname }}
  mysql_user: 
    name="" 
    host="{{ server_hostname }}" 
    state="absent" 
    login_user=root 
    login_password={{ mysql_root_password }}

However, now my playbook fails at this step, returning:

"Access denied for user 'root'@'localhost'"

TASK [mysql : MySQL | Delete anonymous MySQL server user for vagrant] **********
task path: /Users/jonrobinson/vagrant/survey/playbooks/roles/mysql/tasks/mysql.yml:51
fatal: [vagrant]: FAILED! => {"changed": false, "failed": true, "msg": "unable to connect to database, check login_user and login_password are correct or /home/vagrant/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}

I've tried several things:

1. Setting the password blank for root user mysql_root_password=""
2. Attempting to delete the root user then recreate it with Ansible. I get same error probably because it's trying to act at the root user.
3. Manually updating the root password in mysql. - This also doesn't appear to work (password isn't recognized) unless I delete the root user and recreate it with all the permissions. Just updating the root user password appears to have no change.

My Full MySQL YAML:

---
- name: MySQL | install mysql packages
  apt: pkg={{ item }} state=installed
  become: true
  with_items:    
   - mysql-client
   - mysql-common
   - mysql-server
   - python-mysqldb

- name: MySQL | create MySQL configuration file
  template:
    src=my.cnf.j2
    dest=/etc/mysql/my.cnf
    backup=yes
    owner=root
    group=root
    mode=0644
  become: true

- name: MySQL | create MySQLD configuration file
  template:
    src=mysqld.cnf.j2
    dest=/etc/mysql/conf.d/mysqld.cnf
    backup=yes
    owner=root
    group=root
    mode=0644
  become: true

- name: MySQL | restart mysql
  service: name=mysql state=restarted
  become: true

- name: MySQL | Set the root password.
  mysql_user: 
    name=root 
    host=localhost
    password={{ mysql_root_password }}
  become: true

- name: MySQL | Config for easy access as root user
  template: src=mysql_root.my.cnf.j2 dest=/root/.my.cnf
  become: true

- name: MySQL | Config for easy access as root user
  template: src=mysql_root.my.cnf.j2 dest={{ home_dir }}/.my.cnf
  when: "'{{ user }}' != 'root'"

- name: MySQL | Delete anonymous MySQL server user for {{ server_hostname }}
  mysql_user: name="" host="{{ server_hostname }}" state="absent" login_user=root login_password={{ mysql_root_password }}

- name: MySQL | Delete anonymous MySQL server user for localhost
  mysql_user: name="" state="absent" host=localhost login_user=root login_password={{ mysql_root_password }}

- name: MySQL | Secure the MySQL root user for IPV6 localhost (::1)
  mysql_user: name="root" password="{{ mysql_root_password }}" host="::1" login_user=root login_password={{ mysql_root_password }}

- name: MySQL | Secure the MySQL root user for IPV4 localhost (127.0.0.1)
  mysql_user: name="root" password="{{ mysql_root_password }}" host="127.0.0.1" login_user=root login_password={{ mysql_root_password }}

- name: MySQL | Secure the MySQL root user for localhost domain (localhost)
  mysql_user: name="root" password="{{ mysql_root_password }}" host="localhost" login_user=root login_password={{ mysql_root_password }}

- name: MySQL | Secure the MySQL root user for {{ server_hostname }} domain
  mysql_user: name="root" password="{{ mysql_root_password }}" host="{{ server_hostname }}" login_user=root login_password={{ mysql_root_password }}

- name: MySQL | Remove the MySQL test database
  mysql_db: db=test state=absent login_user=root login_password={{ mysql_root_password }}

- name: MySQL | create application database user
  mysql_user: name={{ dbuser }} password={{ dbpass }} priv=*.*:ALL host='%' state=present login_password={{ mysql_root_password }} login_user=root

- name: MySQL | restart mysql
  service: name=mysql state=restarted
  become: true

Platinum

What is your requirement? Do you only want to setup mysql on a server? – Orinasal

I will provide you my playbook to install mysql – Orinasal

I'm trying to install MySql 5.7 on Ubuntu 16. Setting the password for root user, and if you see at the bottom of my playbook I'm creating another database user. – Platinum

Answer 1

I was able to figure it out. The gist of the problem had to do with mysql 5.7 using auth_socket for the root user when no password is provided. See the following: "That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username. "

When this is the case you cannot update the password using:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test');

And instead must use:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password='test';

Solution 1: However, Ansible, as of version 2.0.2 didn't account for this. I was able to get around this by setting the password before MySql is installed

- name: Specify MySQL root password before installing
  debconf: name='mysql-server' question='mysql-server/root_password' value='{{mysql_root_password | quote}}' vtype='password'
  become: true

- name: Confirm MySQL root password before installing
  debconf: name='mysql-server' question='mysql-server/root_password_again' value='{{mysql_root_password | quote}}' vtype='password'
  become: true

- name: MySQL | install mysql packages
  apt: pkg={{ item }} state=installed
  become: true
  with_items:    
   - mysql-client
   - mysql-common
   - mysql-server
   - python-mysqldb
  ...

However, this has also since been addressed by Ansible

Solution 2: The easiest solution is just to upgrade Ansible to 2.2.1

Platinum

Answer 2

From what I understand, in MySQL, changing the root password needs to be done for localhost, the server's hostname and 127.0.0.1 and also needs full privileges. Something along the these lines may help (Note: I've only tested this on MariaDB, and not MySQL):

tasks:
  - name: Set a new root password
    mysql_user: check_implicit_admin=yes
                login_user=root
                login_password={{ mysql_root_password }}
                user=root
                password={{ NEW_mysql_root_password }}
                host={{ item }}
                priv='*.*:ALL,GRANT'
    with_items:
      - localhost
      - 127.0.0.1
      - {{ server_hostname }}
    notify:
        - restart_mariadb

handlers:
  - name: restart_mariadb
    service: name=mariadb
             state=restarted

Perspective

I just tried this. Getting: Access denied for user 'root'@'localhost'\" at the set a new root password step. I set the login_password blank. Because I believe that's what mysql 5.7 defaults to. What is your original mysql_root_password value? – Platinum

In this test, I mysql_root_password was assigned a random plaintext password, like so: mysql_root_password: "testpass" – Perspective