MKILL(1) User Contributed Perl Documentation MKILL(1)NAMEmkill - Kills slow queries
SYNOPSISmkill [--host={mysql_host}] [--dbuser={mysql_user}]
[--password={mysqluser_pw}]
[--filter-user={regex}] [--filter-host={regex}] [--filter-db={regex}]
[--filter-command={regex}] [--filter-state={regex}] [--filter-info={{regex}}]
[--user={user}] [--slow={seconds}]
[--daemonize] [--test]
mkill--help
mkill--version
DESCRIPTION
This is an alpha version of this program. Please let me know what you
think and what additional features would be nice. Future version will
most likely perform the same but have different output. One idea i've
been thinking about is letting you specify filters which short perl
snippets instead of just regexes.
This program kills long running queries based on several criteria
including query time, host, user, database, state, and query content.
The following keys are active while mkill is running:
q - quit
A log of killed queries is sent to STDERR, watched queries are sent to
STDOUT. A typical command line would be:
mkill-sl 180 -fi 'select.*from bad_table' > /var/log/mkill.out 2> /var/log/mkill.kill
OPTIONS
All options can be abbreviated by their shortest unique abbreviation.
-?, --help
Show the help screen and exit.
-t, --test
Show what would be done but don't actually kill anything. Good for
testing your filter expressions.
-v, --version
Show the version number and exit.
-h {mysql_host}, --host={mysql_host}
By default, the mysqld on localhost is monitored. Specify an
alternate host with this option.
-dbu {mysql_user}, --dbuser={mysql_user}
By default, the user 'mysqltop' is used to connect to the database.
Specify an alternate user with this option.
-p {mysqluser_pw}, --password={mysqluser_pw}
By default, there is no password associated with the mysqltop user,
specify a password with this option.
-sl {seconds}, --slow={seconds}
The number of seconds before a slow query is killed. The default
is 180 seconds.
FILTER OPTIONS-u {user}, --user={user}
Kill only threads owned by this user.
-fu {regex_pattern}, --filter-user={regex_pattern}
-fh {regex_pattern}, --filter-host={regex_pattern}
-fd {regex_pattern}, --filter-db={regex_pattern}
-fs {regex_pattern}, --filter-state={regex_pattern}
-fc {regex_pattern}, --filter-command={regex_pattern}
-fi {regex_pattern}, --filter-info={regex_pattern}
Filter the queries based on the regex_pattern provided. The
regex_pattern is a perl regular expression. The regular expression
match is done with case insensitivity.
For example, to only kill select statements on the user table, use
the following:
--filter-info='select from user'
or, to be more forgiving for mutil-table joins and extra spaces,
use:
--filter-info='select\s+from\s+.*\buser\b.*where'
Be careful to escape any special shell characters in the regex.
All options can be stored in initialization files. Command line
options override options stored in the initialization file(s). The
following files are checked for arguments: current direcotry .mkillc,
home directory .mkillc, /usr/local/etc/mkillc, /etc/mkillc. Options in
the former files override options in the later files.
The format of the initialization file is one option per line. Options
are specified just as they would be on the command line. They can be
abbreviated and use the one or two hyphen syntax. Comments and blank
lines are ignored. The following is an exmple .mkillc file which kills
any 'select' statements from 'user1' which last more than 120 seconds.
# Only kill 'user1' 'select' queries > 120 seconds
-fu user1
-filter-info='select'
--slow=1 # refresh every one seconds
SETUP
The most convenient way to setup your system to use mkill is to create
a database user called mysqlkill which has no password. For security
purposes, this user should have all privileges set to N except
Process_priv which must be set to Y.
To grant these privileges, execute the following from the MySQL command
prompt
For mysql 4.0.2 and greater:
mysql> grant super,process on *.* to mysqlkill;
mysql> grant super,process on *.* to mysqlkill@localhost;
mysql> flush privileges;
For mysql 3.x and 4.0.1:
mysql> grant process on *.* to mysqlkill;
mysql> grant process on *.* to mysqlkill@localhost;
mysql> flush privileges;
Notes:
· GRANT only works in MySQL 3.22.11 or later, for earlier versions
add the user manually and fix the permissions as noted above.
· The GRANT to mysqltop and mysqltop@localhost may be modified
depending upon which hosts you want to grant access from. In
general, you probably want to limit it to the hosts in your domain.
Initially, mkill does not connect to a specific database. Most
commands this program issues are non-database specific (SHOW FULL
PROCESSLIST, SHOW VARIABLES, KILL id). However, when database-specific
commands are needed, mkill will try to connect to the the required
database and prompt for a username/password if the default one fails.
To install mkill, run the following shell commands:
perl Makefile.PL
make
make install
The default {install_prefix} is /usr/local which means that mkill is
installed in /usr/local/bin/. To change this, run:
perl Makefile.PL --prefix={install_prefix}
or modify the PREFIX line in Makefile.PL.
Requires the following perl modules:
Module Available At
--------------------------------------------------------------------
DBI Distributed as Bundle::DBI: http://www.cpan.org/authors/id/TIMB
DBD::mysql http://www.cpan.org/authors/id/JWIED
Getopt::Long (Distributed with Perl 5)
Net::Domain Part of libnet: http://www.cpan.org/authors/id/GBARR/
AUTHOR
Marc Prewitt, Chelsea Networks <mprewitt@chelsea.net>
Copyright (C) 2003 Marc Prewitt/Chelsea Networks, under the GNU GPL.
mkill comes with ABSOLUTELY NO WARRANTY. This is free software, and you
are welcome to redistribute it under certain conditions; see the
COPYING file for details.
KNOWN BUGS
$Id: mkill.PL,v 1.6 2004/09/12 21:19:12 mdprewitt Exp $
The get char routines used to quit the program currently require the
user to hit the RETURN key on the keyboard after entry. This will soon
be fixed.
POD ERRORS
Hey! The above document had some coding errors, which are explained
below:
Around line 789:
'=end' without a target? (Should be "=end showsatus")
perl v5.20.2 2015-09-15 MKILL(1)
