MySQL Utilities (mysqlprocgrep)

by

MySQL Utilities (mysqlprocgrep)
Reference: http://thinkit.co.jp/story/2014/02/10/4814
mysqlprocgrep search process information

mysqluc> mysqlprocgrep --help
MySQL Utilities mysqlprocgrep.exe version 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqlprocgrep.exe --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>].
  --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'.
  -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-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.

mysqluc>

SQLコマンドの場合

mysql> show full processlist;
+----+------+-----------------+------+---------+------+-------+-----------------------+
| Id | User | Host            | db   | Command | Time | State | Info                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
| 41 | root | localhost:53225 | test | Query   |    0 | init  | show full processlist |
| 50 | root | localhost:54535 | NULL | Sleep   | 7197 |       | NULL                  |
| 59 | root | localhost:54658 | NULL | Sleep   | 6030 |       | NULL                  |
| 63 | root | localhost:55078 | NULL | Sleep   | 2937 |       | NULL                  |
| 67 | root | localhost:55114 | NULL | Sleep   | 2498 |       | NULL                  |
| 69 | root | localhost:55122 | NULL | Sleep   | 2386 |       | NULL                  |
| 71 | root | localhost:55135 | NULL | Sleep   | 2242 |       | NULL                  |
| 72 | root | localhost:55217 | NULL | Sleep   | 1808 |       | NULL                  |
| 75 | root | localhost:55382 | NULL | Sleep   | 1079 |       | NULL                  |
| 78 | root | localhost:55394 | NULL | Sleep   |  919 |       | NULL                  |
+----+------+-----------------+------+---------+------+-------+-----------------------+
10 rows in set (0.00 sec)

mysql> select * from information_schema.processlist where time >=10;
+----+------+-----------------+------+---------+------+-------+------+
| ID | USER | HOST            | DB   | COMMAND | TIME | STATE | INFO |
+----+------+-----------------+------+---------+------+-------+------+
| 75 | root | localhost:55382 | NULL | Sleep   | 1201 |       | NULL |
| 69 | root | localhost:55122 | NULL | Sleep   | 2508 |       | NULL |
| 78 | root | localhost:55394 | NULL | Sleep   | 1041 |       | NULL |
| 59 | root | localhost:54658 | NULL | Sleep   | 6152 |       | NULL |
| 71 | root | localhost:55135 | NULL | Sleep   | 2364 |       | NULL |
| 67 | root | localhost:55114 | NULL | Sleep   | 2620 |       | NULL |
| 72 | root | localhost:55217 | NULL | Sleep   | 1930 |       | NULL |
| 50 | root | localhost:54535 | NULL | Sleep   | 7319 |       | NULL |
| 63 | root | localhost:55078 | NULL | Sleep   | 3059 |       | NULL |
+----+------+-----------------+------+---------+------+-------+------+
9 rows in set (0.01 sec)

mysql>

MySQL Utilitiesの場合

mysqluc> mysqlprocgrep --server=root:password@localhost:3306 --age=0:10:00
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
| Connection             | Id  | User  | Host             | Db    | Command  | Time  | State  | Info  |
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
| root:*@localhost:3306  | 75  | root  | localhost:55382  | None  | Sleep    | 1155  |        | None  |
| root:*@localhost:3306  | 69  | root  | localhost:55122  | None  | Sleep    | 2462  |        | None  |
| root:*@localhost:3306  | 78  | root  | localhost:55394  | None  | Sleep    | 995   |        | None  |
| root:*@localhost:3306  | 59  | root  | localhost:54658  | None  | Sleep    | 6106  |        | None  |
| root:*@localhost:3306  | 71  | root  | localhost:55135  | None  | Sleep    | 2318  |        | None  |
| root:*@localhost:3306  | 67  | root  | localhost:55114  | None  | Sleep    | 2574  |        | None  |
| root:*@localhost:3306  | 72  | root  | localhost:55217  | None  | Sleep    | 1884  |        | None  |
| root:*@localhost:3306  | 50  | root  | localhost:54535  | None  | Sleep    | 7273  |        | None  |
| root:*@localhost:3306  | 63  | root  | localhost:55078  | None  | Sleep    | 3013  |        | None  |
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+

mysqluc>


mysqluc> mysqlprocgrep --server=root:password@localhost:3306 --age=0:10:00 --format=vertical
*************************       1. row *************************
 Connection: root:*@localhost:3306
         Id: 75
       User: root
       Host: localhost:55382
         Db: None
    Command: Sleep
       Time: 1255
      State:
       Info: None
*************************       2. row *************************
 Connection: root:*@localhost:3306
         Id: 69
       User: root
       Host: localhost:55122
         Db: None
    Command: Sleep
       Time: 2562
      State:
       Info: None
*************************       3. row *************************
 Connection: root:*@localhost:3306
         Id: 78
       User: root
       Host: localhost:55394
         Db: None
    Command: Sleep
       Time: 1095
      State:
       Info: None
