杀SQL脚本

DB_USERNAME=root
DB_PASSWORD=123456

select=`mysql -u$DB_USERNAME -p$DB_PASSWORD -N -e "SELECT id, time, info FROM information_schema.processlist WHERE command = 'query' AND time > 20 ORDER BY time DESC"`

echo ====================$(date +%F%n%T) start====================

# [0100,0500)=12
# [0500,0800)=10
# [0800,1000)=08
# [1000,2000)=05
# [2000,3000)=04
# [3000,4000)=03
# [4000,5000)=02
# [5000,0000)=01
count_0100=0
kill_0100=""
count_0500=0
kill_0500=""
count_0800=0
kill_0800=""
count_1000=0
kill_1000=""
count_2000=0
kill_2000=""
count_3000=0
kill_3000=""
count_4000=0
kill_4000=""
count_5000=0
kill_5000=""

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

  echo $line

  id=`echo $line | awk '{print int($1)}'`

  if [ "$id" -eq 0 ]; then
    continue
  fi

  time=`echo $line | awk '{print int($2)}'`

  if [ $time -ge 100 -a $time -lt 500 ]; then
    count_0100=`expr $count_0100 + 1`
    kill_0100=`echo ${kill_0100}kill "$id;"`
  elif [ $time -ge 500 -a $time -lt 800 ]; then
    count_0500=`expr $count_0500 + 1`
    kill_0500=`echo ${kill_0500}kill "$id;"`
  elif [ $time -ge 800 -a $time -lt 1000 ]; then
    count_0800=`expr $count_0800 + 1`
    kill_0800=`echo ${kill_0800}kill "$id;"`
  elif [ $time -ge 1000 -a $time -lt 2000 ]; then
    count_1000=`expr $count_1000 + 1`
    kill_1000=`echo ${kill_1000}kill "$id;"`
  elif [ $time -ge 2000 -a $time -lt 3000 ]; then
    count_2000=`expr $count_2000 + 1`
    kill_2000=`echo ${kill_2000}kill "$id;"`
  elif [ $time -ge 3000 -a $time -lt 4000 ]; then
    count_3000=`expr $count_3000 + 1`
    kill_3000=`echo ${kill_3000}kill "$id;"`
  elif [ $time -ge 4000 -a $time -lt 5000 ]; then
    count_4000=`expr $count_4000 + 1`
    kill_4000=`echo ${kill_4000}kill "$id;"`
  elif [ $time -ge 5000 ]; then
    count_5000=`expr $count_5000 + 1`
    kill_5000=`echo ${kill_5000}kill "$id;"`
  fi
done

kill_sql=""

if [ "$count_0100" -ge 12 ]; then
  kill_sql=`echo ${kill_sql}${kill_0100}`
fi
if [ "$count_0500" -ge 10 ]; then
  kill_sql=`echo ${kill_sql}${kill_0500}`
fi
if [ "$count_0800" -ge  8 ]; then
  kill_sql=`echo ${kill_sql}${kill_0800}`
fi
if [ "$count_1000" -ge  5 ]; then
  kill_sql=`echo ${kill_sql}${kill_1000}`
fi
if [ "$count_2000" -ge  4 ]; then
  kill_sql=`echo ${kill_sql}${kill_2000}`
fi
if [ "$count_3000" -ge  3 ]; then
  kill_sql=`echo ${kill_sql}${kill_3000}`
fi
if [ "$count_4000" -ge  2 ]; then
  kill_sql=`echo ${kill_sql}${kill_4000}`
fi
if [ "$count_5000" -ge  1 ]; then
  kill_sql=`echo ${kill_sql}${kill_5000}`
fi

if [ -n "$kill_sql" ]; then
  echo ====================$(date +%F%n%T) kill=====================
  echo $kill_sql
  mysql -u$DB_USERNAME -p$DB_PASSWORD -e "$kill_sql"
fi

echo ====================$(date +%F%n%T) end======================