Kill long running MySQL queries automatically using PERL script

Note: Make sure that you have perl-DBD-mysql, perl-DBI and perl installed. If those package are missing, you can use YaST, YUM, ZYPPER, APT-GET to install those missing packages based on your distro.

Step1: Create a perl script that connects to the database and runs a query to show full processlist. Then, it goes through each row of the output from show full processlist and checks if a process is Query and it is running over 300 seconds(5 minutes). If so, kill that query.

/usr/local/bin # less killLongRunningSql.pl
use strict;
use DBI;
use DBD::mysql;

#MySQL connection
my $db_name = "YourDatabase";
my $db_connection = DBI->connect("DBI:mysql:$db_name","username","password") or die "Connection Error: $DBI::errstr\n";

#Execute query that shows the processlist
my $run_query = $db_connection->prepare("SHOW FULL PROCESSLIST");
$run_query->execute or die "SQL Error: $DBI::errstr\n";

#Declare @row array to store each row of above query being executed
my @row;

while (@row=$run_query->fetchrow_array()){
        if ( @row[5] > 300 && @row[4] =~ /Query/ ){
                my $killQuery = "KILL QUERY @row[0]";
                print "Query to be Executed: $killQuery\n";
                print "Process Info: @row[0] @row[1] @row[2]  @row[4] @row[5]\n";

                my $killQueryExecute = $dbh -> prepare($killQuery);
                $killQueryExecute-> execute;
        }

}

Step2: Add above script to cronjobs to run every minute

 # crontab -l

*/1 * * * * /usr/local/bin/killLongRunningSql.pl > /usr/local/bin/killLongRunningSql.log 2>&1

That's it!  Cheers!!!