Kill running MySQL queries

A slow or blocked query can hold up other requests from the application. Before interrupting it, identify its user, database, state and content: having run for a long time does not on its own mean it should be closed.

Keep the diagnosis without publishing personal data that appears in the SQL. If it is a planned import, copy or schema change, first check who started it.

Over SSH or the terminal built into cPanel

First you need to connect to the account over SSH. If you have experience, connecting through the terminal installed on your computer is the ideal solution; otherwise, the terminal built into cPanel is a much simpler option.

Using SQL queries from the MySQL server

Once in the terminal, connect to MySQL with the command:

mysql -u usuario_mysql -p

Where usuario_mysql is a MySQL user with permissions on the database. The client asks for the password without leaving it in the history or visible in the process list.

Then run the query that shows the open processes.

SHOW FULL PROCESSLIST;

This gives you a table with the running queries and how long each has been in its current state. A Sleep connection is idle, but it may be holding a transaction open; do not close it just because of its duration.

Take the ID value of the one you want to close and run:

KILL QUERY IDENTIFICADOR;

KILL QUERY stops the current query without closing the whole connection. After running it, check the list and the application's status again. It does not necessarily release all the locks of a transaction that remains open, and the interruption may take a while to complete. Do not repeat commands against an old list: check the identifier again.

If you need to prepare several commands, the following query generates one statement per process of the given user. It does not run them: review each identifier, leave out your own session and any legitimate production query, and then run only the statements you need.

SELECT CONCAT('KILL QUERY ', ID, ';') AS sentencia_a_revisar
FROM information_schema.PROCESSLIST
WHERE USER = 'USUARIO_MYSQL'
  AND COMMAND <> 'Sleep'
  AND ID <> CONNECTION_ID();

On managed hosting you can only see and stop the processes your user's privileges allow. Do not remove the filter to try to close everything visible: you could also interrupt active requests from your own website.

Using the mysqladmin tool

From the terminal you also have mysqladmin to list processes. Its kill command is not the same as KILL QUERY: it ends the whole connection and may interrupt a transaction or an application session.

mysqladmin -u USUARIO_MYSQL -p processlist

Use the identifier with mysqladmin kill only if you have checked that you can close that whole connection:

mysqladmin -u USUARIO_MYSQL -p kill ID

You can give several identifiers separated by commas, but each one will close its whole connection:

mysqladmin -u USUARIO_MYSQL -p kill 12,34,56

To stop only the active statement, use KILL QUERY from MySQL. Do not chain processlist with awk, grep and xargs to close processes automatically: the output contains headers and borders, and an imprecise match can interrupt legitimate queries.

From phpMyAdmin

The interface lets you view processes without opening a terminal. The action to kill a process may close its whole connection; to stop only the statement, use the SQL tab with KILL QUERY if your user has permission.

Open phpMyAdmin, go to Status and then Processes. That view shows the processes allowed for your user and, when you have permission, the action to kill each one. Check the identifier, the time and the query before using it.

If the problem keeps happening

Review the query, its indexes, open transactions and the task that launches it. Closing processes relieves some locks, but it does not fix their cause. If you need support, give the time, database, state and identifier, and share the SQL through a private channel when necessary.

See the scope of KILL and KILL QUERY in MySQL.