前段时间测试机上的数据库出问题,好几十个操作锁死,一个个kill掉简直会让人疯掉,后来直接重启了mysql,闲来无事,写了个kill掉mysql进程的shell脚本。代码如下:
- #!/bin/sh
- mysql_cmd=mysql;
- user_help()
- {
- echo "-------------------------------------------------------";
- echo "| this program help you killing locked mysql process |";
- echo "| -c the type of query.like:Sleep,Locked,Killed |";
- echo "| -h Connect to host. |";
- echo "| -p password for user. |";
- echo "| -P port for mysql server. |";
- echo "| -t the sql execute longest time. |";
- echo "| -u user for login mysql. |";
- echo "-------------------------------------------------------";
- }
- host= user= password= port= time= type=
- while getopts :c:h:p:P:t:u: opt
- do
- case $opt in
- c)
- type=$OPTARG;
- ;;
- h)
- host=$OPTARG;
- ;;
- p)
- password=$OPTARG;
- ;;
- P)
- port=$OPTARG;
- ;;
- t)
- time=$OPTARG;
- ;;
- u)
- user=$OPTARG;
- ;;
- '?')
- user_help ;
- exit 2;
- ;;
- ':')
- echo option \'-${OPTARG}\' requires an argument;
- echo "Try '-H' for more infomation";
- exit 2;
- ;;
- esac
- done
- if [ -z $host ] || [ -z $password ] || [ -z $user ] || [ -z $port ]
- then
- user_help;
- exit 3;
- fi
- check=
- if ! [ -z $type ]
- then
- check=${check}' && $5='${type};
- fi
- if ! [ -z $time ]
- then
- check=${check}' && $6>'${time};
- fi
- kill_process=$($mysql_cmd -h $host -u $user -p$password -P $port -e 'show processlist' | awk '{if(NR>1'"${check}"') print $1}' );
- all=0 success=0 fail=0
- for process_id in $kill_process
- do
- if ($mysql_cmd -h $host -u $user -p$password -P $port -e 'kill '"${process_id}")
- then
- success=$((success+1));
- else
- fail=$((fail+1));
- fi
- all=$((all+1));
- done
- echo kill $all process;
- echo success $success process;
- echo fail $fail process;
参数说明:
-h 数据库的服务器地址
-u 数据库的用户名
-p(小写) 数据库的密码
-P(大写) 数据库的端口
-c 要kill掉的进程状态,一般为Locked、Sleep,可以进入mysql用show process查看
-t SQL执行的最大时间,超过此最大值将会被kill掉
执行方式如下
./kill_lock.sh -h127.0.0.1 -uroot -P3306 -p'root' -t 10
执行结果如下
转载于:https://blog.51cto.com/xdebug/1070753