安装MySql 5.7 -设置根用户密码

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.

我最近把我的流浪汉从ubuntu/trusty-64升级到bento/ubuntu-16.04。MySQL被更新为5.7。我已经对我的剧本做了几次更新，但是当设置根用户的密码时，我总是被卡住。

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

在过去(5.7年以前)，以下是充分的:

- 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'"

“拒绝用户'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. 设置根用户mysql_root_password="的密码空白"
3. 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.
4. 试图删除根用户，然后再重新创建它。我可能会犯同样的错误，因为它试图在根用户处操作。
5. 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.
6. 手动更新mysql中的根密码。-除非我删除根用户并使用所有权限重新创建它，否则这似乎也不起作用(密码无法识别)。仅仅更新根用户密码似乎没有变化。

My Full MySQL YAML:

我的完整的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

2 个解决方案

#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. "

我能算出来。问题的要点与mysql 5.7有关，它为根用户使用auth_socket，而不提供密码。请看下面:“那个插件不在乎，也不需要密码。”它只是检查用户是否正在使用UNIX套接字连接，然后比较用户名。”

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

解决方案1:但是，在版本2.0.2中没有考虑到这一点。我可以通过在安装MySql之前设置密码来解决这个问题

- 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

然而，这也被Ansible解决了

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

解决方案2:最简单的解决方案是将Ansible升级到2.2.1

#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):

据我所知，在MySQL中，需要为localhost、服务器的主机名和127.0.0.1修改根密码，并且还需要完整的特权。以下几点可能会有帮助(注意:我只在MariaDB上测试过这个，而不是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