Need help? Call us on 1300 789 260

How to Stop a Process within MySQL

Difficulty: Intermediate

Prerequisites

  • SSH Acccess to your server

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.
Was this article helpful? Yes No
👥

Having trouble? We’re here to help!

We’ve built our company with a serious focus on quality service. Feel free to give us a call!

1300 789 260