如何停止运行的MySQL查询?

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.

我从我的Linux shell中连接到mysql。现在，我运行一个太大的SELECT查询。它打印和打印，我已经知道这不是我的意思。我想停止查询。

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

按下Ctrl+C(几次)会完全杀死mysql，把我带回shell，所以我必须重新连接。

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

是否可以在不杀死mysql本身的情况下停止查询?

6 个解决方案

#1

328

mysql>show processlist;

mysql> kill "number from first col";

#2

Just to add

只是为了增加

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

KILL查询**Id**，其中Id为show processlist的连接Id。

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

想了解更多细节，请阅读mysql文档。

#3

Connect to mysql

连接到mysql

mysql -uusername -p  -hhostname

show full processlist:

显示全部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

杀死特定的查询。id = 9255451

mysql> kill 9255451;

If you get permission denied, try this SQL:

如果您被拒绝，请尝试以下SQL:

CALL mysql.rds_kill(9255451)

#4

Use mysqladmin to kill the runaway query:

使用mysqladmin杀死失控查询:

Run the following commands:

运行以下命令:

mysqladmin -uusername -ppassword pr

Then note down the process id.

然后记下进程id。

mysqladmin -uusername -ppassword kill pid

The runaway query should no longer be consuming resources.

失控的查询不再需要消耗资源。

#5

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

如果您有mysqladmin，您可能会得到以下查询列表:

> 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:

然后，您可以停止运行长时间运行查询的mysql进程:

> mysqladmin -uUSERNAME -pPASSWORD kill 137

#6

The author of this question mentions that it’s usually only after MySQL prints its output that he realises that the 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打印输出之后，他才意识到错误的查询被执行了。如前所述，在本例中，Ctrl-C没有帮助。但是，我注意到它将中止当前的查询——如果在打印输出之前捕获它。例如:

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:

MySQL忙于生成上述两个表的Cartesian产品，您很快就会注意到MySQL并没有将任何输出输出到屏幕(进程状态是发送数据)，所以您键入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.

Ctrl-C也可以用来停止更新查询。

#1

328

mysql>show processlist;

mysql> kill "number from first col";

#2