PHP SCRIPT - Mysql sleep query kill (for cPanel, adaptable also to other panels)

From wiki.morphey.org

This script, tries to eliminate the annoying problem of the queries mysql that they remains in sleep, occupying a lot of ram and cpu. To install him, must be authenticates as "root" and the command "mysqladmin" it has to obligatorily be without password and with the db of default (or shaped for being launched without consumer password however "root"), after that you can launch this command:

 
cd /root ; rm -rf morphtool ; mkdir morphtool ; cd morphtool
wget http://wiki.morphey.org/images/8/8b/Mysql_sleep_query_kill.zip
unzip Mysql_sleep_query_kill.zip ; rm -f Mysql_sleep_query_kill.zip
rm -rf php.ini ; touch php.ini
echo "* * * * * cd /root/morphtool ; php -c php.ini mysql_sleep_query_kill.php > /dev/null &" >> /var/spool/cron/root
 

Code in an only solution

 
cd /root ; rm -rf morphtool ; mkdir morphtool ; cd morphtool ; wget http://wiki.morphey.org/images/8/8b/Mysql_sleep_query_kill.zip ; unzip Mysql_sleep_query_kill.zip ; rm -f Mysql_sleep_query_kill.zip ; rm -rf php.ini ; touch php.ini ; echo "* * * * * cd /root/morphtool ; php -c php.ini mysql_sleep_query_kill.php > /dev/null &" >> /var/spool/cron/root
 

This is the code:

mysql_sleep_query_kill.php

 
<?
// Mysql sleep kill
//                     * coded by morphey (morphey@morphey.org)
//
// AVVISO: deve essere avviato via cron come utente "root" (uid=0)
// ottimizzato per cPanel
/*
 
#Installazione:
cd /root ; rm -rf morphtool ; mkdir morphtool ; cd morphtool
vi mysql_sleep_query_kill.php
#Incolla il codice e salva
rm -rf php.ini ; touch php.ini
echo "* * * * * cd /root/morphtool ; php -c php.ini mysql_sleep_query_kill.php > /dev/null &" >> /var/spool/cron/root
 
 
*/
// Configurazione: ___________________________|
 
// $sleep_time = Impostare i secondi dopo i quali, se la query e' in sleep, si killera' automaticamente la query
$sleep_time = "60";
 
// $times = Impostare quante volte eseguire lo script | 0 = infinito (rimane in bg)
$times = 1;
// $times_sleep = Impostare quanto tempo far passare prima dell'avvio di un ciclo
$times_sleep = "1";
// $file_log = Impostare il file di log (percorso "assoluto" compreso) | sara' utilizzato per l'invio delle email
$file_log = "/var/log/mysql_kill_query.log";
 
$database_skip = array();
// $database_skip[] = Impostare i database da far saltare al controllo
$database_skip[] = "eximstats";
$database_skip[] = "horde";
 
//___________________________________|
function scriviLog($somecontent) {
	global $file_log;
	$filename = $file_log;
	if (!is_file($filename)) { shell_exec("touch ".$filename." ; chmod 777 ".$filename); }
	if (!is_writable($filename)) { shell_exec("chmod 777 ".$filename); }
	$handle = fopen($filename, 'a');
	fwrite($handle, $somecontent."\n");
	fclose($handle);
}
 
$active_times = 0;
while ($active_times<=$times) {
	$out = shell_exec("mysqladmin proc");
	$c = 0;
	foreach (explode("\n",$out) as $linea) {
		if (!eregi("-+-",$linea)) {
			if ($c!=0) {
				$arr = explode("|",trim($linea));
				if ($arr[1]!="") {
 
					$time = intval(ltrim(rtrim($arr[6])));
					settype($time,"integer");
 
					$id = ltrim(rtrim($arr[1]));
					$user = ltrim(rtrim($arr[2]));
					$database = ltrim(rtrim($arr[4]));
					$command = ltrim(rtrim($arr[5]));
					$state = ltrim(rtrim($arr[7]));
					$query = ltrim(rtrim($arr[8]));
 
					echo "ID->".$arr[1]." | Time->".$arr[6]." | User->".$arr[2]." | Query->".$query;
 
					if ($time>=$sleep_time) {
						$check_db = 0;
						foreach ($database_skip as $db_skip) {
							if ($database==$db_skip) { $check_db = 1; }
						}
						if ($check_db!=1) {
							// struttura dei log
							// id,user,database,command,state,times,query
							scriviLog(date("d-m-Y_G.i.s",time()).",".$id.",".$user.",".$database.",".$command.",".$state.",".$time.",".$query);
							shell_exec("mysqladmin kill ".$arr[1]);
							echo " ==> killed!";
						}
					}
					echo "\n";
				}
 
			} else { $c++; }
 
		}
	}
 
	if ($times!=0) { $active_times++; }
 
	if ($times!=0) {
		if ($active_times<$times) {
			sleep($times_sleep);
			echo "[sleep->".$active_times."] for ".$times_sleep." seconds...\n";
		}
	}
}
 
?>