如何设置 crontab 来执行 mysql 查询并记录输出？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9948220/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i set a crontab to execute a mysql query and log the output?
提问by Lucas Matos
Well, title self describes it.. I need to run a sql function to clean some duplicated posts, i need to do it several times a day so i need to use cron...
好吧，标题自我描述了它.. 我需要运行一个 sql 函数来清理一些重复的帖子，我需要一天做几次，所以我需要使用 cron ......
I set a new crontab job, like this:
我设置了一个新的 crontab 作业，如下所示：
00 16,18,19,20,21 * * * mysql -h MY-DB-HOST.COM -u MY-DB-USERNAME -pMY-DB-PASSWORD -e "delete from hotaru_posts where post_id in ( select post_id from ( select post_id from hotaru_posts a group by post_title having count(post_title) > 1 ) b )" >> /tmp/cron_job.log
but nothing seems to be logged, so i supposed its not working.
但似乎没有记录任何内容，所以我认为它不起作用。
Theres no problem with the sql sentence, thats not the issue here.
sql语句没有问题，不是这里的问题。
Anything wrong with my cron rule?
我的 cron 规则有什么问题吗？
回答by Lucas Matos
well, since the mysql was not working properly directly inside crontab (thought that i think that was a path issue like Alex Howansky said), i created a php file dealing this query and called the php in crontab, much easier, and give me the option to use conditions.
好吧，由于 mysql 无法直接在 crontab 中正常工作（我认为这是一个路径问题，如 Alex Howansky 所说），我创建了一个处理此查询的 php 文件并在 crontab 中调用了 php，更容易，并给我使用条件的选项。
the cron job:
定时任务：
00 8,14,18,19,20,21,23 * * * /usr/local/bin/php /home/aikaforum/cata/public_html/cron_dup.php >> /cata/tmp/cron_dup.log
the php:
php：
<?php
$username="xxxxxxx";
$password="xxxxxx";
$dbname="xxxxxx";
$dbhost="xxxxx.xxxxx.com";
$query="delete from hotaru_posts where post_id in ( select post_id from ( select post_id from hotaru_posts a group by post_title having count(post_title) > 1 ) b )";
mysql_connect($dbhost,$username,$password);
@mysql_select_db($dbname) or die(strftime('%c')." Unable to select database");
mysql_query($query);
mysql_close();
echo strftime('%c')." ok!";
?>
Thanks for all the help.
感谢所有的帮助。
回答by Moutaz Salem
execute the following in your cron configurations
在您的 cron 配置中执行以下操作
echo "your_SQL_statement" | mysql --skip-column-names -udbuser -pdbpassword yourdb >> yourlog.log
回答by muffinista
I suspect that your script is working but isn't actually returning any output. Here's my local test:
我怀疑您的脚本正在运行，但实际上并未返回任何输出。这是我的本地测试：
mysql -u username dbname -e "delete from posts" > foo
cat foo
(empty file)
Just to be clear, foo is an empty file. postswas not an empty table.
需要明确的是， foo 是一个空文件。posts不是一张空桌子。
So, just to be more precise, I think that's the expected behavior of MySQL here, although I can't confirm this in their docs. If you want/need output here, you'll probably need to write a script to check your table before/after deleting.
所以，更准确地说，我认为这是 MySQL 的预期行为，尽管我无法在他们的文档中确认这一点。如果您想要/需要此处的输出，您可能需要编写一个脚本来在删除之前/之后检查您的表。
回答by Florian
You need to use the full path to scripts executed by a cronjob. For instance if the mysql binary's location is /usr/local/mysql/bin/mysqlyou'd use that in your cronjob.
您需要使用由 cronjob 执行的脚本的完整路径。例如，如果 mysql 二进制文件的位置是/usr/local/mysql/bin/mysql你会在你的 cronjob 中使用它。
00 16,18,19,20,21 * * * /usr/local/mysql/bin/mysql -h MY-DB-HOST.COM -u .....
