jdbc连接mysql8.x踩坑

问题描述

使用jdbc连接数据库报错密码错误，提示检查编码

Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO)
Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding'

使用手工连接数据库正常，密码没有问题。。。

问题原因

检查数据库编码/程序编码均为utf8，编码没问题，查询资料

检查用户密码加密方式为caching_sha2_password

mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host      | user             | plugin                | authentication_string                                                  |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | caching_sha2_password | xxx |
| localhost | mysql.session    | caching_sha2_password | xxx |
| localhost | mysql.sys        | caching_sha2_password | xxx |
| localhost | root             | caching_sha2_password | xxx |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+

解决方案

修改密码加密方式

alter user 'root'@'%' identified with mysql_native_password by 'pwd';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

报错1396，检查用户是否存在，发现当前用户root只有localhost的host，因此修改命令行中的host执行成功

alter user 'root'@'localhost' identified with mysql_native_password by 'pwd';

修改服务器配置my.cnf，重启服务即可

[mysqld]
default_authentication_plugin=mysql_native_password

设置SSL连接

jdbc连接指定CA证书路径，过程较为复杂，详情可以查看官方文档：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html
如果是本地测试用直接选择上面一个方案改一下密码加密方式即可