62.MySQL-Utilities工具包之-mysqlprocgrep进程抓取工具

1.生成杀死用户是root 的空闲进程(存储过程),不包含CREATE PROCEDURE语句。
[root@mysql1 ~]# 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;

2.生成杀死用户是root 的空闲进程的存储过程,包含CREATE PROCEDURE语句。
[root@mysql1 ~]# mysqlprocgrep --match-user=root --kill-connection --match-state=sleep --print-sql -vvv
use mysql; 
drop  PROCEDURE kill_processes;

delimiter //
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
//

3.10分钟内创建的所有进程。
[root@mysql1 ~]# mysqlprocgrep --server=root:rootroot@192.168.1.51:3306 --match-user=root --age=10m --kill-query
WARNING: Using a password on the command line interface can be insecure.
# The following KILL commands were executed:
+-----+-------+------------+-------+----------+-------+--------+-------+
| Id  | User  | Host       | db    | Command  | Time  | State  | Info  |
+-----+-------+------------+-------+----------+-------+--------+-------+
| 35  | root  | localhost  | test  | Sleep    | 3033  |        | None  |
+-----+-------+------------+-------+----------+-------+--------+-------+

4.杀死所有超过1 小时的空闲进程。谨慎执行。
mysqlprocgrep --server=root:rootroot@192.168.1.51:3306 --match-command=sleep --age=1h --kill-connection

delimiter //
DECLARE kill_done INT;
DECLARE kill_cursor CURSOR FOR SELECT Id, User, Host, Db, Command, Time, State, Info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND LIKE 'update%' AND USER LIKE 'root%';
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;
// 

5.总结。

mysqlprocgrep 进程抓取工具，看起来好用，但是生产的存储过程，执行各种报错，不是一个完成的不报错的语句。所以这个语句不是很实用。当然我们可以通过截取其中的SQL语句，然后手工生成相关的进程过去信息。

SELECT Id, User, Host, Db, Command, Time, State, Info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND LIKE 'update%' AND USER LIKE 'root%';