The MySQL KILL command as the name suggests kills queries that are running.
After identifying the Id using the SHOW PROCESSLIST command, the User of the connection/thread or a database user with SUPER privileges can execute KILL [id]; to remove the connection/thread.
However, there is an ability to kill just the query that is being executed rather the entire connection. The default when not specified is to kill the connection, however you can optional specify the CONNECTION or QUERY keywords.
For example, below is an interactive test.
Thread 1:
mysql> select sleep(10);
Thread 2:
mysql> show processlist; +----+------+-----------+------+---------+------+-----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-----------+------------------+ | 23 | root | localhost | NULL | Query | 2 | executing | select sleep(10) | | 24 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+-----------+------------------+ 2 rows in set (0.00 sec) mysql> kill query 23;
Thread 1:
Notice, the query exits, in less then 10 seconds, but the connection is still valid.
+-----------+ | sleep(10) | +-----------+ | 1 | +-----------+ 1 row in set (7.27 sec) mysql> select sleep(10);
Thread 2:
mysql> show processlist; +----+------+-----------+------+---------+------+-----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-----------+------------------+ | 23 | root | localhost | NULL | Query | 4 | executing | select sleep(10) | | 24 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+-----------+------------------+ 2 rows in set (0.00 sec) mysql> kill 23; Query OK, 0 rows affected (0.00 sec)
Thread 1:
Query is killed and connection is lost. An attempt to run the command again within the interactive client causes the system to re-get a connection.
ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> select sleep(10); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 25 Current database: *** NONE *** +-----------+ | sleep(10) | +-----------+ | 0 | +-----------+ 1 row in set (10.00 sec)
