背景:
最近业务因为db高负载引发了一起故障,原因是因为大量的持续慢查询导致db被打死,之前有在腾讯云平台侧配置持续kill大于5秒的语句,但是出现故障后发现平台支持的粒度最大只到5秒100条,所以这里想通过Shell脚本来实现持续kill的功能,有需要的同学直接拿来就可以用。

Shell:

#!/bin/bash

ip=$1

kill_time=$2

num=$3


function Help(){
		echo "----调用失败,请按照以下示例输入----"
		echo "Usage: bash $0 127.0.0.1 5 0"
		echo -e "第1个参数输入IP地址(默认3306端口)\n第2个参数输入时间\n第3个参数0:表示死循环(默认sleep 5),1:表示只需要执行一次"
		exit 1

}


function check_par(){
		if ! [[ "$kill_time" =~ ^[0-9]+$ && "$num" =~ ^[0-9]+$ ]]; then
		  echo "必须输入数字"
  		exit 1
		fi
}


if [ $# -ne 3 ];then
        Help
else
				check_par
fi



function Kill_mysql(){
while :
do
   	    mysql -h${ip} -uroot -p'123456' -e "select id from information_schema.processlist where time > ${kill_time};" > /tmp/out.txt

		sed -i '/id/d' /tmp/out.txt
		for i in `cat /tmp/out.txt`
		do
				echo `date` | tee -a /tmp/kill_mysql.log
		    echo "kill ${i}" | tee -a /tmp/kill_mysql.log
				mysql -h${ip} -uroot -p'123456' -e " kill $i" 2>&1 | tee -a /tmp/kill_mysql.log
				echo "--------" | tee -a /tmp/kill_mysql.log
		done
		sleep 5
done
}




function Kill_mysql_1(){
while :
do
  	    mysql -h${ip} -uroot -p'123456' -e "select id from information_schema.processlist where time > ${kill_time};" > /tmp/out.txt

		sed -i '/id/d' /tmp/out.txt
		for i in `cat /tmp/out.txt`
		do
				echo `date` | tee -a /tmp/kill_mysql.log
		    echo "kill ${i}" | tee -a /tmp/kill_mysql.log
				mysql -h${ip} -uroot -p'123456' -e " kill $i" 2>&1 | tee -a /tmp/kill_mysql.log
				echo "--------" | tee -a /tmp/kill_mysql.log
		done
		break
done
}





function Start(){
	if [ ${num} -eq 1 ];then
		Kill_mysql_1
	else
		Kill_mysql
	fi
}


Start

执行如下所示:

[root@VM-234-68-centos /data/jarvisyq/shell]# sh kill_mysql.sh 127.0.0.1 10 0
Fri Jun 30 16:44:10 CST 2023
kill 2637059
--------
Fri Jun 30 16:44:45 CST 2023
kill 2637419
--------
Fri Jun 30 16:44:50 CST 2023
kill 2637422
--------
07-01 14:56