如何设置 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屋！