杀掉链接mysql库线程的方法

##杀掉查询的死锁的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
  • 1.
  • 2.
  • 3.
mysqladmin -uroot -p'Zykj#558996' -S /tmp/mysql5.6.sock processlist >>/tmp/test.txt 保存sql记录杀掉查询的死锁的mysql的链接
  • 1.
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 
  • 1.

##杀掉当前的所有的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
  • 1.

##查看表记录数:

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
  • 1.

##通过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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.

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

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

##通过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

posted @ 2017-12-24 22:40  勤奋的蓝猫  阅读(6)  评论(0编辑  收藏  举报  来源