{"id":14343,"date":"2024-01-06T19:18:09","date_gmt":"2024-01-07T02:18:09","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=14343"},"modified":"2024-01-06T20:05:48","modified_gmt":"2024-01-07T03:05:48","slug":"kill-process-in-mysql","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/kill-process-in-mysql\/","title":{"rendered":"How to Kill a Process in MySQL"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to kill a process in MySQL using the KILL statement via the mysql client tool and mysqladmin<\/a>.<\/p>\n\n\n\n

MySQL creates a thread for each connection to the mysqld server. You can kill a thread (or process) with the KILL<\/code> statement.<\/p>\n\n\n\n

Notice that we’ll use the terms process and thread interchangeably but they are different from the technical point of view<\/p>\n\n\n\n

First, find the thread ID<\/code> using the SHOW PROCESSLIST<\/a> statement:<\/p>\n\n\n

SHOW<\/span> PROCESSLIST<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement returns a list of running processes, each is assigned an id.<\/p>\n\n\n\n
Second, kill a thread by its ID<\/code> using the KILL<\/code> statement:<\/p>\n\n\n
KILL<\/span> id<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To run the KILL<\/code> command, you need to have the CONNECTION_ADMIN<\/code> privilege.<\/p>\n\n\n\n
Kill a process using mysql client<\/h2>\n\n\n\n
First, open the Command Prompt on Windows or Terminal on Unix-like systems.<\/p>\n\n\n\n
Second, connect to the MySQL server<\/a> using the mysql client tool:<\/p>\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, display all processes in the server using the SHOW<\/code> PROCESSLIST<\/code> statement:<\/p>\n\n\n
SHOW<\/span> PROCESSLIST<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll display the currently running threads:<\/p>\n\n\n
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+<\/span>\n| Id | User            | Host            | db   | Command | Time   | State                  | Info             |\n+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+<\/span>\n|  5 | event_scheduler | localhost       | NULL | Daemon  | 134298 | Waiting on empty queue | NULL             |\n| 50 | root            | localhost:63998 | NULL | Query   |      0 | init                   | show<\/span> processlist<\/span> |\n| 52<\/span> | bob             | localhost:64014<\/span> | NULL<\/span> | Sleep<\/span>   |      6<\/span> |                        | NULL<\/span>             |\n+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+<\/span>\n3<\/span> rows<\/span> in<\/span> set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the output shows that the local MySQL server has three threads. To kill a thread, you need to find the ID<\/code> of the process and execute the KILL<\/code> statement:<\/p>\n\n\n
KILL<\/span> id<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, you can kill the process with ID<\/code> 52 as follows:<\/p>\n\n\n
KILL<\/span> 52<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you use the bob<\/code> user account to make a query, you’ll see the following message:<\/p>\n\n\n
ERROR 2013 (HY000): Lost connection to MySQL server during query\nNo connection. Trying to reconnect...\nConnection id:    54\nCurrent database: *** NONE ***<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The message says that the session of bob<\/code> was lost and reconnected immediately.<\/p>\n\n\n\n
Kill a process using the mysqladmin tool<\/h2>\n\n\n\n
mysqladmin<\/a> is a client tool that allows you to perform database administrative tasks. To kill a process using mysqladmin, you follow these steps:<\/p>\n\n\n\n
First, show the processes using the mysqladmin processlist<\/code> command:<\/p>\n\n\n
mysqladmin -u root -p processlist;<\/code><\/span><\/pre>\n\n\n
It’ll display a list of processes:<\/p>\n\n\n
+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+\r\n| Id | User            | Host            | db | Command | Time   | State                  | Info             |\r\n+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+\r\n| 5<\/span>  | event_scheduler | localhost       |    | Daemon  | 140572<\/span> | Waiting on empty<\/span> queue |                  |\r\n| 53<\/span> | root            | localhost:64026<\/span> |    | Sleep   | 5589<\/span>   |                        |                  |\r\n| 54<\/span> | bob             | localhost:64051<\/span> |    | Sleep   | 6065<\/span>   |                        |                  |\r\n| 56<\/span> | root            | localhost:50039<\/span> |    | Query   | 0<\/span>      | init                   | show processlist |\r\n+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, kill a process by id using the kill command. For example:<\/p>\n\n\n
mysqladmin -u root -p kill 54<\/code><\/span><\/pre>\n\n\n
Behind the scenes, the mysqladmin kill command executes the KILL<\/code> statement to kill a process.<\/p>\n\n\n\n
Kill all processes in MySQL<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
MySQL stores the process list in the information_schema.processlist<\/code> table. Therefore, you can retrieve the current process list with id from the information_schema.processlist table.<\/p>\n\n\n\n
Here is the processlist<\/code> table:<\/p>\n\n\n
DESC<\/span> information_schema<\/span>.processlist<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------+-----------------+------+-----+---------+-------+\r\n| Field   | Type            | Null<\/span> | Key | Default<\/span> | Extra |\r\n+---------+-----------------+------+-----+---------+-------+\r\n| ID      | bigint unsigned | NO   |     |         |       |\r\n| USER    | varchar(32<\/span>)     | NO   |     |         |       |\r\n| HOST    | varchar(261<\/span>)    | NO   |     |         |       |\r\n| DB      | varchar(64<\/span>)     | YES  |     |         |       |\r\n| COMMAND | varchar(16<\/span>)     | NO   |     |         |       |\r\n| TIME    | int             | NO   |     |         |       |\r\n| STATE   | varchar(64<\/span>)     | YES  |     |         |       |\r\n| INFO    | varchar(65535<\/span>)  | YES  |     |         |       |\r\n+---------+-----------------+------+-----+---------+-------+\r\n8<\/span> rows in set (0.04<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
First, construct a list of KILL<\/code> statements that kill the processes belonging to user account bob<\/code>:<\/p>\n\n\n
SELECT CONCAT('KILL '<\/span>,id, ';'<\/span>) \nFROM information_schema.processlist \nWHERE user='bob'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-------------------------+\r\n| CONCAT('KILL '<\/span>,id, ';'<\/span>) |\r\n+-------------------------+\r\n| KILL 59<\/span>;                |\r\n| KILL 61<\/span>;                |\r\n+-------------------------+\r\n2<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, find the location on the MySQL server where you can store a file securely:<\/p>\n\n\n
SELECT<\/span> @@secure_file_priv<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll show something like the following on Windows:<\/p>\n\n\n
+------------------------------------------------+\r\n| @@secure_file_priv                             |\r\n+------------------------------------------------+\r\n| C:\\ProgramData\\MySQL\\MySQL Server 8.0<\/span>\\Uploads\\ |\r\n+------------------------------------------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
You need to construct the file name in the secure file directory such as:<\/p>\n\n\n
C:\/ProgramData\/<\/span>MySQL\/MySQL Server 8.0<\/span>\/Uploads\/kill.txt<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that on Windows, you need to replace the backslash (\\) with the forward (\/) in the file path.<\/p>\n\n\n\n
Third, save the KILL<\/code> statements into a file on the MySQL:<\/p>\n\n\n
SELECT CONCAT('KILL '<\/span>,id, ';'<\/span>) \nFROM information_schema.processlist \nWHERE user='bob'<\/span>\nINTO OUTFILE 'C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads\/kill.txt'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Query<\/span> OK<\/span>, 2 rows<\/span> affected<\/span> (0.01<\/span> sec<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
The kill.txt<\/code> file will contain the following lines:<\/p>\n\n\n
KILL 59;\nKILL 61;<\/code><\/span><\/pre>\n\n\n
Finally, execute the KILL<\/code> statements in the kill.txt<\/code> file to kill the processes that belong to the user account bob:<\/p>\n\n\n
SOURCE C:\/ProgramData\/<\/span>MySQL\/MySQL Server 8.0<\/span>\/Uploads\/kill.txt;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The SOURCE<\/a> statement executes all SQL statements in the kill.txt file.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the SHOW<\/code> PROCESSLIST<\/code> statement to display a list of threads and  the KILL<\/code> statement to kill a thread by an id.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t