How to Kill Running Process, Query, or Session in MySQL

Every database developer and administrator strives to maintain optimal performance and stability for their databases. There are tasks that need to be done every single day to keep things the way they should be. Let us explore how to manage MySQL processes and queries to ensure smooth database operations.

In this article, we will discuss various aspects of MySQL process management, providing examples and visual representations in both the command-line interface (CMD) and a graphical user interface (GUI) tool such as dbForge Studio for MySQL:

The importance of active process management in MySQL
Sorting and filtering methods for process termination
Kill MySQL process by ID
Kill all processes in MySQL
Kill MySQL session

The importance of active process management in MySQL

As we mentioned at the beginning of this article, it is crucial to keep a close eye on running processes, queries, and sessions in MySQL. And just like any other rule, this one stems from the experience and trial and error of developers who faced the same issues long before us. For example, unmanaged processes can lead to increased server load, slow query execution, and even system crashes. Thus, if your business relies on online sales, such a crash can result in an unfortunate money loss and customer dissatisfaction. If you are willing to avoid this misfortune, simply keep reading. To see all the running processes, execute this query:

show full processlist;

The importance of active process management in MySQL

Sorting and filtering methods for process termination

Sorting and filtering methods for processes in MySQL makes it easier for you to differentiate between those you need to terminate and those that are to keep going. For instance, the process that takes longer to run is the perfect candidate to be removed.

To filter processes, click and select the filtering criterion.
To sort processes, click the header of the required column.

For more detailed instructions, refer to How to show running queries in MySQL Processlist.

Sorting and filtering methods for process termination

Kill MySQL process by ID

A process ID (PID) in MySQL is a unique number assigned to each running process, which is used to identify and manage individual processes within the MySQL server environment.

show full processlist;

After executing this command, you will see the IDs of all the running processes in the very first column of the result grid.

By reviewing the Time column, you can detect the process that takes the most time to run. Once detected, you can take its ID and run the following query:

KILL (ID);

This way, you can kill a MySQL query by ID or terminate a process that is slowing down the performance of your database from the command line or a GUI like dbForge Studio for MySQL.

Kill all processes in MySQL

There are scenarios where killing all running processes in MySQL becomes necessary, such as during system maintenance or when dealing with a sudden influx of queries. Even though MySQL does not have a unique command for killing all processes at once, you can terminate the ones that belong to a certain user.

To kill all processes for a specific user, use CONCAT to create a list of threads and statements. In this example, we entered jordansanders as the user. To specify another one, replace jordansanders with the desired username.

SELECT
  CONCAT('KILL ', ID, ';')
FROM performance_schema.PROCESSLIST
WHERE user = 'jordansanders';

Once you execute this command, you will be presented with a list of KILL commands for all processes that are currently running on your server. Execute those to terminate each and every process. The query above allows you to kill all queries in MySQL as well.

Kill MySQL session

In MySQL, a session refers to a connection between a client and a MySQL server. Each time you connect to the MySQL server, you initiate a session, during which you can execute queries and perform various operations on the database. Managing sessions effectively is as important as managing processes since failing to do so can lead to resource exhaustion, performance degradation, and potential system crashes. You can use the dbForge Studio's graphical interface to see the list of all the running sessions.

1. Navigate to the Database menu and select Session Manager.

2. Right-click the session you wish to terminate and select Kill Session. Alternatively, simply select the desired session and click the Kill Session button.

This way, you can also kill all user sessions, a query, or refresh the list of active sessions.

Kill MySQL session in dbForge Studio for MySQL

Conclusion

Efficient management of MySQL processes, queries, and sessions is crucial for maintaining system stability and optimizing performance. Responsible implementation of the methods we described in this article is essential to avoid unintended consequences and uphold the integrity of the database environment. For those seeking comprehensive tools to aid in MySQL management, dbForge Studio for MySQL is one of the best MySQL GUI tools on the market today. With features such as query optimization, database administration, and performance tuning, dbForge Studio empowers users to tackle any task efficiently and effectively. Whether it's required to analyze query execution plans, streamlining database administration, or implementing MySQL performance tips, dbForge Studio provides the tools needed to elevate MySQL management to the next level. It is better to try it once than to read about it many times, so we welcome you to download a FREE 30-day trial!