mysql 工具 08s01_Mysql管理必备工具Maatkit详解之十四(mk-kill)

mk-kill - 顾名思义，杀mysql线程。安装方法查看这里。

在一个OLTP的生产环境，一般不会让sql执行过长的时间，特别是myisam这样表锁的引擎，如果出现长时间执行的sql一般是误操作，要不就是出现问题了。

出现这种情况，一般是登录mysql手工执行kill操作，Maatkit现在提供了一个命令来执行这个操作。例如：

杀掉超过60秒的sql：

mk-kill –busy-time 60 –kill

如果你想先不杀，先看看有哪些sql运行超过60秒：

mk-kill –busy-time 60 –print

如果你想杀掉，同时输出杀掉了哪些进程：

mk-kill –busy-time 60 –print –kill

下面举例说明：

先模拟一个长时间的sql

mysq> select count(*) from test where gid>2;

把长时间的sql打印：

[root@mysql2 ~]# mk-kill --busy-time 5 --print -u -p -h

# 2009-12-29T10:26:34 KILL 499967 (Query 33 sec) select count(*) from test where gid>2

将这个sql杀掉并打印：

[root@mysql2 ~]# mk-kill --busy-time 5 --print -u -p -h --kill

# 2009-12-29T10:27:09 KILL 499967 (Query 68 sec) select count(*) from test where gid>2

在查看原来模拟的sql:

mysql> select count(*) from test where gid>2;

ERROR 1053 (08S01): Server shutdown in progress

利用mk-kill可以根据自己的需求杀掉影响系统运行的sql，怎么用就需要看自己的实际情况了。下面给出它的帮助:

mk-kill --help

mk-kill kills, prints or executes another script for queries in MySQL that match

certain criteria. If FILE is not given, mk-kill executes SHOW PROCESSLIST on

MySQL to get queries. Else, mk-kill will get queries from FILE which should

contain the output of SHOW PROCESSLIST. If FILE is -, mk-kill reads from STDIN.

For more details, please use the --help option, or try 'perldoc

/usr/bin/mk-kill' for complete documentation.

Usage: /usr/bin/mk-kill [OPTION]... [FILE...]

Options:

--ask-pass Prompt for a password when connecting to MySQL

--charset -A Default character set

--config Read this comma-separated list of config files; if

specified, this must be the first option on the command

line

--daemonize Fork to the background and detach from the shell

--defaults-file -F Only read mysql options from the given file

--heartbeat Print information to STDOUT about what is being done

--help Show help and exit

--host -h Connect to host

--interval How often to check for queries to kill. Optional suffix

s=seconds, m=minutes, h=hours, d=days; if no suffix, s is

used.

--iterations How many times to iterate through the find-and-kill cycle

(default 1)

--log Print all output to this file when daemonized

--password -p Password to use when connecting

--pid Create the given PID file when daemonized

--port -P Port number to use for connection

--run-time How long to run before exiting. Optional suffix

s=seconds, m=minutes, h=hours, d=days; if no suffix, s is

used.

--set-vars Set these MySQL variables (default wait_timeout=10000)

--socket -S Socket file to use for connection

--user -u User for login if not current user

--version Show version and exit

--wait-after-kill Wait after killing a query, before looking for more to

kill. Optional suffix s=seconds, m=minutes, h=hours,

d=days; if no suffix, s is used.

--wait-before-kill Wait before killing a query. Optional suffix s=seconds,

m=minutes, h=hours, d=days; if no suffix, s is used.

Actions:

--execute-command Execute this command when a query matches

--kill Actually kill matching queries

--print Print a KILL statement for matching queries; does not

actually kill queries

Matches:

--busy-time Kill connections that have been running for longer than

this time. Optional suffix s=seconds, m=minutes, h=hours,

d=days; if no suffix, s is used.

--idle-time Kill connections that have been idle/sleeping for longer

than this time. Optional suffix s=seconds, m=minutes,

h=hours, d=days; if no suffix, s is used.

--ignore-command Ignore queries whose Command matches this Perl regex

(default Binlog.Dump)

--ignore-db Ignore queries whose db (database) matches this Perl regex

--ignore-host Ignore queries whose Host matches this Perl regex

--ignore-info Ignore queries whose Info (query) matches this Perl regex

--[no]ignore-self Don't kill mk-kill's own connection (default yes)

--ignore-state Ignore queries whose State matches this Perl regex

(default Locked)

--ignore-user Ignore queries whose user matches this Perl regex (default

system.user)

--match-command Kill only queries whose Command matches this Perl regex

--match-db Kill only queries whose db (database) matches this Perl

regex

--match-host Kill only queries whose Host matches this Perl regex

--match-info Kill only queries whose Info (query) matches this Perl

regex

--match-state Kill only queries whose State matches this Perl regex

--match-user Kill only queries whose User matches this Perl regex

--[no]only-oldest Only kill the single oldest query (default yes)

Rules:

Specify at least one of --kill, --print or --execute-command.