The server requested authentication method unknown to the client-优快云博客

本文解决MySQL8.0中默认认证插件由mysql_native_password更改为caching_sha2_password导致的问题，提供了重新配置服务器以恢复到mysql_native_password的方法，并指导如何创建新用户及更改现有用户的认证插件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client.

In MySQL 8.0+, the default authentication plugin has changed from 'mysql_native_password' to 'caching_sha2_password', and the 'root'@'localhost' administrative account uses the 'caching_sha2_password' authentication plugin by default. If you prefer that the root account use the previous default authentication plugin 'mysql_native_password'.

The PDO_MySQL and ext/mysqli extensions of PHP do not support caching_sha2_password. In addition, when PHP MySQL extensions used with PHP versions before 7.1.16 and PHP 7.2 before 7.2.4, they fail to connect with default_authentication_plugin=caching_sha2_password even if caching_sha2_password is not used. However the X DevAPI PHP extension (mysql_xdevapi) supports it.

To restore MySQL pre-8.0 compatibility, you can reconfigure the server to revert to the previous default authentication plugin mysql_native_password like:

[mysqld]
default_authentication_plugin=mysql_native_password

复制代码

Restart the MySQL database, the problem should be solved, otherwise you may need to reinitialize the MySQL installation data and reinstall it.

You can connect to the MySQL database using mysql commandline utility, and then create a new user with the older mysql_native_password plugin with SQL.

mysql -uroot -p

CREATE USER 'WUXIANCHENG' IDENTIFIED WITH 'mysql_native_password' BY 'YOUR_PASSWORD_IN_TRANSPARENT_TEXT';
GRANT ALL PRIVILEGES ON *.* TO 'WUXIANCHENG'@'%';

Now you can connect to the MySQL database in PHP using the newly created account. You can also change existing users' authentication plugin after connecting to the MySQL database with the new account.

use mysql;
UPDATE `user` SET `Host`='%' WHERE `User`='root';
ALTER USER 'root' IDENTIFIED WITH 'mysql_native_password' BY 'YOUR_PASSWORD_IN_TRANSPARENT_TEXT';

If the default root user was restricted to localhost, the ALTER USER statment will fail with message like "ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'", so you have to change Host from localhost to % first.
Dont' forget to replace YOUR_PASSWORD_IN_TRANSPARENT_TEXT with your own password!

转载于:https://www.cnblogs.com/jdcai/p/9202584.html