Mysql批量杀死线程

一、场景描述

在mysql的管理维护工作中，有时候会出现锁表的情况，表现为一个线程占用mysql进程，其他线程只能等待，可能的原因比较多，之前遇到的两种情况是，数据库操作的代码逻辑不合理（对数据库操作没有考虑释放线程或者异步操作不合理等等），上传大文件时写入数据库的操作耗时太久，测试为了验证多文件上传，在数据库同时产生40多个线程。
要杀掉这些线程，一个个删除显然不现实，这时候就要用到批量删除的操作进程处理，由于数据库服务器环境的不同，又需要不同的处理方式来解决。

二、Mysql手册说明

2.1.KILL语法

KILL [CONNECTION | QUERY] thread_id
每个与mysqld的连接都在一个独立的线程里运行，可以使用SHOW PROCESSLIST语句查看哪些线程正在运行，并使用KILL thread_id语句终止一个线程。
KILL允许自选的CONNECTION或QUERY修改符：

KILL CONNECTION与不含修改符的KILL一样：它会终止与给定的thread_id有关的连接。
KILL QUERY会终止连接当前正在执行的语句，但是会保持连接的原状。

如果拥有PROCESS权限，则可以查看所有线程。如果拥有SUPER权限，可以终止所有线程和语句。否则，只能查看和终止自己的线程和语句。
也可以使用mysqladmin processlist和mysqladmin kill命令来检查和终止线程。
注释：不能同时使用KILL和Embedded MySQL Server库，因为内置的服务器只运行主机应用程序的线程，它不能创建任何自身的连接线程。
当进行一个KILL时，对线程设置一个特有的终止标记。在多数情况下，线程终止可能要花一些时间，这是因为终止标记只会在在特定的间隔被检查：

在SELECT, ORDER BY和GROUP BY循环中，在读取一组行后检查标记。如果设置了终止标记，则该语句被放弃。
在ALTER TABLE过程中，在每组行从原来的表中被读取前，检查终止标记。如果设置了终止标记，则语句被放弃，临时表被删除。
在UPDATE或DELETE运行期间，在每个组读取之后以及每个已更行或已删除的行之后，检查终止标记。如果终止标记被设置，则该语句被放弃。注意，如果正在使用事务，则变更不会被回滚。
GET_LOCK()会放弃和返回NULL。
INSERTDELAYED线程会快速地刷新（插入）它在存储器中的所有的行，然后终止。
如果线程在表锁定管理程序中（状态：锁定），则表锁定被快速地放弃。
如果在写入调用中，线程正在等待空闲的磁盘空间，则写入被放弃，并伴随"disk full"错误消息。

警告：对MyISAM表终止一个REPAIR TABLE或OPTIMIZE TABLE操作会导致出现一个被损坏的没有用的表。对这样的表的任何读取或写入都会失败，直到再次优化或修复它（不中断）。

三、解决问题

3.1通过information_schema.processlist表中的连接信息生产需要处理掉的临时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)

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)

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

3.2Mysqladmin工具配合awk工具杀死所有进程

mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill

mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "Mike")print $2}'|xargs -n 1 mysqladmin -uroot -p kill

3.3通过shell脚本实现

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

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