如何杀死 MySQL 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4932503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to kill MySQL connections
提问by leora
I'm building a website with MySQL. I'm using TOAD for MySQL and suddenly I can't connect to the database as I'm getting an error:
我正在用 MySQL 建立一个网站。我正在使用 TOAD for MySQL，突然我无法连接到数据库，因为我收到一个错误：
"Too many connections"
“太多联系”
Is there any way in Toad for MySQL to view existing connections to be able to kill them or simple close all connections all together?
Toad for MySQL 中是否有任何方法可以查看现有连接以杀死它们或简单地将所有连接全部关闭？
回答by Konerak
No, there is no built-in MySQL commandfor that. There are various tools and scripts that support it, you can kill some connections manually or restart the server (but that will be slower).
不，没有内置的 MySQL 命令。有各种支持它的工具和脚本，您可以手动终止一些连接或重新启动服务器（但这会更慢）。
Use SHOW PROCESSLISTto view all connections, and KILLthe process ID's you want to kill.
使用SHOW PROCESSLIST查看所有连接，KILL进程ID是你想杀死。
You could edit the timeout setting to have the MySQL daemon kill the inactive processes itself, or raise the connection count. You can even limit the amount of connections per username, so that if the process keeps misbehaving, the only affected process is the process itself and no other clients on your database get locked out.
您可以编辑超时设置，让 MySQL 守护进程自行终止非活动进程，或增加连接计数。您甚至可以限制每个用户名的连接数量，这样如果进程一直行为不端，唯一受影响的进程就是进程本身，数据库上的其他客户端不会被锁定。
If you can't connect yourself anymore to the server, you should know that MySQL always reserves 1 extra connection for a user with the SUPERprivilege. Unless your offending process is for some reason using a username with that privilege...
如果您无法再连接到服务器，您应该知道 MySQL 始终为具有SUPER权限的用户保留 1 个额外连接。除非您的违规过程出于某种原因使用具有该权限的用户名...
Then after you can access your database again, you should fix the process (website) that's spawning that many connections.
然后在您可以再次访问您的数据库后，您应该修复产生那么多连接的进程（网站）。
回答by zloctb
mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+-------+---------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------------+------+---------+------+-------+----------------+
| 143 | root | localhost:61179 | cds | Query | 0 | init | SHOW PROCESSLIST |
| 192 | root | localhost:53793 | cds | Sleep | 4 | | NULL |
+-----+------+-----------------+------+---------+------+-------+----------------+
2 rows in set (0.00 sec)
mysql> KILL 192;
Query OK, 0 rows affected (0.00 sec)
USER 192 :
用户 192：
mysql> SELECT * FROM exept;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> SELECT * FROM exept;
ERROR 2013 (HY000): Lost connection to MySQL server during query
回答by zero8
I would recommend checking the connections to show the maximum thread connection is
我建议检查连接以显示最大线程连接是
show variables like "max_connections";
sample
样本
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 13 |
+-----------------+-------+
1 row in set
Then increase it by example
然后通过例子增加它
set global max_connections = 500;
回答by Moby Duck
While you can't kill all open connections with a single command, you can create a set of queries to do that for you if there are too many to do by hand.
虽然您无法使用单个命令终止所有打开的连接，但如果手动操作太多，您可以创建一组查询来为您执行此操作。
This example will create a series of KILL <pid>;queries for all some_user's connections from 192.168.1.1to my_db.
此示例将为KILL <pid>;所有some_user从192.168.1.1到的连接创建一系列查询my_db。
SELECT
CONCAT('KILL ', id, ';')
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE `User` = 'some_user'
AND `Host` = '192.168.1.1';
AND `db` = 'my_db';
回答by user1812597
As above mentioned, there is no special command to do it. However, if all those connection are inactive, using 'flush tables;' is able to release all those connection which are not active.
如上所述，没有特殊命令可以执行此操作。但是，如果所有这些连接都处于非活动状态，则使用“刷新表”；能够释放所有未激活的连接。
回答by David Hudman
In MySQL Workbench:
在 MySQL 工作台中：
Left-hand side navigator > Management > Client Connections
左侧导航器 > 管理 > 客户端连接
It gives you the option to kill queries and connections.
它为您提供了终止查询和连接的选项。
Note: this is not TOAD like the OP asked, but MySQL Workbench users like me may end up here
注意：这不像 OP 所要求的那样 TOAD，但像我这样的 MySQL Workbench 用户可能会在这里结束
