如何删除Mysql“显示processlist”中的所有进程?

Because I see a lot of processes there, and the "time" column shows big values for all of them.

因为我在那里看到了许多过程，而“时间”列显示了所有这些过程的大值。

21 个解决方案

#1

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);
  }
}

#2

Mass killing operation 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;

Reference

参考

---------edit------------

if you do not want to store in file, store in a 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")

#3

I have also searched how to parse through MySQL the command SHOW PROCESSLIST and ended with a one-liner in a Shell:

我还搜索了如何通过MySQL解析命令显示PROCESSLIST，并在Shell中以一行程序结束:

mysqladmin processlist -u <USERNAME> -p<PASSWORD> | \
awk '$2 ~ /^[0-9]/ {print "KILL "$2";"}' | \
mysql -u <USERNAME> -p<PASSWORD>

• mysqladmin processlist will print a table with the thread ids;
• mysqladmin processlist将用线程id打印一个表;
• awk will parse from the second column only the numbers (thread ids) and generate MySQL KILL commands;
• awk将只从第二列解析数字(线程id)并生成MySQL KILL命令;
• and finally the last call to mysql will execute the passed commands.
• 最后对mysql的调用将执行已传递的命令。

You can run grep before the awk command to filter a particular database name.

您可以在awk命令之前运行grep，以过滤特定的数据库名称。

#4

Or... in shell...

还是……在壳…

service mysql restart

Yeah, I know, I'm lazy, but it can be handy too.

是的，我知道，我很懒，但也很方便。

#5

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。

#6

login to Mysql as admin:

登录到Mysql作为admin:

 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)

#7

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

mysql - e“显示全部processlist”|削减f1 | sed - e ' s / ^ /杀' | sed - e ' s / /美元;/ ';> / tmp /杀死。txt mysql >。/ tmp / kill.txt

#8

KILL ALL SELECT QUERIES

杀死所有SELECT查询

select concat('KILL ',id,';') 
from information_schema.processlist 
where user='root' 
  and INFO like 'SELECT%' into outfile '/tmp/a.txt'; 
source /tmp/a.txt;

#9

Here is a solution that you can execute without relying on the operating system:

这里有一个可以在不依赖操作系统的情况下执行的解决方案:

STEP 1: Create a stored procedure.

步骤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 ;

STEP 2: Call the stored procedure giving it the name of a database user whose processes you want to kill. You could rewrite the stored procedure to filter on some other criteria if you like.

步骤2:调用存储过程，给它提供一个数据库用户的名称，该用户的进程是您想要杀死的。如果您愿意，您可以重写存储过程以过滤其他一些标准。

CALL kill_user_processes('devdba');

调用kill_user_processes(“devdba”);

#10

This snipped worked for me (MySQL server 5.5) to kill all MySQL processes :

这对我(MySQL服务器5.5)来说是很有用的，它可以杀死所有MySQL进程:

mysql -e "show full processlist;" -ss | awk '{print "KILL "$1";"}'| mysql

#11

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;

    -- build email list
    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:

它可以通过选择来显示之前和之后的过程:

SELECT * FROM information_schema.processlist;
CALL kill_other_processes();
SELECT * FROM information_schema.processlist;

#12

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.

这样，您就不需要存储到文件中，并使用一个命令来运行所有查询。

#13

mysqladmin pr -u 'USERNAME' -p'PASSWORD' | awk '$2~/^[0-9]+/{print $2}' | xargs -i mysqladmin -u 'USERNAME' -p'PASSWORD' kill {}

#14

An easy way would be to restart the mysql server.. Open "services.msc" in windows Run, select Mysql from the list. Right click and stop the service. Then Start again and all the processes would have been killed except the one (the default reserved connection)

一个简单的方法是重启mysql服务器。开放”服务。在windows运行中，从列表中选择Mysql。右键单击并停止服务。然后重新启动，除了其中一个进程(默认的保留连接)之外，所有进程都将被杀死。

#15

#! /bin/bash
if [ $# != "1" ];then
    echo "Not enough arguments.";
    echo "Usage: killQueryByDB.sh <db_name>";
    exit;
fi;

DB=${1};
for i in `mysql -u <user> -h localhost ${DB} -p<password> -e "show processlist" | sed 's/\(^[0-9]*\).*/\1/'`; do
    echo "Killing query ${i}";
    mysql -u <user> -h localhost ${DB} -p<password> -e "kill query ${i}";
done;

#16

Sometimes I have some zombies mysql processes that can't be killed (using MAMP Pro).

有时我有一些僵尸mysql进程不能被杀死(使用MAMP Pro)。

First get a list of all mysql processes:

首先获取所有mysql进程的列表:

ps -ax | grep mysql

Next kill every one of them with (replace processId with the first column in previous command result):

然后用(用之前命令结果中的第一列替换processId):

kill -9 processId

#17

The following worked great for me:

下面的工作对我来说很有用:

echo "show processlist" | mysql | grep -v ^Id | awk '{print $1}' | xargs -i echo "KILL {}; | mysql"

#18

I used the command flush tables to kill all inactive connections which where actually the mass problem.

我使用命令冲洗表来杀死所有不活跃的连接，这些连接实际上是质量问题。

#19

We can do it by MySQL Workbench. Just execute this:

我们可以用MySQL工作台。执行这个:

kill id;

Example:

例子:

kill 13412

That will remove it.

这将删除它。

#20

for python language, you can do like this

对于python语言，您可以这样做。

import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             db='mysql',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:
    cursor.execute('SHOW PROCESSLIST')
    for item in cursor.fetchall():
        if item.get('Time') > 200:
            _id = item.get('Id')
            print('kill %s' % item)
            cursor.execute('kill %s', _id)
    connection.close()

#21

Query 1 select concat('KILL ',id,';') from information_schema.processlist where user='username' into outfile '/tmp/a.txt';

查询1从information_schema中选择concat('KILL '，id，';')。用户='用户名'到outfile '/tmp/a.txt'的processlist;

Query 2 source a.txt

查询2源a.txt

This will enable you to kill all the queries in show processlist by specific user.

这将使您能够通过特定的用户杀死show processlist中的所有查询。