Unnecessary MySQL processes can cause 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 for maintaining the MySQL server's optimal state and responsiveness.
This guide will show you how to find and kill a MySQL process.
Prerequisites
- MySQL or MariaDB installed.
- Access to the terminal.
- User with sudo or root privileges.
How to Find MySQL Process
Before killing a process, you must locate it. To do that, take the following steps:
1. Access the 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 issues, and resolving 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 database connection, including all associated operations. To confirm the termination, run:
SHOW PROCESSLIST;
The output shows only two running processes (process IDs change over time), confirming that one process was successfully ended.
Via CONCAT
The CONCAT function in MySQL is used to combine strings. While it doesn't kill processes directly, it is used in a query to generate KILL commands to end 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 bottom section of the window shows one fewer process, confirming successful termination.
Conclusion
This guide outlines four different ways to find and kill MySQL processes. Use the method you find easiest.
Next, learn how to start, stop, and restart the MySQL server.
