Mysql管理必备工具Maatkit详解之十四(mk-kill)
Mysql管理必备工具Maatkit详解之十四(mk-kill)
2009年12月29日 作者： 大头刚
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打印：
# 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杀掉并打印：
# 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 ...
Options:
--ask-pass Prompt for a password when connecting to MySQL
--charset -ADefault 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 -FOnly read mysql options from the given file
--heartbeat Print information to STDOUT about what is being done
--help Show help and exit
--host -hConnect 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 -pPassword to use when connecting
--pid Create the given PID file when daemonized
--port -PPort 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 -SSocket file to use for connection
--user -uUser 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-killWait 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
--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
--only-oldest Only kill the single oldest query (default yes)
Rules:
Specify at least one of --kill, --print or --execute-command.
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/111930/showart_2185032.html
页:
[1]