java连接mysql数据库useSSL问题及解决_Java

背景

java spring程序，java版本 java version "1.8.0_301"。

jdbc驱动版本5.1.40 (mysql-connector-java-5.1.40.jar) ，连接 proxysql 代理a 没有问题，直连接代理之后的mysql数据库实例b 报错

proxysql 版本

mysql> show variables like '%version%';
+----------------------+---------------------+
| variable_name        | value               |
+----------------------+---------------------+
| admin-version        | 2.0.13-107-g91737e0 |
| mysql-server_version | 5.5.30              |
+----------------------+---------------------+
2 rows in set (0.00 sec)

mysql数据库实例b 版本

welcome to the mysql monitor.  commands end with ; or \g.
your mysql connection id is 1988
server version: 5.7.27-30-log

问题根源

目前是去掉proxysql层，采用服务直接连接mysql数据库实例，在账号、密码、连接正确的情况下，切换报错

com.alibaba.druid.pool.druiddatasource : create connection sqlexception, url: jdbc:mysql://xxxxx:3306/uuuu?useunicode=true&characterencoding=utf-8&zerodatetimebehavior=converttonull&autoreconnect=true, errorcode 0, state 08001
com.mysql.jdbc.exceptions.jdbc4.mysqlnontransientconnectionexception: could not create connection to database server. attempted reconnect 3 times. giving up.

... ...

caused by: javax.net.ssl.sslhandshakeexception: no appropriate protocol (protocol is disabled or cipher suites are inappropriate)
   at sun.security.ssl.handshakecontext.<init>(handshakecontext.java:171)
   at sun.security.ssl.clienthandshakecontext.<init>(clienthandshakecontext.java:101)
   at sun.security.ssl.transportcontext.kickstart(transportcontext.java:238)
   at sun.security.ssl.sslsocketimpl.starthandshake(sslsocketimpl.java:394)
   at sun.security.ssl.sslsocketimpl.starthandshake(sslsocketimpl.java:373)
   at com.mysql.jdbc.exportcontrolled.transformsockettosslsocket(exportcontrolled.java:149)
   ... 25 common frames omitted

1、从错误日志中得知和ssl有关系

2、分别去确认 proxysql 和 mysql 发现， proxysql 没有开启ssl，而mysql实例是开启了 ssl的

proxysql 检查 ssl 是否开启

mysql> show variables like '%ssl%' ;
+-------------------------------------+-------+
| variable_name                       | value |
+-------------------------------------+-------+
| mysql-have_ssl                      | false |
| mysql-session_idle_show_processlist | true  |
| mysql-show_processlist_extended     | 0     |
| mysql-ssl_p2s_ca                    |       |
| mysql-ssl_p2s_cert                  |       |
| mysql-ssl_p2s_key                   |       |
| mysql-ssl_p2s_cipher                |       |
+-------------------------------------+-------+

mysql数据库检查 ssl 是否开启

mysql> show variables like '%ssl%' ;
+---------------+-----------------+
| variable_name | value           |
+---------------+-----------------+
| have_openssl  | yes             |
| have_ssl      | yes             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+

3、但是从程序的配置文件中得知是没有配置 ssl相关的

datasource.analysismaster.driverclassname=com.mysql.jdbc.driver
datasource.analysismaster.url=jdbc:mysql://xxxxx:3306/uuuu?&useunicode=true&characterencoding=utf-8&zerodatetimebehavior=converttonull&autoreconnect=true
datasource.analysismaster.username=userxxxx
datasource.analysismaster.password=passyyyy
datasource.analysismaster.maxsize=10
datasource.analysismaster.minidle=5

扩展

spring 整合 druid作为连接池

1）pom.xml 文件中添加相关依赖

<!-- mysql  -->
<dependency>
    <groupid>mysql</groupid>
    <artifactid>mysql-connector-java</artifactid>
    <version>${mysql.version}</version>
</dependency>
<!-- 连接池 -->
<dependency>
    <groupid>com.alibaba</groupid>
    <artifactid>druid</artifactid>
    <version>${druid.version}</version>
</dependency>

2）配置数据库连接，如上面的datasource 配置，一般存放项目config目录或者 apollo配置中心等

3）spring 加载druid的配置，加载方式看具体配置的存放方式

4、所以怀疑是 usessl jdbc 参数在不同实例下默认值不同导致的

5、既然这里 datasource中没有配置ssl相关，那么就看 jdbc url 中对于 usessl的默认值设置，找到官方文档说明