【已解决】 mysql的连接问题

冷月汉宫秋

最近发现，mysql数据库老是会报连接的错：
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    820
Current database: *** NONE ***


应用连接mysql时报错：
Last packet sent to the server was 0 ms ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.6.0_29]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) ~[na:1.6.0_29]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) ~[na:1.6.0_29]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513) ~[na:1.6.0_29]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) ~[mysql-connector-java-5.1.6.jar:na]
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074) ~[mysql-connector-java-5.1.6.jar:na]
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2103) ~[mysql-connector-java-5.1.6.jar:na]
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:71 ~[mysql-connector-java-5.1.6.jar:na]
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46) ~[mysql-connector-java-5.1.6.jar:na]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.6.0_29]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) ~[na:1.6.0_29]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) ~[na:1.6.0_29]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513) ~[na:1.6.0_29]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) ~[mysql-connector-java-5.1.6.jar:na]
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302) ~[mysql-connector-java-5.1.6.jar:na]
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282) ~[mysql-connector-java-5.1.6.jar:na]
        at java.sql.DriverManager.getConnection(DriverManager.java:582) ~[na:1.6.0_29]
        at java.sql.DriverManager.getConnection(DriverManager.java:154) ~[na:1.6.0_29]
        at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173) ~[spring-jdbc-3.1.2.RELEASE.jar:3.1.2.RELEASE]
        at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164) ~[spring-jdbc-3.1.2.RELEASE.jar:3.1.2.RELEASE]
        at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149) ~[spring-jdbc-3.1.2.RELEASE.jar:3.1.2.RELEASE]
        at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119) ~[spring-jdbc-3.1.2.RELEASE.jar:3.1.2.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) ~[spring-jdbc-3.1.2.RELEASE.jar:3.1.2.RELEASE]
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77) ~[spring-jdbc-3.1.2.RELEASE.jar:3.1.2.RELEASE]
        ... 43 common frames omitted
Caused by: java.net.ConnectException: Connection timed out: connect

补充：这情况时好时坏的，很不稳定，跪求高手指导！！！




注：经排查，问题已解决。。原来是复制失败，数据不同步导致的~~~

cenalulu

应该和服务器端的 wait_timeout 有关

冷月汉宫秋

回复 2# cenalulu


这个我已经重新设置过了，还是没用。。。不知道什么情况


   

cenalulu

看看mysql日志有没有意外重启。
或者有没有crontab在自动杀进程。
另外show global variables like '%timout%'把结果贴一下

冷月汉宫秋

回复 4# cenalulu


mysql> show global variables like '%timout%';
Empty set (0.00 sec)

日志信息：
130228 18:40:05 [Note] Plugin 'FEDERATED' is disabled.
130228 18:40:05 InnoDB: The InnoDB memory heap is disabled
130228 18:40:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130228 18:40:05 InnoDB: Compressed tables use zlib 1.2.3
130228 18:40:05 InnoDB: Using Linux native AIO
130228 18:40:05 InnoDB: Initializing buffer pool, size = 128.0M
130228 18:40:05 InnoDB: Completed initialization of buffer pool
130228 18:40:05 InnoDB: highest supported file format is Barracuda.
130228 18:40:05  InnoDB: Waiting for the background threads to start
130228 18:40:06 InnoDB: 1.1.8 started; log sequence number 2114298
130228 18:40:06 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130228 18:40:06 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
130228 18:40:06 [Note] Server socket created on IP: '0.0.0.0'.
130228 18:40:06 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=redismaster02-relay-bin' to avoid this problem.
130228 18:40:06 [Note] Event Scheduler: Loaded 0 events
130228 18:40:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.29-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)


日志里好像没报关于连接的错。。。补：现在是能正常连接状态！
   

小版主杀手

加大一点点连接数 超时时间调大一点 看看当时系统的进程数和状态

linuxforlive

连接数吧            

冷月汉宫秋

回复 6# 小版主杀手

连接数和timeout都改的挺大的。。。应用连接进来还是时好时坏的。。。
   

todayhero

默认的mysql一般不会出现类似的问题，我遇到过的情况，主要是下面的原因引起的。

1.网络延时，不稳定。
2.服务器过于繁忙，导致连接无法进行。


当出现上面的情况即使是加大timeout和connections会让问题更严重。

加大的请求过来无法处理又不断开，问题会更加严重。

冷月汉宫秋

回复 9# todayhero


我现在就是加大timeout和connections这两个，发现没用。。

由于我做了两台monitor（配的是同一个mmm架构）的HA，然后我想是不是这样导致连接不稳定，后来我关停了其中一台monitor，发现那个连接报错没了，数据库也恢复正常了，后来我又把关停的那台monitor起来了，发现连接到现在一直是运行正常的。。。对其中的根源很是困惑~~~~