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打印：

[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.