杀掉链接mysql库线程的方法

最新推荐文章于 2024-05-30 15:06:37 发布

weixin_33981932

最新推荐文章于 2024-05-30 15:06:37 发布

阅读量169

点赞数

文章标签：数据库 awk

原文链接：http://blog.51cto.com/wujianwei/2054122

版权

##杀掉查询的死锁的mysql的链接:
检查过程：

 mysqladmin -uroot -p'Zykj#558996' -S /tmp/mysql5.6.sock processlist|awk -F "|" '{print $2,$3,$6,$8}'
 Id             User           Command          State
 54251602   mdrdb          Query        Waiting for table level lock

mysqladmin -uroot -p'Zykj#558996' -S /tmp/mysql5.6.sock processlist >>/tmp/test.txt 保存sql记录杀掉查询的死锁的mysql的链接

mysqladmin -uroot -p'Zykj#558996' -S /tmp/mysql5.6.sock processlist|grep "Waiting for table level lock"|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p'Zykj#558996' -S /tmp/mysql5.6.sock kill

##杀掉当前的所有的mysql的链接：

mysqladmin -uroot -p'Zykj#558996 -S /tmp/mysql5.6.sock processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p'Zykj#558996 -S /tmp/mysql5.6.sock kill

##查看表记录数：

for n in `mysql -utestuser04 -p'Zykj#558996'  -S /tmp/mysql5.6.sock -e "use testdrnew;show tables;"|sed '1d'`;do echo $n&&mysql -utestuser04 -p'Zykj#558996' -S /tmp/mysql5.6.sock -e "use testdrnew;select count(*) from $n";done >/tmp/test.txt

##通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后登录mysql执行临时文件中生成的指令


mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
+------------------------+
| concat('KILL ',id,';') 
+------------------------+
| KILL 3101;       
| KILL 2946;       
+------------------------+
2 rows in set (0.00 sec)

(root@localhost:mysql3308.sock)[test01]>select concat('KILL ',id,';') from information_schema.processlist where user='jianweiuser';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 69;               |
+------------------------+
1 row in set (0.00 sec)

mysql>select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)

(root@localhost:mysql3308.sock)[test01]>select concat('KILL ',id,';') from information_schema.processlist where user='jianweiuser' into outfile '/tmp/b.txt';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 69;               |
+------------------------+
1 row in set (0.00 sec)

mysql>source /tmp/a.txt;
Query OK, 0 rows affected (0.00
mysql>source /tmp/b.txt;
Query OK, 0 rows affected (0.00

##通过SHEL脚本实现杀掉锁定的MySQL连接

for id in `mysqladmin processlist|grep -i locked|awk '{print $1}'`
do
  mysqladmin kill ${id}
done

##通过Maatkit工具集中提供的mk-kill命令进行

#杀掉超过60秒的sql
mk-kill -busy-time 60 -kill
#如果你想先不杀，先看看有哪些sql运行超过60秒
mk-kill -busy-time 60 -print
#如果你想杀掉，同时输出杀掉了哪些进程
mk-kill -busy-time 60 -print –kill
mk-kill更多用法可参考：
http://www.maatkit.org/doc/mk-kill.html
http://www.sbear.cn/archives/426
Maatkit工具集的其它用法可参考：
http://code.google.com/p/maatkit/wiki/TableOfContents?tm=6

转载于:https://blog.51cto.com/wujianwei/2054122