How to find MySQL process list and to kill those processes?

Question

The MySQL database hangs, due to some queries.

How can I find the processes and kill them?

score 249 · Accepted Answer · edited Mar 13 '20 at 17:21

249

Here is the solution:

Login to DB;
Run a command show full processlist;to get the process id with status and query itself which causes the database hanging;
Select the process id and run a command KILL <pid>; to kill that process.

Sometimes it is not enough to kill each process manually. So, for that we've to go with some trick:

Login to MySQL;
Run a query Select concat('KILL ',id,';') from information_schema.processlist where user='user'; to print all processes with KILL command;
Copy the query result, paste and remove a pipe | sign, copy and paste all again into the query console. HIT ENTER. BooM it's done.

edited Mar 13 '20 at 17:21

Kate Orlova

answered May 26 '17 at 01:57

Ashish Dadhich

10

That query is great! For anyone looking at this with light MySQL experience you can also filer the query by `Host`, `db`, `Command`, `Time`, `State`, or `Info`: `SELECT concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';` or `SELECT concat('KILL ',id,';') from information_schema.processlist where Time>'300';` – KareemElashmawy Feb 08 '18 at 21:01
8

Thanks for the query! I used an enhanced version that could save some manual editing time: `select group_concat(concat('KILL ',id,';') separator ' ')` so they all land on one line that can be copy-pasted – architectonic Aug 07 '18 at 10:43
1

The query is ok, but you can end up killing your own process. I would review the list of process ids before rerunning the "kill all". – Patrick.SE Feb 12 '19 at 15:20
3

Tweaking @architectonic 's comment to get me something immediately easier to handle off the clipboard (even with the pesky opening and closing double quotes) ```SELECT group_concat(concat('KILL ',id,';') SEPARATOR ' \n') AS KILL_EVERYTHING FROM information_schema.processlist;``` – leerssej Jul 10 '19 at 19:08

score 44 · Answer 2 · answered Aug 01 '19 at 10:22

select GROUP_CONCAT(stat SEPARATOR ' ') from (select concat('KILL ',id,';') as stat from information_schema.processlist) as stats;

Then copy and paste the result back into the terminal. Something like:

KILL 2871; KILL 2879; KILL 2874; KILL 2872; KILL 2866;

score 20 · Answer 3 · answered Oct 18 '18 at 23:36

20

You can do something like this to check if any mysql process is running or not:

ps aux | grep mysqld
ps aux | grep mysql

Then if it is running you can killall by using(depending on what all processes are running currently):

killall -9 mysql
killall -9 mysqld
killall -9 mysqld_safe

answered Oct 18 '18 at 23:36

Pritam Banerjee

8

The OP is asking about processes running within the MySQL server. – reinierpost Jul 08 '19 at 11:16
9

Although not what was asked for, it helped as this is the first item google returned when searching how to kill all mysqld processes on linux ;) – IncredibleHat Aug 30 '19 at 18:44
On a related note, one should always try with `kill -15` and resort to `kill -9` only if process continues to run. with `kill -15` you are giving process a chance for graceful termination. – sateesh Aug 04 '23 at 09:15

score 2 · Answer 4 · answered Jan 28 '22 at 03:04

2

On RDS:

SELECT
  concat('CALL mysql.rds_kill(',id,');')
  FROM information_schema.processlist
  ORDER BY time;

answered Jan 28 '22 at 03:04

Moshe

4 Answers4