How can I stop a running MySQL query?

Solution 1:

mysql>show processlist;

mysql> kill "number from first col";

Solution 2:

Just to add

KILL QUERY **Id** where Id is connection id from show processlist

is more preferable if you are do not want to kill the connection usually when running from some application.

For more details you can read mysql doc here

Solution 3:

Connect to mysql

mysql -uusername -p  -hhostname

show full processlist:

mysql> show full processlist;
+---------+--------+-------------------+---------+---------+------+-------+------------------+
| Id      | User   | Host              | db      | Command | Time | State | Info             |
+---------+--------+-------------------+---------+---------+------+-------+------------------+
| 9255451 | logreg | dmin001.ops:37651 | logdata | Query   |    0 | NULL  | show processlist |
+---------+--------+-------------------+---------+---------+------+-------+------------------+

Kill the specific query. Here id=9255451

mysql> kill 9255451;

If you get permission denied, try this SQL:

CALL mysql.rds_kill(9255451)

Solution 4:

Use mysqladmin to kill the runaway query:

Run the following commands:

mysqladmin -uusername -ppassword pr

Then note down the process id.

mysqladmin -uusername -ppassword kill pid

The runaway query should no longer be consuming resources.

Solution 5:

If you have mysqladmin available, you may get the list of queries with:

> mysqladmin -uUSERNAME -pPASSWORD pr

+-----+------+-----------------+--------+---------+------+--------------+------------------+
| Id  | User | Host            | db     | Command | Time | State        | Info             |
+-----+------+-----------------+--------+---------+------+--------------+------------------+
| 137 | beet | localhost:53535 | people | Query   | 292  | Sending data | DELETE FROM      |
| 145 | root | localhost:55745 |        | Query   | 0    |              | show processlist |
+-----+------+-----------------+--------+---------+------+--------------+------------------+

Then you may stop the mysql process that is hosting the long running query:

> mysqladmin -uUSERNAME -pPASSWORD kill 137