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

默北 MySQLMySQL管理工具MySQL Utilities — mysqlprocgrep (29)已关闭评论8,626字数 3708阅读12分21秒阅读模式

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/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
默北
  • 本文由 发表于 13/03/2015 01:00:22
  • 转载请务必保留本文链接:https://www.ttlsa.com/mysql/mysql-utilities-mysqlprocgrep/