项目中数据库mysql从5.7升到8.0后启动报错:java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
经过查找文档,找到两个属性
serverRSAPublicKeyFile
File path to the server RSA public key file for sha256_password authentication. If not specified, the public key will be retrieved from the server.
Since Version 5.1.31 Allows special handshake round-trip to get an RSA public key directly from server.
Default Value false Since Version 5.1.31
serverRSAPublicKeyFile:放置服务器上的公钥文件位置
allowPublicKeyRetrieval:允许从服务器上获取公钥
Note
To authenticate accounts with the
caching_sha2_password
plugin, either a secure connection to the server using SSL or an unencrypted connection that supports password exchange using an RSA key pair (enabled by setting one or both of the connecting propertiesallowPublicKeyRetrieval
andserverRSAPublicKeyFile
) must be used.
大致意思是说使用caching_sha2_password插件的,这两个属性必须要设置一个。
If the MySQL server's default authentication method was SHA256 but neither one of the Connector/J connection properties
allowPublicKeyRetrieval
andserverRSAPublicKeyFile
was set, the authentication failed with aTransientConnectionException
, complaining that the public key could not be retrieved. With this fix, authentication continues in the situation, allowing other enabled authentication methods to be tried. (Bug #20433047, Bug #75670)
如果两个都没有设置,会抛public key could not be retrieved异常。
经过在网上搜索和查文档有两个解决方法:
1. 将allowPublicKeyRetrieval设为true,允许从服务器上获取公钥文件
jdbcUrl=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
2. 设置公钥文件位置serverRSAPublicKeyFile:
将服务器上数据目录下的public_key.pem拷至本地,连接上加上此属性 serverRSAPublicKeyFile=/data%2Fpublic_key.pem, 文件:/data/public_key.pem, %2F为/,%3A为:
jdbcUrl=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=GMT%2B8&serverRSAPublicKeyFile=/data%2Fpublic_key.pem
以上两种方法都可以解决。