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?

Answer 1

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 2

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 3

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 4

On RDS:

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