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

Solution 1:

Here is the solution:

1. Login to DB;
2. Run a command show full processlist;to get the process id with status and query itself which causes the database hanging;
3. 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:

1. Login to MySQL;
2. Run a query Select concat('KILL ',id,';') from information_schema.processlist where user='user'; to print all processes with KILL command;
3. 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.

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

Solution 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