用Ansible mysql_user模块设置的root @ localhost密码不起作用

我有以下的剧本：

- hosts: myserver vars: mysql_root_password: foobarbaz [...] tasks: [...] - name: update mysql root password for all root accounts mysql_user: name=root host={{ item }} password={{ mysql_root_password }} priv=*.*:ALL,GRANT with_items: - "{{ ansible_hostname }}" - 127.0.0.1 - ::1 - "localhost" become: true tags: mysql [...] # I've ommitted the tasks to install the mysql packages, # store the password in /root/.my.cnf and restart the server)

问题是所需的密码正确保存在mysql.user中，为127.0.0.1，:: 1和主机名， 但不是本地主机，即

 mysql> select host,user,authentication_string from user; +-----------+------------------+-------------------------------------------+ | host | user | authentication_string | +-----------+------------------+-------------------------------------------+ | localhost | root | | | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | debian-sys-maint | *DA57FEBA9C5C5119186DB8834C7B83216E450117 | | ubuntu | root | *8C5206E23A3B76002AA6E152691F5C5D7ABC31F9 | | 127.0.0.1 | root | *8C5206E23A3B76002AA6E152691F5C5D7ABC31F9 | | ::1 | root | *8C5206E23A3B76002AA6E152691F5C5D7ABC31F9 | +-----------+------------------+-------------------------------------------+

哪里* 8C52 …是encryption密码：

 mysql> select password('foobarbaz'); +-------------------------------------------+ | password('foobarbaz') | +-------------------------------------------+ | *8C5206E23A3B76002AA6E152691F5C5D7ABC31F9 | +-------------------------------------------+

所以这个失败了：

 william@ubuntu:/etc/mysql$ mysql -u root --password=foobarbaz mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1698 (28000): Access denied for user 'root'@'localhost'

也如果我使用sudo，我可以使用任何密码或没有任何连接到MySQL作为根。

换句话说，这个可靠的命令不会做任何事情，但如果我使用任何其他用户或主机它的作品。

 ansible myserver -m mysql_user -a "name=root host=localhost password=foobarbaz priv=*.*:ALL,GRANT" -b myserver | SUCCESS => { "changed": true, "user": "root" }

MySQL：Ver 14.14 Distrib 5.7.15
Ubuntu 16.04.1 LTS
Ansible 2.1.2.0

原因是Ansible mysql_user模块的这个限制：

目前，只支持mysql_native_password加密密码哈希模块。

您可以在指定root密码的情况下安装MySQL软件包。

使用root密码进行安装

使用密码，root @ localhost登录名使用mysql_native_password ，它是MySQL 4.1.1中引入的密码哈希方法，其中密码长度为41个字节，以单个星号开头，而password()函数生成哈希值。

 mysql> select host,user,authentication_string,plugin from mysql.user; +-----------+------------------+-------------------------------------------+-----------------------+ | host | user | authentication_string | plugin | +-----------+------------------+-------------------------------------------+-----------------------+ | localhost | root | *9B500343BC52E2911172EB52AE5CF4847604C6E5 | mysql_native_password | | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | | localhost | debian-sys-maint | *DA57FEBA9C5C5119186DB8834C7B83216E450117 | mysql_native_password | +-----------+------------------+-------------------------------------------+-----------------------+

没有root密码的安装

没有密码，root @ localhost使用auth_socket插件：

 mysql> select host,user,authentication_string,plugin from mysql.user; +-----------+------------------+-------------------------------------------+-----------------------+ | host | user | authentication_string | plugin | +-----------+------------------+-------------------------------------------+-----------------------+ | localhost | root | | auth_socket | | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | | localhost | debian-sys-maint | *DA57FEBA9C5C5119186DB8834C7B83216E450117 | mysql_native_password | +-----------+------------------+-------------------------------------------+-----------------------+

auth_socket只适用于你通过unix_socket从本地主机连接的情况。它只是检查连接的用户名是否与'user'字段中的用户名匹配 ， 没有密码 。

这就是为什么你可以做sudo mysql并使用任何你喜欢的密码，或者没有，但是做一个非特权用户的mysql -u root -p永远不会工作，不管你输入什么密码。

Ansible模块当前无法设置密码，除非插件是mysql_native_password，所以您需要先正确设置插件的值。

已经有一个功能请求来解决这个问题。

解

使用密码安装MySQL，使用debconf模块提前提供（必须设置两次，确认屏幕一次）。然后安装MySQL，将密码保存到/root/.my.cnf以便root用户可以自动连接，并使用mysql_user来设置您需要的任何其他登录名。

 - hosts: myserver vars: mysql_root_password: foobarbaz tasks: - name: Specify MySQL root password before installing # without this, auth_socket will be used for root@localhost, and we won't be able to set the password 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: Install MySQL server apt: name={{ item }} state=present with_items: - mysql-server - python-mysqldb become: true - name: Start MySQL service: name=mysql state=started become: true - name: create /root/.my.cnf (from template) with password credentials template: src=/etc/ansible/templates/root/.my.cnf dest=/root/.my.cnf owner=root mode=0600 become: true - name: update mysql root password for all root accounts mysql_user: name=root host={{ item }} password={{ mysql_root_password }} sql_log_bin=yes priv=*.*:ALL,GRANT with_items: - "{{ ansible_hostname }}" - 127.0.0.1 - ::1 - "localhost" become: true

替代方案：

sudo mysql -u root -p{{ mysql_root_password }} -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{{ mysql_root_password }}'; FLUSH PRIVILEGES;"

它第一次工作，因为root仍然使用auth_socket认证插件，并忽略密码，并为后续运行，因为密码是正确的。

找到解决方案，并解释如何在这里进行调试： https : //www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket /