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.
Instructions
- 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`
- 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)
- 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)
- You can run “SHOW PROCESSLIST;” again to confirm the process has been stopped.