Wednesday, August 29, 2012

Fast way to kill many MySQL processes

I think everyone who works for some time with MySQL has been in trouble with suspended processes or reaching the limit of connections. The fastest way to fix it is to restart MySQL service
service mysql restart

But if it's the production server and you cannot afford restarting the service then here is the tip for you.

At first we need to find out what processes are causing troubles. Let's login to MySQL:
mysql -uroot -ptoor

And watch the list of all processes
select * from information_schema.processlist;

or
show processlist;

Let's assume we need to delete processes that are active more then 1000 seconds. To watch them we need this query:
select * from information_schema.processlist where TIME > 1000;

Let's format the output and save it to the file (if you have problems saving to the file then check this post)
select concat('kill ',id,';') 
into outfile '/home/anton/mysql/kill.sql' 
from information_schema.processlist 
where TIME > 1000;

After this let's load the created file
source /home/anton/mysql/kill.sql;

Thus we have deleted multiple MySQL processes with our own criteria.