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;
}
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!!!