Mysql-连接https域名的Mysql数据源踩的坑

青旅半醒 2023-09-28 08:56 34阅读 0赞

### 背景介绍 ###

大家在实际项目中，大部分都会用到关系数据库mysql，通常数据库服务器提供的都是ip的方式，所以不会出现本文涉及到的https域名的问题，本文介绍的是基于数据库服务器是分配了指定域名且有ssl证书的https 连接数据源，遇到的问题默认连接会useSSL=true这个配置 出现证书过期的问题

### 问题 ###

比如项目中用到xxl-job-admin 而xxl-job底层数据库用的是mysql，连接mysql数据源的配置如下：  
jdbc:mysql://https://xxxx/xxx/db-mysql:3306/xxl\_job?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai

以上配置使用的域名方式连接数据库的，且默认useSSL配置是打开的，即useSSL=true，这样就会有一个ssl证书可能过期的问题，过期之后会报如下错误导致连接不上数据库：

Caused by: org.apache.ibatis.exceptions.PersistenceException:

#### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. ####

#### The error may exist in class path resource \[mybatis-mapper/XxlJobLogMapper.xml\] ####

#### The error may involve com.xxl.job.admin.dao.XxlJobLogDao.findFailJobLogIds ####

#### The error occurred while executing a query ####

#### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. ####

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
        ... 9 common frames omitted

Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.  
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)  
at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:80)  
at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:67)  
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:337)  
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86)  
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)  
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)  
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)  
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)  
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)  
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)  
… 15 common frames omitted  
Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.  
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)  
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)  
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)  
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)  
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)  
at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:903)  
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)  
at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:453)  
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)  
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)  
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)  
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)  
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)  
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477)  
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:560)  
at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:115)  
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)  
at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158)  
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116)  
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)  
… 25 common frames omitted  
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.  
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)  
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)  
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)  
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)  
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)  
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)  
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)  
at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:342)  
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:167)  
at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1350)  
at com.mysql.cj.NativeSession.connect(NativeSession.java:157)  
at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:847)  
… 39 common frames omitted  
Caused by: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateExpiredException: NotAfter: Wed Jun 01 20:00:00 CST 2022  
at sun.security.ssl.Alerts.getSSLException(Alerts.java:198)  
at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1967)  
at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:331)  
at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:325)  
at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1688)  
at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:226)  
at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1082)  
at sun.security.ssl.Handshaker.process\_record(Handshaker.java:1010)  
at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1079)  
at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1388)  
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1416)  
at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1400)  
at com.mysql.cj.protocol.ExportControlled.performTlsHandshake(ExportControlled.java:317)  
at com.mysql.cj.protocol.StandardSocketFactory.performTlsHandshake(StandardSocketFactory.java:188)  
at com.mysql.cj.protocol.a.NativeSocketConnection.performTlsHandshake(NativeSocketConnection.java:97)  
at com.mysql.cj.protocol.a.NativeProtocol.negotiateSSLConnection(NativeProtocol.java:333)  
… 43 common frames omitted  
Caused by: java.security.cert.CertificateExpiredException: NotAfter: Wed Jun 01 20:00:00 CST 2022  
at sun.security.x509.CertificateValidity.valid(CertificateValidity.java:277)  
at sun.security.x509.X509CertImpl.checkValidity(X509CertImpl.java:677)  
at sun.security.x509.X509CertImpl.checkValidity(X509CertImpl.java:650)  
at com.mysql.cj.protocol.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:363)  
at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:1099)  
at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1670)  
… 54 common frames omitted

### 解决方法 ###

1、定位一下数据库是否开启了ssl,方法如下：  
show variables like ‘have%ssl%’;  
![在这里插入图片描述][66dbbf7431ef41fda552af4425f05286.png]

如上图所示，YES 代表了ssl 已经开启了，这样的话 默认客户端连接数据库都会通过ssl 认证才可以使用

2、将连接数据库的url配置中使用ssl的开关关掉，即调整后的数据源连接如下:  
jdbc:mysql://apacdlops-dev-dbi-etl-instance-new.cluster-cxu5ejbshmqy.rds.cn-northwest-1.amazonaws.com.cn:3306/xxl\_job?useUnicode=true&useSSL=false&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai

[66dbbf7431ef41fda552af4425f05286.png]: https://img-blog.csdnimg.cn/66dbbf7431ef41fda552af4425f05286.png