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?

Accepted Answer

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.

Answer 2

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;

Answer 3

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

Answer 4

For MYSQL 8.xx You can just use mysqladmin shutdown. Not sure if this works on older versions.

Example where root password is SomePass

mysqladmin -u root -pSomePass shutdown

Also you should see shutdown in /var/log/mysql/error.log

Answer 5

On RDS:

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

5 Answers