使用“plugin：auth_socket”更改MySQL 5.7中的用户密码

在这篇博客中，我们将讨论如何使用“plugin：auth_socket”来更改MySQL 5.7中的用户密码。在

在Debian / Ubuntu中，为root用户安装带有空密码的MySQL / Percona Server是很常见的。完成所有配置和测试后，将设置密码。这在生产服务器（或即将成为生产服务器）中不是一个好习惯，但您可以为自己的测试服务器执行此操作。关于身份验证，5.7中的情况有所改变，之前工作的方法需要不同的过程。

假设您安装5.7并且未指定密码。您将看到以下内容：

1 2 3 4 5

SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                         | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    |

密码为空。我们更改它：

1 2 3 4 5 6 7

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'test';   mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                         | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    |

这不起作用，它仍然是空的：

1 2 3 4 5 6 7

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test');   mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                         | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost |                                                                                    |

但为什么？我们来看看警告：

1 2 3 4 5 6 7

mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level   | Code | Message                                                                                                                                                                                 | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'SET PASSWORD FOR  = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR  = '' instead | | Note    | 1699 | SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it.                                                                              | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

问题出在1699号注释中。该用户使用的是不支持密码的身份验证插件。可能是哪一个

1 2 3 4 5 6 7

mysql> SELECT * from user where User="root"G *************************** 1. row ***************************                   Host: localhost                   User: root [...]                 plugin: auth_socket [...]

好的， auth_socket。如果您安装5.7并且没有为root用户提供密码，它将使用auth_socket插件。该插件不关心，也不需要密码。它只检查用户是否使用UNIX套接字进行连接，然后比较用户名。

如果我们要配置密码，我们需要在同一命令中同时更改插件并设置密码。首先更改插件然后设置密码将不起作用，它将再次回退到auth_socket。

因此，正确的方法是运行以下命令：

1	ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

 

1 2 3 4 5

mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-----------+-----------+------------------------------------------------------------------------------------+ | User      | Host      | HEX(authentication_string)                                                         | +-----------+-----------+------------------------------------------------------------------------------------+ | root      | localhost | 2A39344244434542453139303833434532413146393539464430324639363443374146344346433239 |

如果您的部署使用空密码，并在以后更改它们，请记住更新脚本！