mysqlprocgrep 望文生义搜索进程的。搜索出给定时间内(--age)和指定条件相匹配(--match-xxx)的进程，显示出来或执行某些动作。
如果没有指定--age和--match-xxx选项，打印出所有的进程。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/
--match-xxx 选项如同INFORMATION_SCHEMA.PROCESSLIST 表列名。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/
执行该命令需要 PROCESS 和 SUPER 权限。没有PROCESS权限，没法查看其他用户的权限。没有SUPER权限，不能对其他用户的进程执行某些动作。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/
输出格式
- grid (default)
- csv
- tab
- vertical
选项
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）文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/
# 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）文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/
# 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分钟内创建的所有进程文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/
# mysqlprocgrep --server=instance_3306 --match-user=nobody --age=1m --kill-query
杀死所有超过1小时的空闲进程文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/
# mysqlprocgrep --server=instance_3306 --match-command=sleep --age=1h --kill-connection
权限
需要对mysql数据库的SELECT权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/
评论