Mysql管理必備工具M(jìn)aatkit詳解之十四(mk-kill)

pkman110

Mysql管理必備工具M(jìn)aatkit詳解之十四(mk-kill)
2009年12月29日　作者： 大頭剛　
mk-kill - 顧名思義，殺mysql線程。安裝方法查看
這里
。
在一個OLTP的生產(chǎn)環(huán)境，一般不會讓sql執(zhí)行過長的時間，特別是myisam這樣表鎖的引擎，如果出現(xiàn)長時間執(zhí)行的sql一般是誤操作，要不就是出現(xiàn)問題了。
出現(xiàn)這種情況，一般是登錄mysql手工執(zhí)行kill操作，Maatkit現(xiàn)在提供了一個命令來執(zhí)行這個操作。例如：
殺掉超過60秒的sql：
mk-kill –busy-time 60 –kill
如果你想先不殺，先看看有哪些sql運(yùn)行超過60秒：
mk-kill –busy-time 60 –print
如果你想殺掉，同時輸出殺掉了哪些進(jìn)程：
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可以根據(jù)自己的需求殺掉影響系統(tǒng)運(yùn)行的sql，怎么用就需要看自己的實(shí)際情況了。下面給出它的幫助:
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.

本文來自ChinaUnix博客，如果查看原文請點(diǎn)：http://blog.chinaunix.net/u3/111930/showart_2185032.html