Shell Script to auto kill mysql sleep processes

I made it.

Create kill_sleep.sh file

mysql -u<user> -p<password> -h<host> -e "select concat('KILL ',id,';')  into outfile '/tmp/sleep_processes.txt' from information_schema.processlist where Command = 'Sleep'"
mysql -u<user> -p<password> -h<host> -e "source /tmp/sleep_processes.txt;"
rm -rf /tmp/sleep_processes.txt

And set kill_sleep.sh to cron job .

Vishal's answer works well if you're running the command on the MySQL server, but it won't work if you're connecting to the server remotely or if you don't have permission to run SOURCE or SELECT ... INTO OUTFILE (eg. Amazon's RDS). It's possible to rewrite it not to rely on those features though, and then it'll work anywhere:

mysql -h<host> -u<user> -p -e "SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE Command = 'Sleep'" > sleep.txt
cat sleep.txt | xargs -I% mysql -h<host> -u<user> -p -e "%"

The syntax is:

KILL thread_id

In your case:

  mysql > KILL 3057

But in order to delete all the sleep processes,one command cant be used, you need to loop through whole processlist,after taking all the processes in tmp table and looping through it:

select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';

select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep' into outfile '/tmp/a.txt';

Referred from here