sql

How to Kill MySQL Process

This article is to describe how to kill a MySQL process and how to use long-running queries using a few commands on the terminal.

1. Introduction

MySQL has tools to help us to manage and enhance our database performance. As an example, when a heavy query is executed on the database, it does a lot of issues like delay in data reading and further unavailability. In this article, we’ll see how to deal with these types of situations.

2. Pre-requisites

For this article, you’ll need MySQL Community Server installed. I’m using the version 8.0.21 on this example. Moreover, it is required a user with MySQL root privileges.

3. Identifying Processes

We need to identify what process are running on MySQL. Log in to MySQL with the following command:

$ mysql -uroot -p

Then, run this command that will show all process that are running:

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 3435 | Waiting on empty queue | NULL             |
| 11 | root            | localhost | NULL | Query   |    0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

Here we can see a lot of information about the processes. Let see them in depth below:

  • Id: the identity of the process.
  • User: the user executing that process.
  • Host: the MySQL host where the instance is running.
  • DB: the database where the process is been executed.
  • Command: the type of command that is running (query, insert, update, and so on…).
  • Time: how much time (in seconds) is the process running.
  • State: the current state of the process.
  • Info: the statement the thread is executing. Generally, it shows the script that is been executed by the processes.

More details about these informations see here.

4. Killing the Process

Now that we’ve seen how to identify the processes, we can choose what process we need to kill to “free” our database. How to do that? Firstly, pay attention to the columns Id, time, and state.

To know if that process is on a stuck, the TIME column is a great clue for that. Long-running queries can lock tables or delay the database. Furthermore, the STATE is also a good point to see with the process is hold.

The Id column will be used to kill the process using the command kill in MySQL like that:

mysql> KILL 11;

Note: Pay attention to the columns Id and User. So, be sure that is the right process that you want to kill, double-check the TIME and STATE columns to ensure your decision before running the kill command on MySQL.

5. Summary

In this article, we learn how to identify processes in MySQL. In addition, we saw how to kill a stuck process using a MySQL command, which is the easiest way to do it.

You can find more articles about MySQL here.

Sergio Lauriano Junior

Sergio is graduated in Software Development in the University City of São Paulo (UNICID). During his career, he get involved in a large number of projects such as telecommunications, billing, data processing, health and financial services. Currently, he works in financial area using mainly Java and IBM technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button