MySQL连接导致服务器消失，进程列表中没有任何内容(MySQL Connections causing server went away, nothing in processlist)

MySQL连接导致服务器消失，进程列表中没有任何内容(MySQL Connections causing server went away, nothing in processlist)

我有大量的连接但是当我发布show full processlist时，我没有显示任何接近我看到的连接。 这些连接孤儿是某种吗？ 我尝试了flush hosts命令并且连接仍然存在，即使重新启动服务器并重新启动mysql服务器也是如此。

我相信这些连接会导致与数据库建立新连接的问题。 用户正在收到“服务器消失”错误。 我该如何清除这些？

请参阅以下命令：

mysql> show status like '%onn%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Aborted_connects | 5 | | Connections | 11743 | | Max_used_connections | 24 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 6 | +--------------------------+-------+ 7 rows in set (0.00 sec) mysql> show full processlist; +-------+---------+----------------------+--------------------+---------+-------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+---------+----------------------+--------------------+---------+-------+-------+-----------------------+ | 4494 | rode | localhost:43411 | NULL | Sleep | 11159 | | NULL | | 4506 | rode | localhost:43423 | information_schema | Sleep | 11159 | | NULL | | 4554 | rode | localhost:43511 | performance_schema | Sleep | 11112 | | NULL | | 11500 | ass | serv:1243 | Home-Tech | Sleep | 0 | | NULL | | 11743 | root | localhost | NULL | Query | 0 | NULL | show full processlist | | 11744 | ass | out:6070 | Home-Tech | Sleep | 4 | | NULL | | 11745 | ass | out:6074 | HTGlobal | Sleep | 8 | | NULL

I have a large amount of connections but when I issue a show full processlist I am not showing anything close to the connections I see. Are these connections orphans of some sort? I tried the flush hosts command and the connections persist, even with a reboot of the server and also restarting the mysql server.

I believe these connections are causing issues with making new connections to the database. User's are getting a "server went away" error. How do I clear these?

See commands below:

mysql> show status like '%onn%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Aborted_connects | 5 | | Connections | 11743 | | Max_used_connections | 24 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 6 | +--------------------------+-------+ 7 rows in set (0.00 sec) mysql> show full processlist; +-------+---------+----------------------+--------------------+---------+-------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+---------+----------------------+--------------------+---------+-------+-------+-----------------------+ | 4494 | rode | localhost:43411 | NULL | Sleep | 11159 | | NULL | | 4506 | rode | localhost:43423 | information_schema | Sleep | 11159 | | NULL | | 4554 | rode | localhost:43511 | performance_schema | Sleep | 11112 | | NULL | | 11500 | ass | serv:1243 | Home-Tech | Sleep | 0 | | NULL | | 11743 | root | localhost | NULL | Query | 0 | NULL | show full processlist | | 11744 | ass | out:6070 | Home-Tech | Sleep | 4 | | NULL | | 11745 | ass | out:6074 | HTGlobal | Sleep | 8 | | NULL

最满意答案

The MySQL server has gone away （错误2006）有两个主要原因

服务器超时并关闭连接。 要修复，请检查my.cnf配置文件中的“wait_timeout” mysql变量是否足够大。

服务器丢弃了不正确或过大的数据包。 如果mysqld获取的数据包太大或不正确，则会假定客户端出现问题并关闭连接。 要修复，您可以增加my.cnf文件中的最大数据包大小限制“max_allowed_packet” ，例如。 设置max_allowed_packet = 128M ，然后sudo /etc/init.d/mysql restart 。

有两种主要方法可以解决这个问题。 如果上述变化没有，那么你的linux或windows mysql数据库服务器可能存在问题; 您需要增加服务器上的RAM或观察它的进程。

这是在Windows或Linux机器上？

The MySQL server has gone away (error 2006) has two main causes

Server timed out and closed the connection. To fix, check that “wait_timeout” mysql variable in your my.cnf configuration file is large enough.

Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. To fix, you can increase the maximal packet size limit “max_allowed_packet” in my.cnf file, eg. set max_allowed_packet = 128M, then sudo /etc/init.d/mysql restart.

there are two main ways to fix this. if the above change doesn't there may be an issue with your linux or windows mysql database server; you either need to increase ram on your server or watch it's process.

is this on a windows or linux box?