mysql自动清理连接

DB_USERNAME=root
DB_PASSWORD=root

iret=`mysql -u$DB_USERNAME -p$DB_PASSWORD -N -e "select id,time from information_schema.processlist where command='query' and time>1 and user != 'root' order by time desc "`

sleep 2

GT_ALL_COUNT=0
GT_20_COUNT=0
GT_100_COUNT=0
GT_500_COUNT=0
GT_2000_COUNT=0
GT_5000_COUNT=0
killstring20=""
killstring100=""
killstring500=""
killstring2000=""
killstring5000=""
killstring=""
starttime=`date`

echo "start to check sql the time is $starttime"

IFS=$'\n';for linetemp in `echo -e "$iret"`;
do
         
        line=`echo $linetemp`
        
        if [ -z "$line" ];then
            continue
        fi
        

        echo "$line"
        query_time=`echo $line| awk '{print int($2)}'`
        process_id=`echo $line| awk '{print int($1)}'`
        if [ "$process_id" -eq 0 ];then
            #echo "abort"
            continue
        fi
        if [ $query_time -ge 20 -a $query_time -lt 100 ]; then
                GT_20_COUNT=`expr $GT_20_COUNT + 1`
                killstring20=`echo $killstring20";"kill "$process_id;"`
        elif [ $query_time -ge 100 -a $query_time -lt 500 ]; then
                echo "$starttime find 100 BIGSQL"
                GT_100_COUNT=`expr $GT_100_COUNT + 1`
                killstring100=`echo $killstring100";"kill "$process_id;"`
        elif [ $query_time -ge 500 -a $query_time -lt 2000 ]; then
                echo "$starttime find 500 BIGSQL"
                GT_500_COUNT=`expr $GT_500_COUNT + 1`
                killstring500=`echo $killstring500";"kill "$process_id;"`
        elif [ $query_time -ge 2000 -a $query_time -lt 5000 ]; then
                echo "$starttime find 2000 BIGSQL"
                GT_2000_COUNT=`expr $GT_2000_COUNT + 1`
                killstring2000=`echo $killstring2000";"kill "$process_id;"`
        elif [ $query_time -ge 5000 ]; then
                echo "$starttime find 5000 BIGSQL"
                GT_5000_COUNT=`expr $GT_5000_COUNT + 1`
                killstring5000=`echo $killstring5000";"kill "$process_id;"`
        fi
done

if [ "$GT_20_COUNT" -ge 200 ];then
    killstring=`echo "$killstring20"`
fi
if [ "$GT_100_COUNT" -ge 8 ];then
    killstring=`echo "$killstring100"`
fi
if [ "$GT_500_COUNT" -ge 5 ];then
    killstring=`echo "$killstring""$killstring500"`
fi
if [ "$GT_2000_COUNT" -ge 1 ];then
    killstring=`echo "$killstring""$killstring2000"`
fi
if [ "$GT_5000_COUNT" -ge 1 ];then
    killstring=`echo "$killstring""$killstring5000"`
fi

starttime=`date`
echo "$killstring"
if [ -n "$killstring" ];then
    echo "$starttime start to kill id and the id is $killstring"
    mysql -u$DB_USERNAME -p$DB_PASSWORD -e "$killstring"

 

 

 

 

 

注意该脚本只能放在安装了mysql的机器上才能起到清理作用
fi