blocks|key|2102836|text|你需要一个接一个地杀死他们，MySQL没有任何大规模的kill命令。您可以用任何语言编写脚本，例如，在PHP中，您可以使用如下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2102837|$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);
++}
}|code-block|syntax|javascript|2102838|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
 $process_id=$row["Id"];
 if ($row["Time"] &gt; 200 ) {
 $sql="KILL $process_id";
 mysql_query($sql);
 }
}
</code></pre>

blocks|key|694566|text|批量杀操作可节省时间。在MySql中执行此操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|694567|运行这些命令|694568|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;|code-block|syntax|javascript|694569|Reference|694570|---------edit------------|694571|如果您不想存储在文件中，请存储在variable中|CODE|694572|只需在命令提示符下运行即可|694573|>+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")|694574|entityMap|0|LINK|mutability|MUTABLE|url|http://www.mysqlperformanceblog.com/^0|0|3|0|0|0|0|9|0|0|0|G|8|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|18|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|19|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1A|8|@]|D|@$9|1B|A|1C|1|1D]]|E|$]]|$1|O|3|P|5|J|7|1E|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1F|8|@$9|1G|A|1H|B|S]]|D|@]|E|$]]|$1|T|3|U|5|6|7|1I|8|@]|D|@]|E|$]]|$1|V|3|W|5|J|7|1J|8|@]|D|@]|E|$K|L]]|$1|X|3|-4|5|6|7|1K|8|@]|D|@]|E|$]]]|Y|$Z|$5|10|11|12|E|$13|14]]]]

Mass killing operation saves time. Do it in MySql itself:

Run these commands

<pre><code>mysql&gt; select concat('KILL ',id,';') from information_schema.processlist
where user='root' and time &gt; 200 into outfile '/tmp/a.txt';

mysql&gt; source /tmp/a.txt;
</code></pre>

<a href="http://www.mysqlperformanceblog.com/" rel="noreferrer">Reference</a>

<pre><code>---------edit------------
</code></pre>

if you do not want to store in file, store in a <code>variable</code>

Just run in your command prompt

<pre><code>&gt; out1=$(mysql -B test -uroot -proot --disable-column-names -e "select concat('KILL ',id,';') from information_schema.processlist where user='root' and time &gt; 200;")

&gt; out2= $(mysql -B test -uroot -proot --disable-column-names -e "$out1")
</code></pre>

blocks|key|2102904|text|我还搜索了如何通过MySQL解析命令SHOW+PROCESSLIST，并以Shell中的一行代码结束：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2102905|mysqladmin+processlist+-u+<USERNAME>+-p<PASSWORD>+%7C+\
awk+'$2+~+/%5E[0-9]/+{print+"KILL+"$2";"}'+%7C+\
mysql+-u+<USERNAME>+-p<PASSWORD>|code-block|syntax|javascript|2102906|2102907|mysqladmin+processlist将打印一个包含线程ids的表；|unordered-list-item|2102908|awk将从第二列中仅解析数字(线程ids)并生成MySQL+KILL命令；|2102909|以及最后一次调用mysql将执行传递的命令。|2102910|2102911|您可以在awk命令之前运行grep来过滤特定的数据库名称。|2102912|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|W|8|@]|9|@]|A|$]]|$1|K|3|L|5|J|7|X|8|@]|9|@]|A|$]]|$1|M|3|N|5|J|7|Y|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|10|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|S|$]]

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

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

<ul>
<li>mysqladmin processlist will print a table with the thread ids;</li>
<li>awk will parse from the second column only the numbers (thread ids) and generate MySQL KILL commands;</li>
<li>and finally the last call to mysql will execute the passed commands.</li>
</ul>

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

blocks|key|613587|text|或者..。在外壳中..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|613588|service+mysql+restart|code-block|syntax|javascript|613589|是的，我知道，我很懒，但这也很方便。|613590|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Or... in shell...

<pre><code>service mysql restart
</code></pre>

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

blocks|key|613675|text|仅适用于mariaDB的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|613676|没有比这更简单的了，只需在mysql提示符中执行此命令即可。|offset|length|style|BOLD|613677|kill+USER+username;|code-block|syntax|javascript|613678|它将杀死所提供用户名下的所有进程。因为大多数人在所有用途上都使用同一个用户，所以它是有效的！|613679|我已经在MariaDB上测试过了，对mysql不太确定。|613680|entityMap^0|0|0|U|0|0|0|1A|0|0|S|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|W|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@$D|11|E|12|F|G]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

