How to Kill a Process in MySQL

Summary: In this tutorial, you will learn how to kill a process in MySQL using the KILL statement via the mysql client tool and mysqladmin.

MySQL creates a thread for each connection to the mysqld server. You can kill a thread (or process) with the KILL statement.

Notice that we’ll use the terms process and thread interchangeably but they are different from the technical point of view

First, find the thread ID using the SHOW PROCESSLIST statement:

SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)

This statement returns a list of running processes, each is assigned an id.

Second, kill a thread by its ID using the KILL statement:

KILL id;Code language: SQL (Structured Query Language) (sql)

To run the KILL command, you need to have the CONNECTION_ADMIN privilege.

Kill a process using mysql client

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, connect to the MySQL server using the mysql client tool:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Third, display all processes in the server using the SHOW PROCESSLIST statement:

SHOW PROCESSLIST;Code language: SQL (Structured Query Language) (sql)

It’ll display the currently running threads:

+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
| Id | User            | Host            | db   | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 134298 | Waiting on empty queue | NULL             |
| 50 | root            | localhost:63998 | NULL | Query   |      0 | init                   | show processlist |
| 52 | bob             | localhost:64014 | NULL | Sleep   |      6 |                        | NULL             |
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the output shows that the local MySQL server has three threads. To kill a thread, you need to find the ID of the process and execute the KILL statement:

KILL id;Code language: SQL (Structured Query Language) (sql)

For example, you can kill the process with ID 52 as follows:

KILL 52;Code language: SQL (Structured Query Language) (sql)

If you use the bob user account to make a query, you’ll see the following message:

ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    54
Current database: *** NONE ***Code language: SQL (Structured Query Language) (sql)

The message says that the session of bob was lost and reconnected immediately.

Kill a process using the mysqladmin tool

mysqladmin is a client tool that allows you to perform database administrative tasks. To kill a process using mysqladmin, you follow these steps:

First, show the processes using the mysqladmin processlist command:

mysqladmin -u root -p processlist;

It’ll display a list of processes:

+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+
| Id | User            | Host            | db | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+
| 5  | event_scheduler | localhost       |    | Daemon  | 140572 | Waiting on empty queue |                  |
| 53 | root            | localhost:64026 |    | Sleep   | 5589   |                        |                  |
| 54 | bob             | localhost:64051 |    | Sleep   | 6065   |                        |                  |
| 56 | root            | localhost:50039 |    | Query   | 0      | init                   | show processlist |
+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+Code language: PHP (php)

Second, kill a process by id using the kill command. For example:

mysqladmin -u root -p kill 54

Behind the scenes, the mysqladmin kill command executes the KILL statement to kill a process.

Kill all processes in MySQL

MySQL does not have a statement that kills all processes or processes that belong to a user once. To do that you need to do a little bit more work.

MySQL stores the process list in the information_schema.processlist table. Therefore, you can retrieve the current process list with id from the information_schema.processlist table.

Here is the processlist table:

DESC information_schema.processlist;Code language: CSS (css)

Output:

+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| ID      | bigint unsigned | NO   |     |         |       |
| USER    | varchar(32)     | NO   |     |         |       |
| HOST    | varchar(261)    | NO   |     |         |       |
| DB      | varchar(64)     | YES  |     |         |       |
| COMMAND | varchar(16)     | NO   |     |         |       |
| TIME    | int             | NO   |     |         |       |
| STATE   | varchar(64)     | YES  |     |         |       |
| INFO    | varchar(65535)  | YES  |     |         |       |
+---------+-----------------+------+-----+---------+-------+
8 rows in set (0.04 sec)Code language: PHP (php)

First, construct a list of KILL statements that kill the processes belonging to user account bob:

SELECT CONCAT('KILL ',id, ';') 
FROM information_schema.processlist 
WHERE user='bob';Code language: JavaScript (javascript)

Output:

+-------------------------+
| CONCAT('KILL ',id, ';') |
+-------------------------+
| KILL 59;                |
| KILL 61;                |
+-------------------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Second, find the location on the MySQL server where you can store a file securely:

SELECT @@secure_file_priv;Code language: CSS (css)

It’ll show something like the following on Windows:

+------------------------------------------------+
| @@secure_file_priv                             |
+------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

You need to construct the file name in the secure file directory such as:

C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/kill.txtCode language: JavaScript (javascript)

Note that on Windows, you need to replace the backslash (\) with the forward (/) in the file path.

Third, save the KILL statements into a file on the MySQL:

SELECT CONCAT('KILL ',id, ';') 
FROM information_schema.processlist 
WHERE user='bob'
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/kill.txt';Code language: JavaScript (javascript)

Output:

Query OK, 2 rows affected (0.01 sec)Code language: CSS (css)

The kill.txt file will contain the following lines:

KILL 59;
KILL 61;

Finally, execute the KILL statements in the kill.txt file to kill the processes that belong to the user account bob:

SOURCE C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/kill.txt;Code language: JavaScript (javascript)

The SOURCE statement executes all SQL statements in the kill.txt file.

Summary

Use the SHOW PROCESSLIST statement to display a list of threads and the KILL statement to kill a thread by an id.

Was this tutorial helpful?