How do I kill all the processes in Mysql show processlist

Question

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

User · Answer

If you don t have information schema   mysql -e  show full processlist    cut -f1   sed -e  s   kill      sed -e  s           gt   tmp kill txt mysql gt     tmp kill txt

User · Answer

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

User · Answer

Or    in shell     service mysql restart   Yeah  I know  I m lazy  but it can be handy too

User · Answer

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    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

User · Answer

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

User · Answer

Here is a solution that you can execute without relying on the operating system   STEP 1  Create a stored procedure     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   CALL kill user processes  devdba

User · Answer

login to Mysql as admin    mysql -uroot -ppassword    And than run command   mysql gt  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 gt  kill 52  Query OK  0 rows affected  0 00 sec

User · Answer

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

User · Answer

The following worked great for me   echo  show processlist    mysql   grep -v  Id   awk   print  1     xargs -i echo  KILL       mysql

User · Answer

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  lt  gt  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

User · Answer

Mass killing operation saves time  Do it in MySql itself   Run these commands  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    Reference  ---------edit------------   if you do not want to store in file  store in a variable  Just run in your command prompt   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

User · Answer

mysqladmin pr -u  USERNAME  -p PASSWORD    awk   2    0-9    print  2     xargs -i mysqladmin -u  USERNAME  -p PASSWORD  kill

User · Answer

We can do it by MySQL Workbench  Just execute this   kill id    Example   kill 13412   That will remove it

User · Answer

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

User · Answer

for python language  you can do like this  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

User · Answer

If you are using laravel then this is for you     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                      Of-course  you should use this use Illuminate Support Facades DB  after your namespace

User · Answer

This snipped worked for me  MySQL server 5 5  to kill all MySQL processes     mysql -e  show full processlist   -ss   awk   print  KILL   1       mysql

User · Answer

I d combine bash and mysql   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

User · Answer

I have also searched how to parse through MySQL the command SHOW PROCESSLIST and ended with a one-liner in a Shell   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     mysqladmin processlist will print a table with the thread ids  awk will parse from the second column only the numbers  thread ids  and generate MySQL KILL commands  and finally the last call to mysql will execute the passed commands    You can run grep before the awk command to filter a particular database name

User · Answer

Sometimes I have some zombies mysql processes that can t be killed  using MAMP Pro     First get a list of all mysql processes   ps -ax   grep mysql   Next kill every one of them with  replace processId with the first column in previous command result    kill -9 processId

User · Answer

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

User · Answer

Only for mariaDB It doesn t get simpler then this  Just execute this in mysql prompt  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

User · Answer

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

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

Examples related to mysql

Examples related to database