INFO-mysql数据源测试连接始终失败

适用模块

easymetahub

具体说明

登记的mysql数据源测试连接始终失败,查看后端日志报错如下:

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 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.jdbc.Util.handleNewInstance(Util.java:403)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
        at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:626)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1007)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2189)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2220)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2015)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:768)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.GeneratedConstructorAccessor142.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:385)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:323)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at com.netease.music.da.metahub.connector.mysql8.service.MysqlConnectorTableService.doCheckDataSourceConnCheck(MysqlConnectorTableService.java:481)
        at com.netease.music.da.metahub.connector.mysql8.service.MysqlConnectorTableService.checkConn(MysqlConnectorTableService.java:460)
        ... 9 more
Caused by: java.net.SocketTimeoutException: Read timed out
        at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
        at java.net.SocketInputStream.read(SocketInputStream.java:171)
        at java.net.SocketInputStream.read(SocketInputStream.java:141)
        at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101)
        at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
        at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174)
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2966)
        at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:561)
        ... 25 more

排查过程:

1、校验网络连通性：telnet ip port，通的

2、jdbc连接串添加参数：?useSSL=false&socketTimeout=500000&connectTimeout=500000&serverTimezone=Asia/Shanghai，仍然连接失败，报错无改变

联系客户排查数据库侧日志，发现：

因为mysql默认会反向解析DNS，对于访问者Mysql不会判断是hosts还是ip都会进行dns反向解析，频繁地查询数据库和权限检查，这大大增加了数据库的压力，导致数据库连接缓慢，严重的时候出现连接数据库时出错的情况

使用示例

两种方式：

1、mysql配置里开启下这个配置，然后重启下mysql进程

[mysqld]

skip-name-resolve

2、把平台的ip加到mysql服务器的host

作者：潘晓宇