percona 实用工具之pt-kill

pt-kill

概述

从show processlist 中获取满足条件的连接或者从包含show processlist的文件中读取满足条件的连接并打印或者杀掉或者执行其他操作，这个工具在工作中实用性很高，当服务器连接出现异常后第一想到的就是pt-kill，自己写一个脚本也可以，但是功能还是没有pt-kill强大；一般我喜欢将连接本地mysql的必要信息放在一个文件文件中，当需要连接本地mysql时直接指定配置文件即可；

常用功能介绍

pt-kill --defaults-file xx --match-command Sleep --kill --victims all --interval 10 每隔10s 杀掉处于sleep状态的连接数；

pt-kill defaults-file xx --busy-time 60 --kill --victims all --interval 10 每隔10s 杀掉处初步runnning状态超过60s的连接数；几个重要参数--daemonize  放在后台以守护进程的形式运行；--interval  多久运行一次，单位可以是s,m,h，d等默认是s--victims 默认是oldest,只杀最古老的查询。这是防止被查杀是不是真的长时间运行的查询，他们只是长期等待。这种种匹配按时间查询，杀死一个时间最高值。               all 杀掉所有满足的线程               杀死所有，但最长的保留不杀action--kill                 杀掉连接并且退出--kill-query           只杀掉连接执行的语句，但是线程不会被终止--print                打印满足条件的语句
QUERY MATCHES
--busy-time 批次查询已运行的时间超过这个时间的线程；--idle-time 杀掉sleep 了多少时间的连接线程，必须在--match-command sleep时才有效
--ignore-command
type: string; group: Query Matches
Ignore queries whose Command matches this Perl regex.
See --match-command.
--ignore-db
type: string; group: Query Matches
Ignore queries whose db (database) matches this Perl regex.
See --match-db.
--ignore-host
type: string; group: Query Matches
Ignore queries whose Host matches this Perl regex.
See --match-host.
--ignore-info
type: string; group: Query Matches
Ignore queries whose Info (query) matches this Perl regex.
See --match-info.
--[no]ignore-self
default: yes; group: Query Matches
Don’t kill pt-kill‘s own connection.
--ignore-state
type: string; group: Query Matches; default: Locked
Ignore queries whose State matches this Perl regex.  The default is to keepthreads from being killed if they are locked waiting for another thread.
See --match-state.
--ignore-user
type: string; group: Query Matches
Ignore queries whose user matches this Perl regex.
See --match-user.
--match-all
group: Query Matches
Match all queries that are not ignored.  If no ignore options are specified,then every query matches (except replication threads, unless--replication-threads is also specified).  This option allows you tospecify negative matches, i.e. “match every query except...” where theexceptions are defined by specifying various --ignore options.
This option is not the same as --victims all.  This option matchesall queries within a class, whereas --victims all specifies that allmatching queries in a class (however they matched) will be killed.  Normally,however, the two are used together because if, for example, you specify--victims oldest, then although all queries may match, only the oldestwill be killed.
--match-command
type: string; group: Query Matches
Match only queries whose Command matches this Perl regex.
Common Command values are:
QuerySleepBinlog DumpConnectDelayed insertExecuteFetchInit DBKillPrepareProcesslistQuitReset stmtTable Dump
See http://dev.mysql.com/doc/refman/5.1/en/thread-commands.html for a fulllist and description of Command values.
--match-db
type: string; group: Query Matches
Match only queries whose db (database) matches this Perl regex.
--match-host
type: string; group: Query Matches
Match only queries whose Host matches this Perl regex.
The Host value often time includes the port like “host:port”.
--match-info
type: string; group: Query Matches
Match only queries whose Info (query) matches this Perl regex.
The Info column of the processlist shows the query that is being executedor NULL if no query is being executed.
--match-state
type: string; group: Query Matches
Match only queries whose State matches this Perl regex.
Common State values are:
Lockedlogincopy to tmp tableCopying to tmp tableCopying to tmp table on diskCreating tmp tableexecutingReading from netSending dataSorting for orderSorting resultTable lockUpdating
See http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html fora full list and description of State values.
--match-user
type: string; group: Query Matches
Match only queries whose User matches this Perl regex.
--replication-threads
group: Query Matches
Allow matching and killing replication threads.
By default, matches do not apply to replication threads; i.e. replicationthreads are completely ignored.  Specifying this option allows matches tomatch (and potentially kill) replication threads on masters and slaves.
--test-matching
type: array; group: Query Matches
Files with processlist snapshots to test matching options against.  Sincethe matching options can be complex, you can save snapshots of processlistin files, then test matching options against queries in those files.
This option disables --run-time, --interval,and --[no]ignore-self.