自学内容网 自学内容网
自学内容网 > 正文

mysql Kill脚本

🕗 发布于 2024-12-10 02:56 mysql  adb  数据库  

背景描述

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

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


原文地址：https://blog.csdn.net/JSWANGCHANG/article/details/144246974

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024