MySQL管理工具MySQL Utilities — mysqlprocgrep (29)
亿网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