。。
数据库 - 如何杀死Mysql“show processlist”中的所有进程？
因为我在那⾥看到了很多流程，⽽且＆＃34；时间＆＃34; 列显⽰所有这些值的⼤值。
TIMEX asked 2019-07-17T23:46:25Z
24个解决⽅案
192 votes
⼤规模杀戮操作可以节省 在MySql本⾝中执⾏：
运⾏这些命令
mysql> select concat('KILL ',id,';') from information_schema.processlist
where user='root' and time > 200 into outfile '/';
mysql> source /;
参考
---------edit------------
如果您不想存储在⽂件中，请存储在variable中
只需在命令提⽰符下运⾏即可zia
> 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")
Angelin Nadar answered 2019-07-17T23:47:18Z
98 votes
你需要逐个杀死它们，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);
}
}
Michal Čihař answered 2019-07-17T23:46:38Z
16 votes
我还搜索了如何通过MySQL解析命令SHOW PROCESSLIST并以Shell中的单⾏结束：
mysqladmin processlist -u -p | \
awk '$2 ~ /^[0-9]/ {print "KILL "$2";"}' | \
mysql -u -p
mysqladmin processlist将打印⼀个带有线程ID的表;
awk将从第⼆列解析数字(线程ID)并⽣成MySQL KILL命令;
最后对mysql的最后⼀次调⽤将执⾏传递的命令。
您可以在awk命令之前运⾏grep来过滤特定的数据库名称。
m8t answered 2019-07-17T23:48:09Z
12 votes
或者......在shell中......
service mysql restart
是的，我知道，我很懒，但它也很⽅便。
EcchiOli answered 2019-07-17T23:48:39Z
11 votes
它并不简单，只需在mysql提⽰符下执⾏此操作即可。
kill USER username;
它会在提供的⽤户名下杀死所有进程。 因为⼤多数⼈为了各种⽬的使⽤相同的⽤户，它的⼯作原理！我在MariaDB上测试了这个，不确定mysql。
Maulik Gangani answered 2019-07-17T23:49:16Z
5 votes
如果您没有information_schema：
mysql -e "show full processlist" | cut -f1 | sed -e 's/^/kill /' | sed -e 's/$/;/' ; > / mysql> . /
mikesl answered 2019-07-17T23:49:47Z
5 votes
以下将创建⼀个简单的存储过程，该过程使⽤游标逐个终⽌所有进程，但当前正在使⽤的进程除外：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 ;
它可以与SELECTs⼀起运⾏，以显⽰之前和之后的过程，如下所⽰：
SELECT * FROM information_schema.processlist;
CALL kill_other_processes();
SELECT * FROM information_schema.processlist;
Steve Chambers answered 2019-07-17T23:50:18Z
4 votes
以admin⾝份登录Mysql：
mysql -uroot -ppassword;
⽽不是运⾏命令：
mysql> show processlist;
您将获得如下内容：
+----+-------------+--------------------+----------+---------+------+-------+------------------+ | 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 |
+----+-------------+--------------------+----------+---------+------+-------+------------------+您将看到不同连接的完整详细信息。 现在你可以杀死睡眠连接，如下所⽰：
mysql> kill 52;
Query OK, 0 rows affected (0.00 sec)
Suresh Kamrushi answered 2019-07-17T23:51:03Z
4 votes
杀死所有选择查询
select concat('KILL ',id,';')
from information_schema.processlist
where user='root'
and INFO like 'SELECT%' into outfile '/';
source /;
user3269995 answered 2019-07-17T23:51:27Z
3 votes
这是⼀个可以在不依赖操作系统的情况下执⾏的解决⽅案：
第1步：创建存储过程。
DROP PROCEDURE IF EXISTS kill_user_processes$$
CREATE PROCEDURE `kill_user_processes`(
IN user_to_kill VARCHAR(255)
)
READS SQL DATA
BEGIN
DECLARE name_val VARCHAR(255);
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE friends_cur CURSOR FOR
SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE USER=user_to_kill; DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN friends_cur;
SELECT FOUND_ROWS() INTO num_rows;
the_loop: LOOP
FETCH friends_cur
INTO name_val;
IF no_more_rows THEN
CLOSE friends_cur;
LEAVE the_loop;
END IF;
SET @s = name_val;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT name_val;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
SELECT num_rows, loop_cntr;
END $$
DELIMITER ;
步骤2：调⽤存储过程，为其指定要杀死其进程的数据库⽤户的名称。 如果您愿意，可以重写存储过程以过滤其他⼀些条件。致电kill_user_processes('devdba');
Dan Spiegel answered 2019-07-17T23:52:12Z
3 votes
这段剪辑为我(MySQL服务器5.5)⼯作以杀死所有MySQL进程：
mysql -e "show full processlist;" -ss | awk '{print "KILL "$1";"}'| mysql
Fedir RYKHTIK answered 2019-07-17T23:52:37Z
2 votes
我最近需要这样做，我想出了这个
-- 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;
这样，您就不需要使⽤单个命令存储到⽂件并运⾏所有查询。
Iberê answered 2019-07-17T23:53:08Z
发布评论