How to Kill MySQL Process

Sometimes MySQL queries take a very long time and slow the database processing. We can find and kill these stuck processes.

MySQL Kill Process Steps

MySQL Kill Process involves running two commands.

Find the running processes using show processlist command.
Kill the process using kill ID command.

1. Find the Stuck MySQL Process

I am using root user to show processes for all the users. If you are using a database-specific user, you will get the results for that database only.

MariaDB [(none)]> show processlist;
+--------+------------+-----------+------------+---------+------+---------------------------------+-----------------------------+----------+
| Id     | User       | Host      | db         | Command | Time | State                           | Info                        | Progress |
+--------+------------+-----------+------------+---------+------+---------------------------------+-----------------------------+----------+
| 566697 | jour_wp    | localhost | jour_wp    | Query   | 1130 | Waiting for table metadata lock | OPTIMIZE TABLE wp_comments  |    0.000 |
| 566698 | jour_wp    | localhost | jour_wp    | Query   | 1130 | Waiting for table level lock    | OPTIMIZE TABLE wp_comments  |    0.000 |

It’s clear from the output that these processes are stuck for more than 1000 seconds.

The Query shows only 100 characters of the Query. If that’s not sufficient then use the SHOW FULL PROCESSLIST command to get the complete query information.

2. Kill MySQL Process by ID

The show processlist command shows the MySQL process IDs. We can run the kill ID command to terminate them.

MariaDB [(none)]> kill 566697;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> kill 566698;
Query OK, 0 rows affected (0.00 sec)

We can check the process list again to make sure all the stuck processes are cleared.