在数据量大或者查询特别频繁的时候,偶尔会出现查询锁表的情况,通过show processlist;可以看到存在有Locked状态,
现写了个shell脚本自动杀死锁表进程,
代码如下:
1 #!/bin/bash 2 3 mysql_cmd=mysql 4 host=192.168.1.1 5 user=root 6 password=root 7 port=3306 8 kill_process=$($mysql_cmd -h $host -u $user -p$password -P $port -e 'show processlist' | grep -i "Locked" | awk '{if(NR>1'"${check}"') print $1}'); 9 10 for process_id in $kill_process 11 do 12 #echo $process_id 13 $mysql_cmd -h $host -u $user -p$password -P $port -e 'kill '"${process_id}" 14 done
另外种更简单的方式
1 (1)将所有的MySQL连接进程杀掉 2 for i in ` mysql -uroot -pzhangyun -Bse "show processlist" | grep -v "show processlist" | awk '{print $1}'` 3 do 4 mysql -uroot -pzhangyun -e "kill $i" 5 done 6 7 注:这里将自身命令的show processlist进程过滤掉 8 9 (2)删除指定用户的连接进程 10 for i in `mysql -uroot -pzhangyun -se "show processlist" | grep -v "show processlist" | awk '{if($2=="youname")print $1}'` 11 do 12 mysql -uroot -pzhangyun -e "kill $i" 13 done