mysqlprocgrep(1)

1MYSQLPROCGREP(1)                MySQL Utilities               MYSQLPROCGREP(1)
2
3
4

NAME

6       mysqlprocgrep - Search Server Process Lists
7

SYNOPSIS

9       mysqlprocgrep [options]
10

DESCRIPTION

12       This utility scans the process lists for the servers specified using
13       instances of the --server option and selects those that match the
14       conditions specified using the --age and --match-xxx options. For a
15       process to match, all conditions given must match. The utility then
16       either prints the selected processes (the default) or executes certain
17       actions on them.
18
19       If no --age or --match-xxx options are given, the utility selects all
20       processes.
21
22       The --match-xxx options correspond to the columns in the
23       INFORMATION_SCHEMA.PROCESSLIST table. For example, --match-command
24       specifies a matching condition for PROCESSLIST.COMMAND column values.
25       There is no --match-time option. To specify a condition based on
26       process time, use --age.
27
28       Processes that can be seen and killed are subject to whether the
29       account used to connect to the server has the PROCESS and SUPER
30       privileges. Without PROCESS, the account cannot see processes belonging
31       to other accounts Without SUPER, the account cannot kill processes
32       belonging to other accounts
33
34       To specify how to display output, use one of the following values with
35       the --format option:
36
37       ·   grid (default)
38
39           Display output in grid or table format like that of the mysql
40           client command-line tool.
41
42       ·   csv
43
44           Display output in comma-separated values format.
45
46       ·   tab
47
48           Display output in tab-separated format.
49
50       ·   vertical
51
52           Display output in single-column format like that of the \G command
53           for the mysql client command-line tool.
54       Options.PP mysqlprocgrep accepts the following command-line options:
55
56       ·   --help
57
58           Display a help message and exit.
59
60       ·   --license
61
62           Display license information and exit.
63
64       ·   --age=<time>
65
66           Select only processes that have been in the current state more than
67           a given time. The time value can be specified in two formats:
68           either using the hh:mm:ss format, with hours and minutes optional,
69           or as a sequence of numbers with a suffix giving the period size.
70
71           The permitted suffixes are s (second), m (minute), h (hour), d
72           (day), and w (week). For example, 4h15m represents 4 hours and 15
73           minutes.
74
75           For both formats, the specification can optionally be preceded by +
76           or -, where + means older than the given time, and - means younger
77           than the given time.
78
79       ·   --character-set=<charset>
80
81           Sets the client character set. The default is retrieved from the
82           server variable character_set_client.
83
84       ·   --format=<format>, -f<format>
85
86           Specify the output display format. Permitted format values are grid
87           (default), csv, tab, and vertical.
88
89       ·   --kill-connection
90
91           Kill the connection for all matching processes (like the KILL
92           CONNECTION statement).
93
94       ·   --kill-query
95
96           Kill the query for all matching processes (like the KILL QUERY
97           statement).
98
99       ·   --match-command=<pattern>
100
101           Match all processes where the Command field matches the pattern.
102
103       ·   --match-db=<pattern>
104
105           Match all processes where the Db field matches the pattern.
106
107       ·   --match-host=<pattern>
108
109           Match all processes where the Host field matches the pattern.
110
111       ·   --match-id=<pattern>
112
113           Match all processes where the ID field matches the pattern.
114
115       ·   --match-info=<pattern>
116
117           Match all processes where the Info field matches the pattern.
118
119       ·   --match-state=<pattern>
120
121           Match all processes where the State field matches the pattern.
122
123       ·   --match-user=<pattern>
124
125           Match all processes where the User field matches the pattern.
126
127       ·   --print
128
129           Print information about the matching processes. This is the default
130           if no --kill-connection or --kill-query option is given. If a kill
131           option is given, --print prints information about the processes
132           before killing them.
133
134       ·   --regexp, --basic-regexp, -G
135
136           Perform pattern matches using the REGEXP operator. The default is
137           to use LIKE for matching. This affects the --match-xxx options.
138
139       ·   --server=<source>
140
141           Connection information for a server. Use this option multiple times
142           to search multiple servers.
143
144           To connect to a server, it is necessary to specify connection
145           parameters such as user name, host name, password, and either a
146           port or socket. MySQL Utilities provides a number of ways to
147           provide this information. All of the methods require specifying
148           your choice via a command-line option such as --server, --master,
149           --slave, etc. The methods include the following in order of most
150           secure to least secure.
151
152           ·   Use login-paths from your .mylogin.cnf file (encrypted, not
153               visible). Example : <login-path>[:<port>][:<socket>]
154
155           ·   Use a configuration file (unencrypted, not visible) Note:
156               available in release-1.5.0. Example :
157               <configuration-file-path>[:<section>]
158
159           ·   Specify the data on the command-line (unencrypted, visible).
160               Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
161
162
163       ·   --sql, --print-sql, -Q
164
165           Instead of displaying the selected processes, emit the SELECT
166           statement that retrieves information about them. If the
167           --kill-connection or --kill-query option is given, the utility
168           generates a stored procedure named kill_processes() for killing the
169           queries rather than a SELECT statement.
170
171       ·   --sql-body
172
173           Like --sql, but produces the output as the body of a stored
174           procedure without the CREATE PROCEDURE part of the definition. This
175           could be used, for example, to generate an event for the server
176           Event Manager.
177
178           When used with a kill option, code for killing the matching queries
179           is generated. Note that it is not possible to execute the emitted
180           code unless it is put in a stored routine, event, or trigger. For
181           example, the following code could be generated to kill all idle
182           connections for user www-data:
183
184               shell> mysqlprocgrep --kill-connection --sql-body \
185                         --match-user=www-data --match-state=sleep
186               DECLARE kill_done INT;
187               DECLARE kill_cursor CURSOR FOR
188                 SELECT
189                       Id, User, Host, Db, Command, Time, State, Info
190                     FROM
191                       INFORMATION_SCHEMA.PROCESSLIST
192                     WHERE
193                         user LIKE 'www-data'
194                       AND
195                         State LIKE 'sleep'
196               OPEN kill_cursor;
197               BEGIN
198                  DECLARE id BIGINT;
199                  DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
200                  kill_loop: LOOP
201                     FETCH kill_cursor INTO id;
202                     KILL CONNECTION id;
203                  END LOOP kill_loop;
204               END;
205               CLOSE kill_cursor;
206
207       ·   --ssl-ca
208
209           The path to a file that contains a list of trusted SSL CAs.
210
211       ·   --ssl-cert
212
213           The name of the SSL certificate file to use for establishing a
214           secure connection.
215
216       ·   --ssl-cert
217
218           The name of the SSL key file to use for establishing a secure
219           connection.
220
221       ·   --ssl
222
223           Specifies if the server connection requires use of SSL. If an
224           encrypted connection cannot be established, the connection attempt
225           fails. Default setting is 0 (SSL not required).
226
227       ·   --verbose, -v
228
229           Specify how much information to display. Use this option multiple
230           times to increase the amount of information. For example, -v =
231           verbose, -vv = more verbose, -vvv = debug.
232
233       ·   --version
234
235           Display version information and exit.
236       NOTES.PP For the --format option, the permitted values are not case
237       sensitive. In addition, values may be specified as any unambiguous
238       prefix of a valid value. For example, --format=g specifies the grid
239       format. An error occurs if a prefix matches more than one valid value.
240
241       The path to the MySQL client tools should be included in the PATH
242       environment variable in order to use the authentication mechanism with
243       login-paths. This will allow the utility to use the my_print_defaults
244       tools which is required to read the login-path values from the login
245       configuration file (.mylogin.cnf).  EXAMPLES.PP For each example,
246       assume that the root user on localhost has sufficient privileges to
247       kill queries and connections.
248
249       Kill all queries created by user john that are less than 1 minute:
250
251           shell> mysqlprocgrep --server=root@localhost \
252                     --match-user=john --age=-1m --kill-query
253
254       Kill all connections that have been idle for more than 1 hour:
255
256           shell> mysqlprocgrep --server=root@localhost \
257                     --match-command=sleep --age=1h --kill-connection
258
259       PERMISSIONS REQUIRED.PP The user must have the SELECT privilege on the
260       mysql database.
261

COPYRIGHT

263       Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
264       reserved.
265
266       This documentation is free software; you can redistribute it and/or
267       modify it only under the terms of the GNU General Public License as
268       published by the Free Software Foundation; version 2 of the License.
269
270       This documentation is distributed in the hope that it will be useful,
271       but WITHOUT ANY WARRANTY; without even the implied warranty of
272       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
273       General Public License for more details.
274
275       You should have received a copy of the GNU General Public License along
276       with the program; if not, write to the Free Software Foundation, Inc.,
277       51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
278       http://www.gnu.org/licenses/.
279
280

AUTHOR

287       Oracle Corporation (http://dev.mysql.com/).
288
289
290
291MySQL 1.5.6                       09/15/2015                  MYSQLPROCGREP(1)

NAME

SYNOPSIS

DESCRIPTION

COPYRIGHT

SEE ALSO

AUTHOR