I am looking for a well tested bash script (or alternative solution) to do so, in order to avoid max_connection to be exhausted. I know that it is fighting the symptoms, but really need such script as a short term solution.
If you have MySQL 5.1 where the processlist is in the INFORMATION_SCHEMA, you can do this to generate the KILL QUERY commands in bulk from within the mysql client for query running longer than 20 minutes (1200 seconds):
You can do WHERE clauses against the INFO field to look for a specific query, the TIME field against long running queries, or the DB field against a specific database.
If you are root@localhost, you should have full privileges to run this as follows
You can crontab this as follows:
Here is another variation:
BTW You do not have specify a myDB since I explicit read from information_schema.processlist as a fully qualified tablename.
Here is a demonstration of what you should see. For this example, I will echo the KILL command of all processes whose time > 20000 seconds:
I have been doing this technique for the last 5 years. In fact, I submitted this answer to the DBA StackExchange last year and it got accepted.
I found the following code-snipped here:
Update 2013-01-14: There was an anonymous hint that this is potentially dangerous and can kill replication processes as well. So use at your own risk: