mysql执行sql停止服务_[随笔] 停止mysql正在执行的sql

背景

很多时候，我们会提交一些耗时比较长的sql，可能出现mysql服务器内存或者CPU暴增，引起报警，甚至影响其他业务。这个时候我们需要及时找到卡主的query，或者说是耗时较长的query，并将其kill。当我们使用navicat客户端连接mysql执行查询的时候，很多新手经常会误认为，关闭navicat客户端即可停止该查询。其实这是错误的，此时关闭navicat或者从任务管理器kill掉navicat，并不是将对应的query停止，此时该query已经提交到mysql服务器了，如果需要停止该查询，实际是需要将mysql服务器上对应的查询停止掉。

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。

· INSERT DELAYED线程会快速地刷新(插入)它在存储器中的所有的行，然后终止。

· 如果线程在表锁定管理程序中(状态：锁定)，则表锁定被快速地放弃。

· 如果在写入调用中，线程正在等待空闲的磁盘空间，则写入被放弃，并伴随"disk full"错误消息。

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

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.00sec)

mysql>select concat('KILL',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';

Query OK,2 rows affected (0.00sec)

mysql>source /tmp/a.txt;

Query OK,0 rows affected (0.00 sec)

2、杀掉当前所有的MySQL连接

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

杀掉指定用户运行的连接，这里为Mike

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

3、通过SHEL脚本实现

#杀掉锁定的MySQL连接for id in `mysqladmin processlist|grep -i locked|awk '{print $1}'`domysqladminkill ${id}done

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

参考文档：

http://www.google.com

http://www.orczhou.com/index.php/2010/10/kill-mysql-connectio-in-batch/

http://www.mysqlperformanceblog.com/2009/05/21/mass-killing-of-mysql-connections/