MySQL 如何杀死Mysql“show processlist”中的所有进程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1903838/
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 do I kill all the processes in Mysql "show processlist"?
提问by TIMEX
Because I see a lot of processes there, and the "time" column shows big values for all of them.
因为我在那里看到了很多流程，并且“时间”列显示了所有流程的大值。
采纳答案by Michal ?iha?
You need to kill them one by one, MySQL does not have any massive kill command. You can script it in any language, for example in PHP you can use something like:
你需要一个一个的杀掉，MySQL没有任何海量的杀戮命令。您可以使用任何语言编写脚本，例如在 PHP 中，您可以使用以下内容：
$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
$process_id=$row["Id"];
if ($row["Time"] > 200 ) {
$sql="KILL $process_id";
mysql_query($sql);
}
}
回答by Angelin Nadar
Mass killingoperation saves time. Do it in MySql itself:
批量杀戮操作节省时间。在 MySql 本身中执行此操作：
Run these commands
运行这些命令
mysql> select concat('KILL ',id,';') from information_schema.processlist
where user='root' and time > 200 into outfile '/tmp/a.txt';
mysql> source /tmp/a.txt;
---------edit------------
if you do not want to store in file, store in a variable
如果您不想存储在文件中，请存储在 variable
Just run in your command prompt
只需在您的命令提示符下运行
> out1=$(mysql -B test -uroot -proot --disable-column-names -e "select concat('KILL ',id,';') from information_schema.processlist where user='root' and time > 200;")
> out2= $(mysql -B test -uroot -proot --disable-column-names -e "$out1")
回答by m8t
I have also searched how to parse through MySQL the command SHOW PROCESSLIST and ended with a one-liner in a Shell:
我还搜索了如何通过 MySQL 解析命令 SHOW PROCESSLIST 并以 Shell 中的单行结尾：
mysqladmin processlist -u <USERNAME> -p<PASSWORD> | \
awk ' ~ /^[0-9]/ {print "KILL "";"}' | \
mysql -u <USERNAME> -p<PASSWORD>
- mysqladmin processlistwill print a table with the thread ids;
- awkwill parse from the second column only the numbers (thread ids) and generate MySQL KILL commands;
- and finally the last call to mysqlwill execute the passed commands.
- mysqladmin processlist将打印一个带有线程 ID 的表；
- awk将仅从第二列解析数字（线程 ID）并生成 MySQL KILL 命令；
- 最后对mysql的最后一次调用将执行传递的命令。
You can run grepbefore the awkcommand to filter a particular database name.
您可以在awk命令之前运行grep来过滤特定的数据库名称。
回答by EcchiOli
Or... in shell...
或者……在外壳中……
service mysql restart
Yeah, I know, I'm lazy, but it can be handy too.
是的，我知道，我很懒，但它也很方便。
回答by Maulik Gangani
It doesn't get simpler then this, Just execute this in mysql prompt.
没有比这更简单的了，只需在 mysql 提示符下执行它。
kill USER username;
It will kill all process under provided username. because most of the people use same user for all purpose, it works!
它将杀死提供的用户名下的所有进程。因为大多数人出于各种目的使用同一个用户，所以它有效！
I have tested this on MariaDB not sure about mysql.
我已经在 MariaDB 上测试过这个，不确定 mysql。
回答by Iberê
I recently needed to do this and I came up with this
我最近需要这样做，我想出了这个
-- GROUP_CONCAT turns all the rows into 1
-- @q:= stores all the kill commands to a variable
select @q:=GROUP_CONCAT(CONCAT('KILL ',ID) SEPARATOR ';')
FROM information_schema.processlist
-- If you don't need it, you can remove the WHERE command altogether
WHERE user = 'user';
-- Creates statement and execute it
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
That way, you don't need to store to file and run all queries with a single command.
这样，您无需存储到文件并使用单个命令运行所有查询。
回答by Steve Chambers
The following will create a simple stored procedure that uses a cursor to kill all processes one by one except for the process currently being used:
下面将创建一个简单的存储过程，该过程使用游标将除当前正在使用的进程之外的所有进程一一杀死：
DROP PROCEDURE IF EXISTS kill_other_processes;
DELIMITER $$
CREATE PROCEDURE kill_other_processes()
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE proc_id INT;
DECLARE proc_id_cursor CURSOR FOR SELECT id FROM information_schema.processlist;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN proc_id_cursor;
proc_id_cursor_loop: LOOP
FETCH proc_id_cursor INTO proc_id;
IF finished = 1 THEN
LEAVE proc_id_cursor_loop;
END IF;
IF proc_id <> CONNECTION_ID() THEN
KILL proc_id;
END IF;
END LOOP proc_id_cursor_loop;
CLOSE proc_id_cursor;
END$$
DELIMITER ;
It can be run with SELECTs to show the processes before and after as follows:
可以用SELECTs运行，显示之前和之后的过程如下：
SELECT * FROM information_schema.processlist;
CALL kill_other_processes();
SELECT * FROM information_schema.processlist;
回答by mikesl
If you don't have information_schema:
如果您没有 information_schema：
mysql -e "show full processlist" | cut -f1 | sed -e 's/^/kill /' | sed -e 's/$/;/' ; > /tmp/kill.txt
mysql> . /tmp/kill.txt
回答by user3269995
KILL ALL SELECT QUERIES
杀死所有选择查询
select concat('KILL ',id,';')
from information_schema.processlist
where user='root'
and INFO like 'SELECT%' into outfile '/tmp/a.txt';
source /tmp/a.txt;
回答by Suresh Kamrushi
login to Mysql as admin:
以管理员身份登录Mysql：
mysql -uroot -ppassword;
And than run command:
然后运行命令：
mysql> show processlist;
You will get something like below :
你会得到如下内容：
+----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+--------------------+----------+---------+------+-------+------------------+
| 49 | application | 192.168.44.1:51718 | XXXXXXXX | Sleep | 183 | | NULL ||
| 55 | application | 192.168.44.1:51769 | XXXXXXXX | Sleep | 148 | | NULL |
| 56 | application | 192.168.44.1:51770 | XXXXXXXX | Sleep | 148 | | NULL |
| 57 | application | 192.168.44.1:51771 | XXXXXXXX | Sleep | 148 | | NULL |
| 58 | application | 192.168.44.1:51968 | XXXXXXXX | Sleep | 11 | | NULL |
| 59 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+--------------------+----------+---------+------+-------+------------------+
You will see complete details of different connections. Now you can kill the sleeping connection as below:
您将看到不同连接的完整详细信息。现在您可以终止睡眠连接，如下所示：
mysql> kill 52;
Query OK, 0 rows affected (0.00 sec)
