'\" t
.\"     Title: \fBmysqlprocgrep\fR
.\"    Author: [FIXME: author] [see http://docbook.sf.net/el/author]
.\" Generator: DocBook XSL Stylesheets v1.79.1 <http://docbook.sf.net/>
.\"      Date: 08/01/2016
.\"    Manual: MySQL Utilities
.\"    Source: MySQL 1.6.3
.\"  Language: English
.\"
.TH "\FBMYSQLPROCGREP\FR" "1" "08/01/2016" "MySQL 1\&.6\&.3" "MySQL Utilities"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
mysqlprocgrep \- Search Server Process Lists
.SH "SYNOPSIS"
.HP \w'\fBmysqlprocgrep\ [\fR\fB\fIoptions\fR\fR\fB]\fR\ 'u
\fBmysqlprocgrep [\fR\fB\fIoptions\fR\fR\fB]\fR
.SH "DESCRIPTION"
.PP
This utility scans the process lists for the servers specified using instances of the
\fB\-\-server\fR
option and selects those that match the conditions specified using the
\fB\-\-age\fR
and
\-\-match\-xxx
options\&. For a process to match, all conditions given must match\&. The utility then either prints the selected processes (the default) or executes certain actions on them\&.
.PP
If no
\fB\-\-age\fR
or
\-\-match\-xxx
options are given, the utility selects all processes\&.
.PP
The
\-\-match\-xxx
options correspond to the columns in the
\fBINFORMATION_SCHEMA\&.PROCESSLIST\fR
table\&. For example,
\fB\-\-match\-command\fR
specifies a matching condition for
\fBPROCESSLIST\&.COMMAND\fR
column values\&. There is no
\-\-match\-time
option\&. To specify a condition based on process time, use
\fB\-\-age\fR\&.
.PP
Processes that can be seen and killed are subject to whether the account used to connect to the server has the
\fBPROCESS\fR
and
\fBSUPER\fR
privileges\&. Without
\fBPROCESS\fR, the account cannot see processes belonging to other accounts Without
\fBSUPER\fR, the account cannot kill processes belonging to other accounts\&.
.PP
When the
\fB\-\-kill\-query\fR
or
\fB\-\-kill\-connection\fR
option is used, the utility will display those rows from the
SHOW PROCESSLIST
that match the query and are killed\&. This behavior exists as of MySQL Utilities 1\&.6\&.0\&.
.PP
To specify how to display output, use one of the following values with the
\fB\-\-format\fR
option:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBgrid\fR
(default)
.sp
Display output in grid or table format like that of the
\fBmysql\fR
client command\-line tool\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBcsv\fR
.sp
Display output in comma\-separated values format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBtab\fR
.sp
Display output in tab\-separated format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBvertical\fR
.sp
Display output in single\-column format like that of the
\eG
command for the
\fBmysql\fR
client command\-line tool\&.
.RE
Options.PP
\fBmysqlprocgrep\fR
accepts the following command\-line options:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-help
.sp
Display a help message and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-license
.sp
Display license information and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-age=<time>
.sp
Select only processes that have been in the current state more than a given time\&. The time value can be specified in two formats: either using the
hh:mm:ss
format, with hours and minutes optional, or as a sequence of numbers with a suffix giving the period size\&.
.sp
The permitted suffixes are
\fBs\fR
(second),
\fBm\fR
(minute),
\fBh\fR
(hour),
\fBd\fR
(day), and
\fBw\fR
(week)\&. For example,
\fB4h15m\fR
represents 4 hours and 15 minutes\&.
.sp
For both formats, the specification can optionally be preceded by
+
or
\-, where
+
means older than the given time, and
\-
means younger than the given time\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-character\-set=<charset>
.sp
Sets the client character set\&. The default is retrieved from the server variable
character_set_client\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-format=<format>, \-f<format>
.sp
Specify the output display format\&. Permitted format values are
\fBgrid\fR
(default),
\fBcsv\fR,
\fBtab\fR, and
\fBvertical\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-kill\-connection
.sp
Kill the connection for all matching processes (like the
KILL CONNECTION
statement)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-kill\-query
.sp
Kill the query for all matching processes (like the
KILL QUERY
statement)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-match\-command=<pattern>
.sp
Match all processes where the
\fBCommand\fR
field matches the pattern\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-match\-db=<pattern>
.sp
Match all processes where the
\fBDb\fR
field matches the pattern\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-match\-host=<pattern>
.sp
Match all processes where the
\fBHost\fR
field matches the pattern\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-match\-id=<pattern>
.sp
Match all processes where the
\fBID\fR
field matches the pattern\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-match\-info=<pattern>
.sp
Match all processes where the
\fBInfo\fR
field matches the pattern\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-match\-state=<pattern>
.sp
Match all processes where the
\fBState\fR
field matches the pattern\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-match\-user=<pattern>
.sp
Match all processes where the
\fBUser\fR
field matches the pattern\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-print
.sp
Print information about the matching processes\&. This is the default if no
\fB\-\-kill\-connection\fR
or
\fB\-\-kill\-query\fR
option is given\&. If a kill option is given,
\fB\-\-print\fR
prints information about the processes before killing them\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-regexp, \-\-basic\-regexp, \-G
.sp
Perform pattern matches using the
\fBREGEXP\fR
operator\&. The default is to use
\fBLIKE\fR
for matching\&. This affects the
\-\-match\-xxx
options\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-server=<source>
.sp
Connection information for a server\&. Use this option multiple times to search multiple servers\&.
.sp
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket\&. MySQL Utilities provides a number of ways to supply this information\&. All of the methods require specifying your choice via a command\-line option such as \-\-server, \-\-master, \-\-slave, etc\&. The methods include the following in order of most secure to least secure\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example : <\fIlogin\-path\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example : <\fIconfiguration\-file\-path\fR>[:<\fIsection\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example : <\fIuser\fR>[:<\fIpasswd\fR>]@<\fIhost\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-sql, \-\-print\-sql, \-Q
.sp
Instead of displaying the selected processes, emit the
\fBSELECT\fR
statement that retrieves information about them\&. If the
\fB\-\-kill\-connection\fR
or
\fB\-\-kill\-query\fR
option is given, the utility generates a stored procedure named
kill_processes()
for killing the queries rather than a
\fBSELECT\fR
statement\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-sql\-body
.sp
Like
\fB\-\-sql\fR, but produces the output as the body of a stored procedure without the
\fBCREATE PROCEDURE\fR
part of the definition\&. This could be used, for example, to generate an event for the server Event Manager\&.
.sp
When used with a kill option, code for killing the matching queries is generated\&. Note that it is not possible to execute the emitted code unless it is put in a stored routine, event, or trigger\&. For example, the following code could be generated to kill all idle connections for user
www\-data:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlprocgrep \-\-kill\-connection \-\-sql\-body \e\fR
          \fB\-\-match\-user=www\-data \-\-match\-state=sleep\fR
DECLARE kill_done INT;
DECLARE kill_cursor CURSOR FOR
  SELECT
        Id, User, Host, Db, Command, Time, State, Info
      FROM
        INFORMATION_SCHEMA\&.PROCESSLIST
      WHERE
          user LIKE \*(Aqwww\-data\*(Aq
        AND
          State LIKE \*(Aqsleep\*(Aq
OPEN kill_cursor;
BEGIN
   DECLARE id BIGINT;
   DECLARE EXIT HANDLER FOR NOT FOUND SET kill_done = 1;
   kill_loop: LOOP
      FETCH kill_cursor INTO id;
      KILL CONNECTION id;
   END LOOP kill_loop;
END;
CLOSE kill_cursor;
.fi
.if n \{\
.RE
.\}
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-ca
.sp
The path to a file that contains a list of trusted SSL CAs\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-cert
.sp
The name of the SSL certificate file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-cert
.sp
The name of the SSL key file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl
.sp
Specifies if the server connection requires use of SSL\&. If an encrypted connection cannot be established, the connection attempt fails\&. Default setting is 0 (SSL not required)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-verbose, \-v
.sp
Specify how much information to display\&. Use this option multiple times to increase the amount of information\&. For example,
\fB\-v\fR
= verbose,
\fB\-vv\fR
= more verbose,
\fB\-vvv\fR
= debug\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-version
.sp
Display version information and exit\&.
.RE
NOTES.PP
For the
\fB\-\-format\fR
option, the permitted values are not case sensitive\&. In addition, values may be specified as any unambiguous prefix of a valid value\&. For example,
\fB\-\-format=g\fR
specifies the grid format\&. An error occurs if a prefix matches more than one valid value\&.
.PP
The path to the MySQL client tools should be included in the
PATH
environment variable in order to use the authentication mechanism with login\-paths\&. This will allow the utility to use the
\fBmy_print_defaults\fR
tools which is required to read the login\-path values from the login configuration file (\&.mylogin\&.cnf)\&.
EXAMPLES.PP
For each example, assume that the
root
user on
localhost
has sufficient privileges to kill queries and connections\&.
.PP
Kill all connections created by user
john:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlprocgrep \-\-server=root@localhost \e\fR
          \fB\-\-match\-user=john \-\-kill\-connection \-\-format=CSV\fR
# The following KILL commands were executed:
Id,User,Host,db,Command,Time,State,Info
4,john,localhost:50706,mysql,Sleep,5,,
.fi
.if n \{\
.RE
.\}
.PP
Kill all connections that have been idle for more than 1 hour:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqlprocgrep \-\-server=root@localhost \e\fR
          \fB\-\-match\-command=sleep \-\-age=1h \-\-kill\-connection\fR
.fi
.if n \{\
.RE
.\}
.sp
PERMISSIONS REQUIRED.PP
The user must have the SELECT privilege on the mysql database\&.
.SH "COPYRIGHT"
.br
.PP
Copyright \(co 2006, 2016, Oracle and/or its affiliates. All rights reserved.
.PP
This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.
.PP
This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
.PP
You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.
.sp
.SH "SEE ALSO"
For more information, please refer to the MySQL Utilities and Fabric
documentation, which is available online at
http://dev.mysql.com/doc/index-utils-fabric.html
.SH AUTHOR
Oracle Corporation (http://dev.mysql.com/).