MySQL管理工具MySQL Utilities — mysqlprocgrep (29)

MySQL管理工具MySQL Utilities — mysqlprocgrep (29)已关闭评论

A+

所属分类：MySQL

mysqlprocgrep 望文生义搜索进程的。搜索出给定时间内(--age)和指定条件相匹配(--match-xxx)的进程，显示出来或执行某些动作。

如果没有指定--age和--match-xxx选项，打印出所有的进程。

--match-xxx 选项如同INFORMATION_SCHEMA.PROCESSLIST 表列名。

执行该命令需要 PROCESS 和 SUPER 权限。没有PROCESS权限，没法查看其他用户的权限。没有SUPER权限，不能对其他用户的进程执行某些动作。

输出格式

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。

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）

# 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;

# 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）

# 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

# 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分钟内创建的所有进程

# mysqlprocgrep --server=instance_3306 --match-user=nobody --age=1m --kill-query

1	# mysqlprocgrep --server=instance_3306 --match-user=nobody --age=1m --kill-query

杀死所有超过1小时的空闲进程

# mysqlprocgrep --server=instance_3306 --match-command=sleep --age=1h --kill-connection

1	# mysqlprocgrep --server=instance_3306 --match-command=sleep --age=1h --kill-connection

权限

需要对mysql数据库的SELECT权限。

微信公众号

扫一扫关注运维生存时间公众号，获取最新技术文章~

输出格式

选项

实例

权限

登录 注册 找回密码

微信

登录注册找回密码