mysqlprocgrep

mysqlprocgrep扫描mysql的线程并对线程进行处理

$ ./mysqlprocgrep --help
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
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>].
  -G, --basic-regexp, --regexp
                        use 'REGEXP' operator to match pattern. Default is to
                        use 'LIKE'.
  -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.
  --sql-body            only print the body of the procedure.
  --kill-connection     kill all matching connections.
  --kill-query          kill query for all matching processes.
  --print               print all matching processes.
  -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-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
root@luoxuan 02:23:50>show processlist;
+--------+-------------+---------------------+---------+-------------+-------+----------------------------------------
| Id     | USER        | Host                | db      | Command     | TIME  | State                                                                |
+--------+-------------+---------------------+---------+-------------+-------+----------------------------------------
| 644208 | slave       | xx.xx.xx.xx:58753 | NULL    | Binlog Dump | 94397 | Master has sent ALL binlog TO slave; waiting FOR binlog TO be updated|
| 644265 | slave       | xx.xx.xx.xx:58765 | NULL    | Binlog Dump | 94371 | Master has sent ALL binlog TO slave; waiting FOR binlog TO be updated|
| 828694 | luoxuan     | yy.yy.yy.yy:64773 | luoxuan | Sleep       |     6 |                                                                      |
+--------+-------------+---------------------+---------+-------------+-------+----------------------------------------

eg1:展示luoxuan库相关线程

$ ./mysqlprocgrep --server=root@localhost --match-db=luoxuan
+------------------------+---------+----------+----------------------+----------+----------+-------+--------+-------+
| Connection             | Id      | User     | Host                 | Db       | Command  | Time  | State  | Info  |
+------------------------+---------+----------+----------------------+----------+----------+-------+--------+-------+
| root:*@localhost:3306  | 828694  | luoxuan  | yy.yy.yy.yy:64773  | luoxuan  | Sleep    | 131   |        | None  |
| root:*@localhost:3306  | 828539  | root     | localhost            | luoxuan  | Sleep    | 29    |        | None  |
+------------------------+---------+----------+----------------------+----------+----------+-------+--------+-------+

eg2:杀掉连接luoxuan库,并且空闲时间大于10S的线程

$ ./mysqlprocgrep --server=root@localhost --match-db=luoxuan  --match-command=sleep --age=10s --kill-connection
ERROR: Unread result found.

因为mysqlprocgrep本地跑的时间,会产生一个线程,同时要杀掉自己(死循环了)

解决办法:过滤掉本地跑的线程

$ ./mysqlprocgrep --server=root@localhost --match-db=luoxuan  --match-user=luoxuan --match-command=sleep --age=10s --kill-connection

线程已杀掉

$ ./mysqlprocgrep --server=root@localhost --match-db=luoxuan 
+------------------------+---------+-------+------------+----------+----------+-------+--------+-------+
| Connection             | Id      | User  | Host       | Db       | Command  | Time  | State  | Info  |
+------------------------+---------+-------+------------+----------+----------+-------+--------+-------+
| root:*@localhost:3306  | 828539  | root  | localhost  | luoxuan  | Sleep    | 35    |        | None  |
+------------------------+---------+-------+------------+----------+----------+-------+--------+-------+

Post a Comment