Introduction
Unnecessary MySQL processes sometimes lead to system performance issues. When active threads accumulate over time, they consume system resources and cause the server to become unresponsive. This prevents users from accessing tables or executing requests, impacting overall system performance.
Learning to terminate unnecessary processes is essential to maintain MySQL server's optimal state and responsiveness.
This guide will show you how to find and kill a MySQL process.
Prerequisites
How to Find MySQL Process
Before killing a process, you must locate it. To do that, take the following steps:
1. Access MySQL server with:
mysql -u root -p
Type in the password when prompted. When the MySQL shell loads, the prompt displays mysql>.
2. Load the list with all active sessions:
SHOW PROCESSLIST;The output displays the following information:
3. Note the Id of the process you want to kill, as it will be needed later. In this example, it's the process with Id 10.
How to Kill MySQL Process
Understanding how to terminate MySQL processes is crucial for managing server resources, addressing performance concerns, and dealing with problematic queries or connections. The following text presents several ways to terminate a MySQL process.
Via the KILL Command
To kill a MySQL session, use the KILL command followed by the Id noted earlier. For instance, to kill the process with Id 10, run:
KILL 10;
The shell displays the query status and the number of affected rows: "Query OK, 0 rows affected (0.00 sec)." This query ends the connection to the database, including all operations associated with the connection. To confirm the termination, run:
SHOW PROCESSLIST;
The output only shows two running processes (process Ids change over time), confirming one process is successfully ended.
Via CONCAT
The CONCAT function in MySQL is used to combine strings. While it doesn't directly kill processes, it is used in a query to create KILL commands for ending processes linked to a specific user. This involves fetching process Ids from the information_schema.processlist table and merging them with KILL and ; to form the KILL commands. This method streamlines the termination of MySQL processes associated with a particular user.
Take these steps to kill a MySQL process with the CONCAT command:
1. Create a list of <strong>KILL</strong> commands for processes associated with the specific user (in this case root):
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/var/lib/mysql-files/process_list.txt';
This command generates a KILL command list for processes associated with the root user in the information_schema.processlist table. It then saves these commands to a file named process_list.txt in the /var/lib/mysql-files/ directory.
2. Execute SQL statements from the file process_list.txt.
SOURCE /var/lib/mysql-files/process_list.txt';
Via MySQL Command-Line Client
The mysqladmin utility is a command-line tool for MySQL server administration, providing various functionalities for managing databases, users, and server configurations. Many mysqladmin commands work without logging in to the MySQL server.
To kill a process using this command line client, follow these steps:
1. List MySQL processes with:
sudo mysqladmin processlist
2. Locate the process you want to terminate. In this example, it's the process with the Id 13.
3. Terminate the process with:
sudo mysqladmin kill 13The command has no output.
4. Verify the process is terminated by listing all processes again:
sudo mysqladmin processlist
The output shows the process is terminated.
Via MySQL Workbench Tool
Another way to terminate a MySQL process is with the MySQL Workbench. Take the following steps to end a process this way:
1. Launch MySQL Workbench and establish a connection to your MySQL server.
Note: MySQL server connection attempts sometimes result in an error. Learn how to fix one of the more common MySQL errors: Access denied for user root@localhost.
2. Open a new query.
3. Write the query to list MySQL processes with:
SHOW PROCESSLIST;
4. Execute the query by clicking the lightning bolt icon or pressing Ctrl+Enter.
5. Choose a process to terminate. For instance, process with an Id 17.
6. Write the query to end the process:
KILL 17;
7. Execute the query.
The window bottom section shows one less process confirming successful termination.
Conclusion
This guide outlines four different ways to find and kill MySQL processes. Use the method that you find easiest to employ.
Next, learn how to start, stop, and restart the MySQL server.
