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になってしまった。
また、時間ある時に確認してみる。