How to Stop a Process within MySQL

Overview

If you have a long running query which is incorrect or taking too long to complete, you can stop the individual query without affecting other databases. 

Don’t forcibly kill MySQL the MySQL process directly. Doing so will most likely lead to data corruption.

Instructions

1. Login to your MySQL shell as the root user:

   mysql -uroot -p<MYSQLPASSWORD>

   For Plesk users, use the following:

   mysql -u admin -p`cat /etc/psa/.psa.shadow`
   

2. Bring up a list of processes with the “SHOW PROCESSLIST;” command. You should see something like this:

   mysql> show processlist;
   +----+-------+-----------+------+---------+------+----------+--------------------+
   | Id | User  | Host      | db   | Command | Time | State     | Info              |
   +----+-------+-----------+------+---------+------+----------+--------------------+
   | 11 | web01 | localhost | NULL | Query   | 1231 | updating | DELETE FROM logs.. |
   | 15 | web02 | localhost | NULL | Sleep   |   10 | NULL     | NULL               |
   | 41 | root  | localhost | NULL | Query   |    0 | NULL     | show processlist   |
   +----+-------+-----------+------+---------+------+----------+--------------------+
   3 rows in set (0.00 sec)

    

3. Locate the process you wish to kill, in this instance we’re going to kill the DELETE query with the ID of 11 with the KILL command:

   KILL 11;
   Query OK, 0 rows affected (0.00 sec)
   

4. You can run “SHOW PROCESSLIST;” again to confirm the process has been stopped.