mysql Kill脚本

背景描述

下面脚本用于在数据库阻塞情况下，紧急恢复.

Kill 某条sql

执行

sh kill.sh 或者sh kill.sh N(次数)

[J33FE0D ~]# cat kill_step1_oneSelect.sh
#!/bin/bash


# kill shwx processlist
# tony wnag


flag=$#

info='''----------------------------------
Argument is not corect
Reference:
sh kill.sh: kill one times
sh kill.sh $x: kill $1 times 
----------------------------------'''

case $flag in 
0)
        for x in `cat kill.list`
        do
            echo $x
            sh mha_manager.sh $x  "select concat('KILL ',id,';') from information_schema.processlist where user <> 'jd_mha_manager' and info like '%50793412%'"| egrep "KILL [0-9]" > /tmp/kill.sql
            mysql  -u jd_mha_manager -p'CmNuduFFYHLEw1P2' -A -P 3358 -h $x < /tmp/kill.sql
        done
;;
1)
        count=$1
        for xx in `seq 1 $count`
        do
            echo "kill time: $xx"
            for x in `cat kill.list`
            do
                echo $x
                sh mha_manager.sh $x  "select concat('KILL ',id,';') from information_schema.processlist where user <> 'jd_mha_manager' and info like '%select%'"| egrep "KILL [0-9]" > /tmp/kill.sql
                mysql  -u jd_mha_manager -p'CmNuduFFYHLEw1P2' -A -P 3358 -h $x < /tmp/kill.sql
            done
            sleep 1
        done
;;
*)
        echo $info
;;
esac

kill所有select

J33FE0D ~]# cat kill_step2_allSelect.sh
#!/bin/bash


# kill shwx processlist
# tony wnag


flag=$#

info='''----------------------------------
Argument is not corect
Reference:
sh kill.sh: kill one times
sh kill.sh $x: kill $1 times 
----------------------------------'''

case $flag in 
0)
        for x in `cat kill.list`
        do
            echo $x
            sh mha_manager.sh $x  "select concat('KILL ',id,';') from information_schema.processlist where user <> 'jd_mha_manager' and info like '%select%'"| egrep "KILL [0-9]" > /tmp/kill.sql
            mysql  -u jd_mha_manager -p'CmNuduFFYHLEw1P2' -A -P 3358 -h $x < /tmp/kill.sql
        done
;;
1)
        count=$1
        for xx in `seq 1 $count`
        do
            echo "kill time: $xx"
            for x in `cat kill.list`
            do
                echo $x
                sh mha_manager.sh $x  "select concat('KILL ',id,';') from information_schema.processlist where user <> 'jd_mha_manager' and info like '%select%'"| egrep "KILL [0-9]" > /tmp/kill.sql
                mysql  -u jd_mha_manager -p'CmNuduFFYHLEw1P2' -A -P 3358 -h $x < /tmp/kill.sql
            done
            sleep 1
        done
;;
*)
        echo $info
;;
esac

kill 所有连接（包括读写，空连接）

#!/bin/bash


# kill shwx processlist
# tony wnag


flag=$#

info='''----------------------------------
Argument is not corect
Reference:
sh kill.sh: kill one times
sh kill.sh $x: kill $1 times 
----------------------------------'''

case $flag in 
0)
        for x in `cat kill.list`
        do
            echo $x
            sh mha_manager.sh $x  "select concat('KILL ',id,';') from information_schema.processlist where user <> 'jd_mha_manager'"| egrep "KILL [0-9]" > /tmp/kill.sql
            mysql  -u jd_mha_manager -p'CmNuduFFYHLEw1P2' -A -P 3358 -h $x < /tmp/kill.sql
        done
;;
1)
        count=$1
        for xx in `seq 1 $count`
        do
            echo "kill time: $xx"
            for x in `cat kill.list`
            do
                echo $x
                sh mha_manager.sh $x  "select concat('KILL ',id,';') from information_schema.processlist where user <> 'jd_mha_manager'"| egrep "KILL [0-9]" > /tmp/kill.sql
                mysql  -u jd_mha_manager -p'CmNuduFFYHLEw1P2' -A -P 3358 -h $x < /tmp/kill.sql
            done
        done
;;
*)
        echo $info
;;
esac

Kill 慢SQL

Kill 执行时间超过2s的慢sql

J33FE0D ~]# cat kill_step7_slowSelect.sh
#!/bin/bash


# kill shwx processlist
# tony wnag


flag=$#

info='''----------------------------------
Argument is not corect
Reference:
sh kill.sh: kill one times
sh kill.sh $x: kill $1 times 
----------------------------------'''

case $flag in 
0)
        for x in `cat kill.list`
        do
            echo $x
            sh mha_manager.sh $x  "select concat('KILL ',id,';') from information_schema.processlist where user <> 'jd_mha_manager' and info like '%select%' and time > 2"| egrep "KILL [0-9]" > /tmp/kill.sql
            mysql  -u jd_mha_manager -p'CmNuduFFYHLEw1P2' -A -P 3358 -h $x < /tmp/kill.sql
        done
;;
1)
        count=$1
        for xx in `seq 1 $count`
        do
            echo "kill time: $xx"
            for x in `cat kill.list`
            do
                echo $x
                sh mha_manager.sh $x  "select concat('KILL ',id,';') from information_schema.processlist where user <> 'jd_mha_manager' and info like '%select%' and time > 2"| egrep "KILL [0-9]" > /tmp/kill.sql
                mysql  -u jd_mha_manager -p'CmNuduFFYHLEw1P2' -A -P 3358 -h $x < /tmp/kill.sql
            done
            sleep 1
        done
;;
*)
        echo $info
;;
esac