Use the PHP applet to clear Mysql dead connection _ MySQL

Using the PHP applet to clear Mysql dead connections in recent days, many dead connections always occur in the school's MySQL database, mainly because there are too many Sleep connections and the Time is very long, all available connections are occupied, so that other users cannot connect to the database. I started to consider adjusting MySQL database parameters, but many parameters have not solved this problem. So I thought of a tough way to write a php script and execute it every two minutes. I found that the connection was killed (more than 120 seconds, in this way, some programs will no longer be killed on the database server. The following is a Kill connection applet:

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 $ password in it to your actual database password, and the dead connection time can also be modified. Then join the scheduled task. For example, use the crontab-e command to add:

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

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