Use PHP program to clear MySQL dead connection

In recent days, the school's MySQL database has always been a lot of dead connections, mainly because there are too many sleep connections, and time is very long, full of all the number of available connections, so that other users can no longer connect to the database. I started to think about tuning MySQL database parameters, but changing many of the parameters still didn't solve the problem. So think of a more ruthless way, write a PHP script, every 2 minutes, found dead connection (more than 120 seconds) to kill, so that no longer let some programs to kill the database server, the following is a kill the connection of the small program:

Download:

kill-mysql-sleep-proc.php

Define (' Max_sleep_time ', 120);

$hostname = "localhost";

$username = "root";

$password = "password";

$connect =mysql_connect ($hostname, $username, $password);

$result =mysql_query ("Showprocesslist", $connect);

while ($proc =mysql_fetch_assoc ($result)) {

if ($proc [Command]== ' sleep ' && $proc [' time ']>max_sleep_time] {

@mysql_query ("KILL". $proc ["Id"], $connect);

}

}

Mysql_close ($connect);

?>

Change the $password in it to your actual database password, and the time of the dead connection can be modified. Then you can join the planning task. For example, use the CRONTAB-E command to join:

*/2****php/usr/local/sbin/kill-mysql-sleep-proc.php

You can check and clear a dead connection in the database every 2 minutes.