title: Ansible Install MySql 5.7 - Set Root User Password
body: <p>I've recently upgraded my vagrant from <strong>ubuntu/trusty-64</strong> to <strong>bento/ubuntu-16.04</strong>. With that MySQL was updated to <strong>5.7</strong>. I've made several updates to my playbook, but I keep getting stuck when setting the root user's password. </p>
  
  <p>In the past (before 5.7) the following was sufficient: </p>
  
  <pre><code>- name: MySQL | Set the root password.
    mysql_user: 
      name=root 
      host=localhost
      password={{ mysql_root_password }}
    become: true
  </code></pre>
  
  <p>In my playbook this is tested by attempting to delete an anonymous user.</p>
  
  <pre><code>- 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 }}
  </code></pre>
  
  <p>However, now my playbook fails at this step, returning: </p>
  
  <blockquote>
    <p>"Access denied for user 'root'@'localhost'"</p>
  </blockquote>
  
  <pre><code>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! =&gt; {"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'\")"}
  </code></pre>
  
  <p>I've tried several things: </p>
  
  <ol>
  <li>Setting the password blank for root user <code>mysql_root_password=""</code></li>
  <li>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.</li>
  <li>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.</li>
  </ol>
  
  <p>My Full MySQL YAML:</p>
  
  <pre><code>---
  - 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
  </code></pre>