如何设置crontab来执行mysql查询并记录输出? [英] How can i set a crontab to execute a mysql query and log the output?

问题描述

好吧，标题自我描述了它. 我需要运行sql函数来清理一些重复的帖子，我每天需要做几次，所以我需要使用cron ...

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

我设置了一个新的crontab作业，如下所示:

I set a new crontab job, like this:

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.

sql语句没有问题，那不是这里的问题.

Theres no problem with the sql sentence, thats not the issue here.

我的cron规则有什么问题吗?

Anything wrong with my cron rule?

推荐答案

好吧，由于mysql无法直接在crontab中正常运行(我认为这是Alex Howansky所说的路径问题)，所以我创建了一个php文件处理此查询并在crontab中调用php会容易得多，并提供使用条件的选项.

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.

cron作业:

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 

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!";
?>

感谢所有帮助.

这篇关于如何设置crontab来执行mysql查询并记录输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！