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
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 -hwuxiancheng.cn -uroot -p -P3306 mysql
- CREATE USER 'WUXIANCHENG' IDENTIFIED WITH 'mysql_native_password' BY 'YOUR_PASSWORD_IN_TRANSPARENT_TEXT';
- GRANT ALL PRIVILEGES ON *.* TO 'WUXIANCHENG'@'%';
- UPDATE `user` SET `Host`='%' WHERE `User`='root';
- ALTER USER 'root' IDENTIFIED WITH 'mysql_native_password' BY 'YOUR_PASSWORD_IN_TRANSPARENT_TEXT';
Dont' forget to replace YOUR_PASSWORD_IN_TRANSPARENT_TEXT with your own password!
PHP 7.3.0+ supports the caching_sha2_password authentication plugin. |
|
