Kill Session and running query in MySQL database

Find the process running in MySQL

SHOW PROCESSLIST;
OR
SELECT * FROM information_schema.processlist ORDER BY id;

Example:
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+-------+---------+------+------------------------+------------------+
| Id | User            | Host            | db    | Command | Time | State                  | Info             |
+----+-----------------+-----------------+-------+---------+------+------------------------+------------------+
| 19 | root            | localhost:53687 | NULL  | Sleep   |  196 |                        | NULL             |
| 20 | root            | localhost:53688 | world | Sleep   |  196 |                        | NULL             |
| 21 | root            | localhost:53689 | NULL  | Sleep   | 3134 |                        | NULL             |
| 22 | root            | localhost:53690 | NULL  | Sleep   |    1 |                        | NULL             |
| 24 | root            | localhost:53749 | NULL  | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------------+-------+---------+------+------------------------+------------------+
6 rows in set (0.00 sec)

Kill the Query running on Session

KILL QUERY thread_id;
Example:
KILL QUERY 20;

Kill Connection or Session in MySQL

KILL CONNECTION thread_id;
KILL thread_id;

Example:
mysql> KILL CONNECTION 20;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User            | Host            | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 3696 | Waiting on empty queue | NULL             |
| 19 | root            | localhost:53687 | NULL | Sleep   |   28 |                        | NULL             |
| 21 | root            | localhost:53689 | NULL | Sleep   | 3513 |                        | NULL             |
| 22 | root            | localhost:53690 | NULL | Sleep   |    1 |                        | NULL             |
| 24 | root            | localhost:53749 | NULL | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.