错误提示:The server requested authentication method unknown to the client [caching_sha2_password]
一原因
从MySQL 8.0.4开始,MySQL服务器的默认身份验证插件从mysql_native_password更改为caching_sha2_password。相应地,libmysqlclient现在也将caching_sha2_password用作默认身份验证机制。具体可看官方解释:Mysql管方说明文档
解决办法
从MySQL 8.0.4开始,MySQL服务器的默认身份验证插件从mysql_native_password更改为caching_sha2_password。相应地,libmysqlclient现在也将caching_sha2_password用作默认身份验证机制。
$ mysql -u root -p
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT Host, User, plugin from user;
+-----------+------------------+-----------------------+
| Host | User | plugin |
+-----------+------------------+-----------------------+
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+-----------+------------------+-----------------------+
————————————————
将caching_sha2_password修改为mysql_native_password即可,如下:
```c
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
mysql> SELECT Host, User, plugin from user;
+-----------+------------------+-----------------------+
| Host | User | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+-----------+------------------+-----------------------+
测试连接代码
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
// 创建连接
$conn = new mysqli($servername, $username, $password);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
?>