Only for mariaDB
It doesn't get simpler then this, Just execute this in mysql prompt.
<pre><code>kill USER username;
</code></pre>
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.

blocks|key|694851|text|我最近需要这样做，于是我想到了这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|694852|--+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;|code-block|syntax|javascript|694853|这样，您就不需要存储到文件中并使用单个命令运行所有查询。|694854|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I recently needed to do this and I came up with this

<pre><code>-- 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;
</code></pre>

That way, you don't need to store to file and run all queries with a single command.

blocks|key|694958|text|下面将创建一个简单的存储过程，该过程使用游标逐个杀死除当前正在使用的进程之外的所有进程：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|694959|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+;|code-block|syntax|javascript|694960|它可以与SELECTs一起运行，以显示前后的进程，如下所示：|offset|length|style|CODE|694961|SELECT+*+FROM+information_schema.processlist;
CALL+kill_other_processes();
SELECT+*+FROM+information_schema.processlist;|694962|entityMap^0|0|0|4|6|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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:

<pre><code>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 &lt;&gt; CONNECTION_ID() THEN
 KILL proc_id;
 END IF;

 END LOOP proc_id_cursor_loop;
 CLOSE proc_id_cursor;
END$$

DELIMITER ;
</code></pre>

It can be run with <code>SELECT</code>s to show the processes before and after as follows:

<pre><code>SELECT * FROM information_schema.processlist;
CALL kill_other_processes();
SELECT * FROM information_schema.processlist;
</code></pre>

blocks|key|613504|text|如果您没有information_schema：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|613505|mysql+-e+"show+full+processlist"+%7C+cut+-f1+%7C+sed+-e+'s/%5E/kill+/'+%7C+sed+-e+'s/$/;/'+;++>+/tmp/kill.txt
mysql>+.+/tmp/kill.txt|code-block|syntax|javascript|613506|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you don't have information_schema:

<pre><code>mysql -e "show full processlist" | cut -f1 | sed -e 's/^/kill /' | sed -e 's/$/;/' ; &gt; /tmp/kill.txt
mysql&gt; . /tmp/kill.txt
</code></pre>

blocks|key|694719|text|取消所有SELECT查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|694720|select+concat('KILL+',id,';')+
from+information_schema.processlist+
where+user='root'+
++and+INFO+like+'SELECT%25'+into+outfile+'/tmp/a.txt';+
source+/tmp/a.txt;|code-block|syntax|javascript|694721|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

KILL ALL SELECT QUERIES 

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

blocks|key|1916659|text|我把bash和mysql结合起来：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1916660|for+i+in+$(mysql+-Ne+"select+id+from+information_schema.processlist+where+user+like+'foo%25user'+and+time+>+300;");+do
++mysql+-e+"kill+${i}"
done|code-block|syntax|javascript|1916661|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I'd combine bash and mysql:

<pre><code>for i in $(mysql -Ne "select id from information_schema.processlist where user like 'foo%user' and time &gt; 300;"); do
 mysql -e "kill ${i}"
done
</code></pre>

blocks|key|613635|text|这段代码适用于我(MySQL服务器5.5)杀死所有MySQL进程：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|613636|mysql+-e+"show+full+processlist;"+-ss+%7C+awk+'{print+"KILL+"$1";"}'%7C+mysql|code-block|syntax|javascript|613637|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>mysql -e "show full processlist;" -ss | awk '{print "KILL "$1";"}'| mysql
</code></pre>

blocks|key|1916634|text|我们可以通过MySQL工作台来完成这项工作。只需执行以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1916635|kill+id;|code-block|syntax|javascript|1916636|示例：|1916637|kill+13412|1916638|这将删除它。|1916639|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>kill id;
</code></pre>

Example:

<pre><code>kill 13412
</code></pre>

That will remove it.

