解决MySQL 8中“Public Key Retrieval is not allowed”问题
问题
从 MySQL 8 开始,身份验证插件更改为“caching_sha2_password”。因此,使用 JDBC 或其他客户端工具连接 MySQL 时,可能会遇到“Public Key Retrieval is not allowed”的问题。
1 | Public Key Retrieval is not allowed |
解决方案一
在连接 MySQL 时,设置以下参数,允许客户端自动向服务器请求公钥。1
allowPublicKeyRetrieval=true
如:1
jdbc:mysql://localhost:3306/mysql_database?useSSL=false&allowPublicKeyRetrieval=true
解决方案二
我们可以将身份验证插件更改回“mysql_native_password”来解决该问题。
- 在终端中执行以下命令以使用 root 用户登录 MySQL:
1
$ mysql -u root -p
- 进入
mysql
数据库:1
mysql> use mysql;
- 查看当前用户表中的 host 和用户信息:
1
mysql> select host, user, plugin from user;
可以看到一个结果列表,其中包含用户和对应的 host:1
2
3
4
5
6
7
8
9
10+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | hadoop | caching_sha2_password |
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.02 sec)
- 将 root 用户的身份验证插件更改为“mysql_native_password”并设置新密码。执行以下命令:
1
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Your New Password';
- 刷新权限:
1
mysql> FLUSH PRIVILEGES;
具体如下:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33mysql> use mysql;
Database changed
mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | hadoop | caching_sha2_password |
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.03 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Your New Password';
Query OK, 0 rows affected (0.08 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | hadoop | caching_sha2_password |
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.02 sec)
mysql>
小结
以上步骤完成后,您就成功将 MySQL 8 的身份验证插件更改为“mysql_native_password”并设置了新密码。现在,您应该能够使用 JDBC 或其他客户端工具连接到 MySQL 服务器,而不再遇到“Public Key Retrieval is not allowed”的问题。
希望本文对解决 MySQL 8 中的身份验证插件问题有所帮助!
参考资料
Caching SHA-2 Pluggable Authentication:
https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html
(END)