如果时间太长，如何停止MySQL查询? [英] How can I stop a MySQL query if it takes too long?

问题描述

是否可以在MySQL中使查询超时?

Is it possible to timeout a query in MySQL?

也就是说，如果任何查询超过我指定的时间，它将被MySQL终止，并且将返回错误，而不是等待永恒.

That is, if any query exceeds the time I specify, it will be killed by MySQL and it will return an error instead of waiting for eternity.

推荐答案

CPAN上有一个不错的Perl脚本可以做到这一点: http://search.cpan.org/~rsoliv/mysql-genocide -0.03/mysql-genocide

There is a nice Perl script on CPAN to do just this: http://search.cpan.org/~rsoliv/mysql-genocide-0.03/mysql-genocide

只需调度它以适当的参数运行.创建一个CRONtab文件/etc/cron.d/mysql_query_timeout 安排它每分钟运行一次:

One only needs to schedule it to run with the proper parameters. Create a CRONtab file /etc/cron.d/mysql_query_timeout to schedule it to run every minute:

* * * * * root /path/to/mysql-genocide -t 7200 -s -K

其中7200是允许的最大执行时间(以秒为单位). -s开关过滤掉除SELECT查询以外的所有查询. -K开关指示脚本杀死匹配的进程.

Where 7200 is the maxiumum allowed execution time in seconds. The -s switch filters out all except SELECT queries. The -K switch instructs the script to kill the matching processes.

root用户应该能够在不进行身份验证的情况下运行本地mysql工具，否则您将需要在命令行上提供凭据.

The root user should be able to run local mysql tools without authentication otherwise you will need to provide credentials on the command line.

这篇关于如果时间太长，如何停止MySQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！