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

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

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.

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;

Related