mysql如何杀掉线程_杀掉链接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

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