blocks|key|1914230|text|以管理员身份登录Mysql：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1914231|+mysql+-uroot+-ppassword;|code-block|syntax|javascript|1914232|然后运行命令：|1914233|mysql>+show+processlist;|1914234|您将得到如下所示的内容：|1914235|%2B----%2B-------------%2B--------------------%2B----------%2B---------%2B------%2B-------%2B------------------%2B
%7C+Id+%7C+User++++++++%7C+Host+++++++++++++++%7C+db+++++++%7C+Command+%7C+Time+%7C+State+%7C+Info+++++++++++++%7C
%2B----%2B-------------%2B--------------------%2B----------%2B---------%2B------%2B-------%2B------------------%2B
%7C+49+%7C+application+%7C+192.168.44.1:51718+%7C+XXXXXXXX+%7C+Sleep+++%7C++183+%7C+++++++%7C+NULL+++++++++++++%7C%7C
%7C+55+%7C+application+%7C+192.168.44.1:51769+%7C+XXXXXXXX+%7C+Sleep+++%7C++148+%7C+++++++%7C+NULL+++++++++++++%7C
%7C+56+%7C+application+%7C+192.168.44.1:51770+%7C+XXXXXXXX+%7C+Sleep+++%7C++148+%7C+++++++%7C+NULL+++++++++++++%7C
%7C+57+%7C+application+%7C+192.168.44.1:51771+%7C+XXXXXXXX+%7C+Sleep+++%7C++148+%7C+++++++%7C+NULL+++++++++++++%7C
%7C+58+%7C+application+%7C+192.168.44.1:51968+%7C+XXXXXXXX+%7C+Sleep+++%7C+++11+%7C+++++++%7C+NULL+++++++++++++%7C
%7C+59+%7C+root++++++++%7C+localhost++++++++++%7C+NULL+++++%7C+Query+++%7C++++0+%7C+NULL++%7C+show+processlist+%7C
%2B----%2B-------------%2B--------------------%2B----------%2B---------%2B------%2B-------%2B------------------%2B|1914236|您将看到不同连接的完整详细信息。现在您可以关闭休眠连接，如下所示：|1914237|mysql>+kill+52;
Query+OK,+0+rows+affected+(0.00+sec)|1914238|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

login to Mysql as admin:

<pre><code> mysql -uroot -ppassword;
</code></pre>

And than run command:

<pre><code>mysql&gt; show processlist;
</code></pre>

You will get something like below :

<pre><code>+----+-------------+--------------------+----------+---------+------+-------+------------------+
| 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 |
+----+-------------+--------------------+----------+---------+------+-------+------------------+
</code></pre>

You will see complete details of different connections. Now you can kill the sleeping connection as below:

<pre><code>mysql&gt; kill 52;
Query OK, 0 rows affected (0.00 sec)
</code></pre>

blocks|key|1914261|text|以下是一个您可以在不依赖操作系统的情况下执行的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1914262|步骤1:创建一个存储过程。|1914263|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+%2B+1;

++END+LOOP+the_loop;

++SELECT+num_rows,+loop_cntr;

END+$$
DELIMITER+;|code-block|syntax|javascript|1914264|步骤2:调用存储过程，为其提供要终止其进程的数据库用户的名称。如果愿意，您可以重写存储过程以根据其他条件进行筛选。|1914265|调用kill_user_processes('devdba');|offset|length|style|CODE|1914266|entityMap^0|0|0|0|0|2|U|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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

STEP 1: Create a stored procedure. 

<pre><code>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 ;
</code></pre>

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.

CALL <code>kill_user_processes('devdba');</code>

blocks|key|2103139|text|mysqladmin+pr+-u+'USERNAME'+-p'PASSWORD'+%7C+awk+'$2~/%5E[0-9]%2B/{print+$2}'+%7C+xargs+-i+mysqladmin+-u+'USERNAME'+-p'PASSWORD'+kill+{}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2103140|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

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

blocks|key|2103532|text|对于python语言，您可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2103533|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+%25s'+%25+item)
++++++++++++cursor.execute('kill+%25s',+_id)
++++connection.close()|code-block|syntax|javascript|2103534|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

for python language, you can do like this

<pre><code>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') &gt; 200:
 _id = item.get('Id')
 print('kill %s' % item)
 cursor.execute('kill %s', _id)
 connection.close()
</code></pre>

blocks|key|1916470|text|一种简单的方法是重新启动mysql服务器。打开windows+Run中的"services.msc“，从列表中选择Mysql。右键单击并停止服务。然后重新启动，除一个进程(默认保留连接)外，所有进程都将被终止。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1916471|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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)

