在PHP 7.x使用如下代码连接MySQL 8.x
|
1 2 3 |
<?php mysqli_connect('localhost:3306','user','password','database') or die('Error connecting to MySQL server.') ?> |
结果出现如下错误信息
|
1 |
Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in xx.php |
原因为 MySQL 支持 caching_sha2_password / 用户名密码 两种验证方式，但是从 MySQL 8 开始默认使用caching_sha2_password 的验证方式，导致以前版本的客户端不能成功连接。更详细的解释如下：
The server validates the user and returns the connection status. MySQL uses caching_sha2_password and auth_socket plugins for validation.
The caching_sha2_password plugin uses an SHA-2 algorithm with 256-bit password encryption. MySQL 8 prefers this auth method.
Whereas the auth_socket plugin checks if the socket username matches with the MySQL username. If the names don’t match, it checks for the socket username of the mysql.user. If a match is found, the plugin permits the connection.
But to serve the pre 8.0 clients and avoid compatibility errors, it is preferred to revert back the auth method. Older versions of MySQL use mysql_native_password plugin for validation.
解决方案为修改用户默认的认证方式，如下：
|
1 |
ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; |
参考链接
[RESOLVED] MySQL the server requested authentication method unknown to the client