1MYSQLPROCGREP(1) MySQL Utilities MYSQLPROCGREP(1)
2
6 mysqlprocgrep - Search MySQL servers for processes matching a pattern
7
9 mysqlprocgrep [ --version | --help ] | --format=<format> |
10 --kill-connection | --kill-query | --regexp | --sql-body |
11 --print | --verbose | --match-user=<pattern> |
12 --match-host=<pattern> | --match-command=<pattern> |
13 --match-info=<pattern> | --match-state=<pattern> |
14 --server=<user>[<passwd>]@<host>:[<port>][:<socket>]
15
17 This utility scan the process lists for all the servers provided via
18 repeated occurrences of the --server option and will either print the
19 result (the default) or execute certain actions on it. The match condi‐
20 tions are given as options to the tool and in order for a row to match,
21 all the conditions given have to match.
22 You also have the choice to view the output in one of the following
24 formats using the --format option.
25 GRID
27 Displays output formatted like that of the mysql monitor in a
29 grid or table layout. This is the default.
30 CSV
32 Displays the output in a comma-separated list.
34 TAB
36 Displays the output in a tab-separated list.
38 VERTICAL
40 Displays the output in a single column similar to the \G option
42 for the mysql monitor commands.
43
45 --help, -h
46 Print help
48 --match-user <pattern>
50 Match all rows where the User field matches pattern
52 --match-host <pattern>
54 Match all rows where the Host field matches pattern
56 --match-db <pattern>
58 Match all rows where the Db field matches pattern
60 --match-time <pattern>
62 Match all rows where the Time field matches pattern
64 --match-command <pattern>
66 Match all rows where the Command field matches pattern
68 --match-state <pattern>
70 Match all rows where the State field matches pattern.
72 --match-info <pattern>
74 Match all rows where the Info field matches pattern.
76 --kill-connection
78 Kill the connection for all matching processes.
79 --kill-query
81 Kill the query for all matching processes.
82 --print
84 Print information about the matching processes. This is the
85 default if no --kill-connection or --kill-query option is given.
86 If a kill option is given, this option will print information
87 about the processes before killing them.
88 --verbose, -v
90 Be more verbose and print messages about execution. Can be given
92 multiple times, in which case the verbosity level increases.
93 For example, -v = verbose, -vv = more verbose, -vvv = debug
94 --regexp, --basic-regexp, -G
96 Use REGEXP operator to match patterns instead of LIKE.
98 --sql, --print-sql, -Q
100 Emit the SQL for matching or killing the queries. If the
102 --kill-connection or --kill-query option is given, a routine for
103 killing the queries are generated.
104 --sql-body
106 Emit SQL statements for performing the search or kill of the
107 INFORMATION_SCHEMA.PROCESSLIST table. This is useful together
108 with mysqlmkevent(1) to generate an event for the server sched‐
109 uler.
110 When used with a kill option, code for killing the matching
112 queries are generated. Note that it is not possible to execute
113 the emitted code unless it is put in a stored routine, event, or
114 trigger. For example, the following code could be generated to
115 kill all connections for user www-data that is idle:
116 $ mysqlprocgrep --kill-connection --sql-body \
118 > --match-user=www-data --match-state=sleep
119 DECLARE kill_done INT;
120 DECLARE kill_cursor CURSOR FOR
121 SELECT
122 Id, User, Host, Db, Command, Time, State, Info
123 FROM
124 INFORMATION_SCHEMA.PROCESSLIST
125 WHERE
126 user LIKE 'www-data'
127 AND
128 State LIKE 'sleep'
129 OPEN kill_cursor;
130 BEGIN
131 DECLARE id BIGINT;
132 DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
133 kill_loop: LOOP
134 FETCH kill_cursor INTO id;
135 KILL CONNECTION id;
136 END LOOP kill_loop;
137 END;
138 CLOSE kill_cursor;
139 --format <format>, -f <format>
141 display the output in either GRID (default), TAB, CSV, or VERTI‐
143 CAL format
144 Specifying time
146 Time for the --age option can be specified in two formats: either using
147 the hh:mm:ss format, with hours and minutes optional, or as a sequence
148 of numbers with a suffix giving the period size.
149 The allowable suffixes are s (second), m (minute), h (hour), d (day),
151 and w (week), so 4h15m mean 4 hours and 15 minutes.
152 For both formats, the specification can optionally be preceeded by a +
154 or a -, where a + means older than the given time, and - means younger
155 than the given age.
156
158 For all the examples, we assume that the root user on localhost has
159 sufficient privileges to kill queries and connections.
160 To kill all connections created by user "mats" that are younger than 1
162 minute:
163 mysqlprocgrep --server=root@localhost --match-user=mats --age=1m --kill-query
165 To kill all queries that has been idle for more than 1 hour:
167 mysqlprocgrep --server=root@localhost --match-command=sleep --age=1h --kill
169
171 Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
172 This program is free software; you can redistribute it and/or modify it
174 under the terms of the GNU General Public License as published by the
175 Free Software Foundation; version 2 of the License.
176 This program is distributed in the hope that it will be useful, but
178 WITHOUT ANY WARRANTY; without even the implied warranty of MER‐
179 CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
180 Public License for more details.
181 You should have received a copy of the GNU General Public License along
183 with this program; if not, write to the Free Software Foundation, Inc.,
184 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
185
187 MySQL Utilities Team
188
190 2010, Oracle and/or its affiliates. All rights reserved.
1911.0.1 September 23, 2011 MYSQLPROCGREP(1)