首页域名资讯 正文

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

2025-01-20 2 0条评论

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

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

–match-xxx 选项如同INFORMATION_SCHEMA.PROCE ssl IST 表列名。

执行该命令需要 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权限。

文章转载来自:trustauth.cn

文章版权及转载声明

本文作者:亿网 网址:https://edns.com/ask/post/150558.html 发布于 2025-01-20
文章转载或复制请以超链接形式并注明出处。