blocks|key|613446|text|#!+/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"+%7C+sed+'s/$%5E[0-9]*$.*/\1/'`;+do
++++echo+"Killing+query+${i}";
++++mysql+-u+<user>+-h+localhost+${DB}+-p<password>+-e+"kill+query+${i}";
done;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|613447|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

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

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

blocks|key|695042|text|有时我有一些僵尸mysql进程不能被杀死(使用MAMP+Pro)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|695043|首先获取所有mysql进程的列表：|695044|ps+-ax+%7C+grep+mysql|code-block|syntax|javascript|695045|接下来，用(用上一个命令结果中的第一列替换processId+)杀死它们中的每一个：|695046|kill+-9+processId|695047|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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

First get a list of all mysql processes:

<pre><code>ps -ax | grep mysql
</code></pre>

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

<pre><code>kill -9 processId
</code></pre>

blocks|key|613720|text|下面这些对我来说很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|613721|echo+"show+processlist"+%7C+mysql+%7C+grep+-v+%5EId+%7C+awk+'{print+$1}'+%7C+xargs+-i+echo+"KILL+{};+%7C+mysql"|code-block|syntax|javascript|613722|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The following worked great for me:

<pre class="lang-shell prettyprint-override"><code>echo "show processlist" | mysql | grep -v ^Id | awk '{print $1}' | xargs -i echo "KILL {}; | mysql"
</code></pre>

blocks|key|2103459|text|我使用flush+tables命令杀死了所有不活跃的连接，这实际上是质量问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2103460|entityMap^0|3|C|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

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

blocks|key|695285|text|如果正在运行的进程不是您的进程，则Kill不起作用。我合并了其中的一些解决方案，并对它们进行了扩展，以创建一个更简单的解决方案(至少对我来说是这样)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|695286|m=mysql+-p+$password+-h+$host+-u$user+#you+can+also+inline+it+of+course+
for+i+in+`$m+-e+"show+processlist"+%7C+awk+'/$anySearchString/+{print+$1}'`;+do+$m+-e+"call+mysql.rds_kill($i);";+done|code-block|syntax|javascript|695287|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Kill does not work if the running process is not yours. I merged some of these solutions and extended them to create a more simple one (at least to me).
<pre><code>m=mysql -p $password -h $host -u$user #you can also inline it of course 
for i in `$m -e &quot;show processlist&quot; | awk '/$anySearchString/ {print $1}'`; do $m -e &quot;call mysql.rds_kill($i);&quot;; done
</code></pre>

blocks|key|695236|text|如果你使用的是laravel，那么这是为你准备的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|695237|$query+=+"SHOW+FULL+PROCESSLIST";
++++$results+=+DB::select(DB::raw($query));

++++foreach($results+as+$result){
++++++++if($result->Command+==+"Sleep"){
++++++++++++$sql="KILL+".+$result->Id;
++++++++++++DB::select(DB::raw($sql));
++++++++}
++++}|code-block|syntax|javascript|695238|当然，您应该在名称空间之后使用此use+Illuminate\Support\Facades\DB;。|offset|length|style|CODE|695239|entityMap^0|0|0|G|Y|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you are using laravel then this is for you: 

<pre><code>$query = "SHOW FULL PROCESSLIST";
 $results = DB::select(DB::raw($query));

 foreach($results as $result){
 if($result-&gt;Command == "Sleep"){
 $sql="KILL ". $result-&gt;Id;
 DB::select(DB::raw($sql));
 }
 }
</code></pre>

Of-course, you should use this <code>use Illuminate\Support\Facades\DB;</code> after your namespace.

blocks|key|613441|text|查询1+select+concat('KILL+'，id，';')+from+information_schema.processlist+where+user='username‘into+outfile+'/tmp/a.txt'；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|613442|查询2源a.txt|613443|这将使您能够按特定用户终止show+processlist中的所有查询。|613444|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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

Query 2
source a.txt

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

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

How do I kill all the processes in Mysql "show processlist"?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

因为我在那里看到了很多进程，"time“列显示了所有进程的较大值。

问如何杀死Mysql show processlist中的所有进程？
EN

回答 24

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何杀死Mysql show processlist中的所有进程？EN

回答 24

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何杀死Mysql show processlist中的所有进程？
EN