SQLSTATE[HY000]  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:
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 -hwuxiancheng.cn -uroot -p -P3306 mysql
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. 复制代码
- CREATE USER 'WUXIANCHENG' IDENTIFIED WITH 'mysql_native_password' BY 'YOUR_PASSWORD_IN_TRANSPARENT_TEXT';
- GRANT ALL PRIVILEGES ON *.* TO 'WUXIANCHENG'@'%';
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. 复制代码
- 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.