*************************       4. row *************************
 Connection: root:*@localhost:3306
         Id: 59
       User: root
       Host: localhost:54658
         Db: None
    Command: Sleep
       Time: 6206
      State:
       Info: None
*************************       5. row *************************
 Connection: root:*@localhost:3306
         Id: 71
       User: root
       Host: localhost:55135
         Db: None
    Command: Sleep
       Time: 2418
      State:
       Info: None
*************************       6. row *************************
 Connection: root:*@localhost:3306
         Id: 67
       User: root
       Host: localhost:55114
         Db: None
    Command: Sleep
       Time: 2674
      State:
       Info: None
*************************       7. row *************************
 Connection: root:*@localhost:3306
         Id: 72
       User: root
       Host: localhost:55217
         Db: None
    Command: Sleep
       Time: 1984
      State:
       Info: None
*************************       8. row *************************
 Connection: root:*@localhost:3306
         Id: 50
       User: root
       Host: localhost:54535
         Db: None
    Command: Sleep
       Time: 7373
      State:
       Info: None
*************************       9. row *************************
 Connection: root:*@localhost:3306
         Id: 63
       User: root
       Host: localhost:55078
         Db: None
    Command: Sleep
       Time: 3113
      State:
       Info: None
9 rows.

mysqluc>

Kill Optionを付けると指定した条件の接続をKillする事が出来る。


mysqluc> mysqlprocgrep --server=root:password@localhost:3306 --age=0:10:00 --match-command='Sleep'
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
| Connection             | Id  | User  | Host             | Db    | Command  | Time  | State  | Info  |
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
| root:*@localhost:3306  | 75  | root  | localhost:55382  | None  | Sleep    | 2203  |        | None  |
| root:*@localhost:3306  | 69  | root  | localhost:55122  | None  | Sleep    | 3510  |        | None  |
| root:*@localhost:3306  | 78  | root  | localhost:55394  | None  | Sleep    | 2043  |        | None  |
| root:*@localhost:3306  | 59  | root  | localhost:54658  | None  | Sleep    | 7154  |        | None  |
| root:*@localhost:3306  | 71  | root  | localhost:55135  | None  | Sleep    | 3366  |        | None  |
| root:*@localhost:3306  | 67  | root  | localhost:55114  | None  | Sleep    | 3622  |        | None  |
| root:*@localhost:3306  | 72  | root  | localhost:55217  | None  | Sleep    | 2932  |        | None  |
| root:*@localhost:3306  | 50  | root  | localhost:54535  | None  | Sleep    | 8321  |        | None  |
| root:*@localhost:3306  | 63  | root  | localhost:55078  | None  | Sleep    | 4061  |        | None  |
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
mysqluc>
mysqluc> mysqlprocgrep --server=root:password@localhost:3306 --age=2:10:00 --match-command='Sleep'
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
| Connection             | Id  | User  | Host             | Db    | Command  | Time  | State  | Info  |
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
| root:*@localhost:3306  | 50  | root  | localhost:54535  | None  | Sleep    | 8377  |        | None  |
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
mysqluc> mysqlprocgrep --server=root:password@localhost:3306 --age=2:10:00 --match-command='Sleep' --kill-connection
mysqluc>
mysqluc> mysqlprocgrep --server=root:password@localhost:3306 --age=00:00:00 --match-command='Sleep'
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
| Connection             | Id  | User  | Host             | Db    | Command  | Time  | State  | Info  |
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+
| root:*@localhost:3306  | 41  | root  | localhost:53225  | test  | Sleep    | 209   |        | None  |
| root:*@localhost:3306  | 75  | root  | localhost:55382  | None  | Sleep    | 2377  |        | None  |
| root:*@localhost:3306  | 69  | root  | localhost:55122  | None  | Sleep    | 3684  |        | None  |
| root:*@localhost:3306  | 78  | root  | localhost:55394  | None  | Sleep    | 2217  |        | None  |
| root:*@localhost:3306  | 59  | root  | localhost:54658  | None  | Sleep    | 7328  |        | None  |
| root:*@localhost:3306  | 71  | root  | localhost:55135  | None  | Sleep    | 3540  |        | None  |
| root:*@localhost:3306  | 67  | root  | localhost:55114  | None  | Sleep    | 3796  |        | None  |
| root:*@localhost:3306  | 72  | root  | localhost:55217  | None  | Sleep    | 3106  |        | None  |
| root:*@localhost:3306  | 63  | root  | localhost:55078  | None  | Sleep    | 4235  |        | None  |
+------------------------+-----+-------+------------------+-------+----------+-------+--------+-------+

mysqluc>

複数のプロセスを纏めてKillしようとしたら、Errorになってしまった。
また、時間ある時に確認してみる。

Posted in: DB, MYSQL, 運用 ⋅ Tagged: , ,

Comments are closed.

Post Navigation