How to Find and Terminate / Kill MySQL Process

January 23, 2020

Introduction

Unnecessary MySQL processes can cause performance issues on your system. Over time, active threads pile up and stall your server preventing users from accessing tables and executing requests.

When resource usage is extremely high, you may need to kill MySQL processes. This guide will show you how to identify MySQL processes and kill a process.

tutorial on How to kill mysql process command line

Prerequisites

  • Access to a command line/terminal window
  • MySQL or MariaDB installed
  • User with sudo or root privileges

How to Find MySQL Processes List

Before you can locate a process and kill it, you must access either a local or remote MySQL server. To log into your MySQL local account as root, open the terminal and enter:

mysql -u root -p

Type in the password when prompted. When the MySQL shell loads, the prompt displays mysql>.

To locate a process to kill or terminate, load the list with all active sessions.
In MySQL shell, use this query:

SHOW PROCESSLIST;

The output displays the following information:

displaying all mysql processes output

You may have more entries on your list. Note the Id of the process you want to kill. The Time column helps you determine the longest-running processes. Those are usually the ones you want to terminate first.

How to Kill MySQL Process

To kill a MySQL session from the list, use the KILL query followed by the thread Id you noted earlier.

KILL 14;

The shell displays the query status and the number of affected rows: “Query OK, 0 rows affected (0.06 sec).This query ends the connection to the database, including all operations associated with the connection.

Remember that a user must have proper privileges to be able to kill a process.

How to Kill All MySQL Processes for a Specific User

MySQL does not have a unique command for killing all processes.

To kill all processes for a specific user, use CONCAT to create a file with the list of threads and statements. In our case, we entered root as the user. To specify another user, replace root with the desired username.

SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/tmp/process_list.txt';

This query created a file called process_list.txt. You can edit the name to your liking. Open the file and review if those are the processes you want to kill. When you are ready, enter:

SOURCE /tmp/process_list.txt;

You can add conditions to the query to narrow down the list of processes in the output file. For example, add time > 1000 to the command to include only the processes with the time value higher than 1000.

SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='root' and time>1000 INTO OUTFILE '/tmp/process_list.txt';

Conclusion

This guide has outlined the easiest way to find and kill MySQL processes. Make sure you always double-check which processes you are terminating before running a MySQL query.

For options to further optimize a MySQL Database, read our article on MySQL Performance Tuning.

Was this article helpful?
YesNo
Goran Jevtic
Goran combines his passions for research, writing and technology as a technical writer at phoenixNAP. Working with multiple departments and on a variety of projects, he has developed extraordinary understanding of cloud and virtualization technology trends and best practices.
Next you should read
How to Check the MySQL Version In Linux
July 11, 2019

It is essential to know which version of MySQL you have installed. The version number helps to determine if...
Read more
How To Show a List of All Databases in MySQL
July 23, 2019

With Structured Query Language (SQL), you can easily access and manage content in all your databases.
Read more
How to List All Users in a MySQL Database
November 18, 2019

This simple tutorial analyses the commands used to list all user accounts in MySQL.
Read more
How To Remove or Delete a MySQL User Account
December 1, 2019

This article covers the basics of using the DROP USER statement used to delete MySQL user account.
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.