killall
NAME
killall(): Kill connections with by matching GRANTEE, user or hostTYPE
ProcedureDESCRIPTION
Kill connections by matching connection GRANTEE, user or host with given input. This routine provides with a quick "kill those connections right now" solution, which is otherwise achieved by looking up connection IDs from SHOW PROCESSLIST, or by evaluating the processlist_grantees view.
killall(), similar to unix' killall command, kills by name rather than by ID. killall() accepts a grantee_term input, which may be either a fully qualified GRANTEE (e.g. 'gromit'@'webhost.local'), a relaxed GRANTEE (e.g. gromit@localhost), a username only (e.g. 'gromit') or a hostname only (e.g. 'analytics_server.localdomain').
Thus, it makes it easy to quickly kill, for example, all connections by a given user, wherever that user may connect from.
killall() allows killing of all connections, including those of users with the SUPER privilege, as well as replication. However, killall() is guaranteed never to kill the current connection - the very one invoking the routine.
Whatever the grantee_term is, it is compared against GRANTEE accounts, and NOT against particular connections. Thus, the following:
call killall('192.168.0.%');
killall() does not provide the mechanism to kill queries which are slow, or include a given text. Use eval() and processlist_grantees for that.
SYNOPSIS
killall(IN grantee_term TINYTEXT CHARSET utf8) READS SQL DATA
Input:
- grantee_term: a GRANTEE, qualified or unqualified, or the user or host parts of a GRANTEE.
EXAMPLES
Kill all connections by user 'apps':
mysql> SHOW PROCESSLIST;
+----+------+-----------+---------------+---------+------+------------+---------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------------+---------+------+------------+---------------------+
| 7 | root | localhost | common_schema | Query | 0 | NULL | SHOW PROCESSLIST |
| 78 | apps | localhost | NULL | Query | 31 | User sleep | select sleep(10000) |
+----+------+-----------+---------------+---------+------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> CALL killall('apps');
mysql> SHOW PROCESSLIST;
+----+------+-----------+---------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------------+---------+------+-------+------------------+
| 7 | root | localhost | common_schema | Query | 0 | NULL | SHOW PROCESSLIST |
+----+------+-----------+---------------+---------+------+-------+------------------+
1 row in set (0.00 sec)
Kill all 'localhost' connections:
mysql> SHOW PROCESSLIST;
+----+------+-----------+---------------+---------+------+------------+---------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------------+---------+------+------------+---------------------+
| 7 | root | localhost | common_schema | Query | 0 | NULL | SHOW PROCESSLIST |
| 81 | apps | localhost | NULL | Query | 18 | User sleep | select sleep(10000) |
+----+------+-----------+---------------+---------+------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> CALL killall('localhost');
mysql> SHOW PROCESSLIST;
+----+------+-----------+---------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------------+---------+------+-------+------------------+
| 7 | root | localhost | common_schema | Query | 0 | NULL | SHOW PROCESSLIST |
+----+------+-----------+---------------+---------+------+-------+------------------+
1 row in set (0.00 sec)