问题

从 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”来解决该问题。

  1. 在终端中执行以下命令以使用 root 用户登录 MySQL:
    1
    $ mysql -u root -p
  1. 进入mysql数据库:
    1
    mysql> use mysql;
  1. 查看当前用户表中的 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)

  1. 将 root 用户的身份验证插件更改为“mysql_native_password”并设置新密码。执行以下命令:
    1
    mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Your New Password';
  1. 刷新权限:
    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
33
mysql> 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)