How can I stop a running MySQL query?

I connect to mysql from my Linux shell. Every now and then I run a SELECT query that is too big. It prints and prints and I already know this is not what I meant. I would like to stop the query.

Hitting Ctrl+C (a couple of times) kills mysql completely and takes me back to shell, so I have to reconnect.开发者_JAVA百科

Is it possible to stop a query without killing mysql itself?

mysql> show processlist;
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
| Id | User | Host      | db  | Command | Time | State               | Info                         | Progress |
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
| 14 | usr1 | localhost | db1 | Query   |    0 | starting            | show processlist             |    0.000 |
| 16 | usr1 | localhost | db1 | Query   |   94 | Creating sort index | SELECT  `tbl1`.* FROM `tbl1` |    0.000 |
+----+------+-----------+-----+---------+------+---------------------+------------------------------+----------+
2 rows in set (0.000 sec)

mysql> kill 16;
Query OK, 0 rows affected (0.004 sec)

mysql> show processlist;
+----+------+-----------+-----+---------+------+----------+------------------+----------+
| Id | User | Host      | db  | Command | Time | State    | Info             | Progress |
+----+------+-----------+-----+---------+------+----------+------------------+----------+
| 14 | usr1 | localhost | db1 | Query   |    0 | starting | show processlist |    0.000 |
+----+------+-----------+-----+---------+------+----------+------------------+----------+
1 row in set (0.000 sec)

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

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)

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.

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

You need to run following command to kill the process. Find out the id of the process which you wanted to kill by

> show processlist;

Take the value from id column and fire below command

kill query <processId>;

Query parameter specifies that we need to kill query command process.

The syntax for kill process as follows

KILL [CONNECTION | QUERY] processlist_id

Please refer this link for more information.

The author of this question mentions that it’s usually only after MySQL prints its output that he realises that the wrong query was executed. As noted, in this case, Ctrl-C doesn’t help. However, I’ve noticed that it will abort the current query – if you catch it before any output is printed. For example:

mysql> select * from jos_users, jos_comprofiler;

MySQL gets busy generating the Cartesian Product of the above two tables and you soon notice that MySQL hasn't printed any output to screen (the process state is Sending data) so you type Ctrl-C:

Ctrl-C -- sending "KILL QUERY 113240" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

Ctrl-C can similarly be used to stop an UPDATE query.