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.