mysqlprocgrep 望文生义搜索进程的。搜索出给定时间内(–age)和指定条件相匹配(–match-xxx)的进程，显示出来或执行某些动作。
如果没有指定–age和–match-xxx选项，打印出所有的进程。
–match-xxx 选项如同INFORMATION_SCHEMA.PROCESSLIST 表列名。
执行该命令需要 PROCESS 和 SUPER 权限。没有PROCESS权限，没法查看其他用户的权限。没有SUPER权限，不能对其他用户的进程执行某些动作。
输出格式
- grid (default)
- csv
- tab
- vertical
选项
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
MySQL Utilities mysqlprocgrep version 1.5.3
License type: GPLv2
Usage: mysqlprocgrep —server=user:pass@host:port:socket [options]
mysqlprocgrep – search process information
Options:
—version show program‘s version number and exit
–help display a help message and exit
–license display program’s license and exit
—server=SERVER connection information for the server in the form:
<user>[:<password>]@<host>[:<port>][:<socket>] or
<login–path>[:<port>][:<socket>] or <config–
path>[<[group]>].
—ssl–ca=SSL_CA The path to a file that contains a list of trusted SSL
CAs.
—ssl–cert=SSL_CERT The name of the SSL certificate file to use for
establishing a secure connection.
—ssl–key=SSL_KEY The name of the SSL key file to use for establishing a
secure connection.
—character–set=CHARSET
sets the client character set. The default is
retrieved from the server variable
‘character_set_client’.
–G, —basic–regexp, —regexp
use ‘REGEXP’ operator to match pattern. Default is to
use ‘LIKE’.影响—match–xxx选项。
–Q, —print–sql, —sql
print the statement instead of sending it to the
server. If a kill option is submitted, a procedure
will be generated containing the code for executing
the kill.生成一个存储过程命名为kill_processes() 而不是SELECT语句。
—sql–body only print the body of the procedure.只输出存储过程体，不会含有
CREATE PROCEDURE定义部分。
—kill–connection kill all matching connections.
—kill–query kill query for all matching processes.
—print print all matching processes.如果没有指定—kill–connection
或 —kill–query 选项是默认的。如果有指定kill选项，在kill之前打印。
–f FORMAT, —format=FORMAT
display the output in either grid (default), tab, csv,
or vertical format
–v, —verbose control how much information is displayed. e.g., –v =
verbose, –vv = more verbose, –vvv = debug
—match–id=PATTERN match the ‘ID’ column of the PROCESSLIST table.
—match–user=PATTERN match the ‘USER’ column of the PROCESSLIST table.
—match–host=PATTERN match the ‘HOST’ column of the PROCESSLIST table.
—match–db=PATTERN match the ‘DB’ column of the PROCESSLIST table.
—match–command=PATTERN
match the ‘COMMAND’ column of the PROCESSLIST table.
—match–info=PATTERN match the ‘INFO’ column of the PROCESSLIST table.
—match–state=PATTERN
match the ‘STATE’ column of the PROCESSLIST table.
—age=AGE show only processes that have been in the current
state more than a given time.只选择超过当前给定时间的进程。两种
格式： hh:mm:ss或数字后面跟上时间单位，后缀可以有 s (second),
m (minute), h (hour), d (day), w (week)。如4h15m。
|
实例
生成杀死用户是pro_user的空闲进程的存储过程（不含CREATE PROCEDURE）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
# mysqlprocgrep –match-user=root –kill-connection –match-state=sleep –sql-body
DECLARE kill_done INT;
DECLARE kill_cursor CURSOR FOR
SELECT
Id, User, Host, Db, Command, Time, State, Info
FROM
INFORMATION_SCHEMA.PROCESSLIST
WHERE
USER LIKE ‘root’
AND
STATE LIKE ‘sleep’
OPEN kill_cursor;
BEGIN
DECLARE id BIGINT;
DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
kill_loop: LOOP
FETCH kill_cursor INTO id;
KILL CONNECTION id;
END LOOP kill_loop;
END;
CLOSE kill_cursor;
|
生成杀死用户是pro_user的空闲进程的存储过程（含CREATE PROCEDURE）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
# mysqlprocgrep –match-user=root –kill-connection –match-state=sleep –print-sql -vvv
CREATE PROCEDURE kill_processes ()
BEGIN
DECLARE kill_done INT;
DECLARE kill_cursor CURSOR FOR
SELECT
Id, User, Host, Db, Command, Time, State, Info
FROM
INFORMATION_SCHEMA.PROCESSLIST
WHERE
USER LIKE ‘root’
AND
STATE LIKE ‘sleep’
OPEN kill_cursor;
BEGIN
DECLARE id BIGINT;
DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
kill_loop: LOOP
FETCH kill_cursor INTO id;
KILL CONNECTION id;
END LOOP kill_loop;
END;
CLOSE kill_cursor;
END
|
消灭用户nobody在1分钟内创建的所有进程
|
1
|
# mysqlprocgrep –server=instance_3306 –match-user=nobody –age=1m –kill-query
|
消灭所有超过1小时的空闲进程
|
1
|
# mysqlprocgrep –server=instance_3306 –match-command=sleep –age=1h –kill-connection
|
权限
需要对mysql数据库的SELECT权限。
文章转载来自：ttlsa.com
