java连接mysql数据库useSSL问题

最新推荐文章于 2024-08-01 08:23:29 发布

菩提老鹰

最新推荐文章于 2024-08-01 08:23:29 发布

阅读量2.6k

点赞数 1

分类专栏： MYSQL 文章标签：数据库 mysql java usessl

本文链接：https://blog.csdn.net/eagle5063/article/details/127616825

版权

MYSQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

spring & druid & mysql & useSSL

背景

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>