背景:
最近业务因为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
--------