How to find MySQL process list and to kill those processes?
MysqlKill ProcessMysql Problem Overview
The MySQL database hangs, due to some queries.
How can I find the processes and kill them?
Mysql Solutions
Solution 1 - Mysql
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 withKILL
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.
Solution 2 - Mysql
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 - Mysql
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
Solution 4 - Mysql
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
Solution 5 - Mysql
On RDS:
SELECT
concat('CALL mysql.rds_kill(',id,');')
FROM information_schema.processlist
ORDER BY time;