Run a MySQL Query as a Cron Job

Run a mySQL query as a cron job?

Try creating a shell script like the one below:

#!/bin/bash

mysql --user=[username] --password=[password] --database=[db name] --execute="DELETE FROM tbl_message WHERE DATEDIFF( NOW( ) ,  timestamp ) >=7"

You can then add this to the cron

How can i set a crontab to execute a mysql query and log the output?

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.

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

Can you run database queries in a PHP cron job?

You can run any PHP file using CRON. Just add the PHP file in CRON using syntax:

01 04 1 1 1 /var/www/somedirectory/somephpfile.php

You can see more options for CRON here

Then somephpfile.php can have any CRUD operation which is supported by PHP.

In short you can call any PHP file which is error free and it will definitely run (if it runs when you try to run it directly in browser)

Need to add mysql query in cron job

Added the MySQL queries in the script and included it in crontab.

Script:

#!/bin/bash
echo $(date >> /seachange/unassigned_count.txt)
mysql -D tvbsadmin -u root -pitvitv -e "SELECT COUNT(*) FROM eam_package WHERE STATUS IN (1,3);" >> /seachange/unassigned_count.txt
echo $(date >> /seachange/processlist_May07.txt)
mysql -u root -pitvitv -e "SELECT TIME,INFO FROM information_schema.PROCESSLIST WHERE INFO !='NULL';" >> /seachange/processlist_May07.txt

Cron entry:

*/10 * * * * cd /seachange && sh Mysql_output.sh

Cron log o/p:

May 7 15:30:01 verzcore1 crond[20978]: (root) CMD (cd /seachange &&
sh Mysql_output.sh)

It is working fine!

Cronjob or MySQL event?

I would always go a cron job, because:

• That's where sysadmins will expect it to be (this point is not to be underestimated)
• crontab is bullet-proof, time-tested, extremely widely used and understood
• You can freely direct/analyse error/success messages where you want
• Some database tasks require/prefer mysql to be off-line (eg full backup), so you've got to use cron for those - it's a bad idea to have some tasks done with cron and some done with mysql; you'll be unsure where to look
• You can chain up other events that should follow if you've got a shell script

And finally, just because you can do something, doesn't mean it's a good idea. Mysql is good at data stuff. Don't use it for "shell" stuff.

Cron job using database results

First of all IMHO you don't need to pass current time to your select statement, just use CURRENT_TIME

SELECT `show.file` 
  FROM show, schedule 
 WHERE channel = 1 AND start_time <= CURRENT_TIME;

Depending on your actual table's DDL you might need to do some conversion to correctly compare time values.

Assuming that your query correct and returns ONLY ONE filename you can execute the query with mysql, output the result into predefined file and use && to chain ffmpeg command

mysql -u user -ppassword dbname -sN -e \
 "SELECT show.file \
 FROM show, schedule \
 WHERE channel = 1 AND start_time <= CURRENT_TIME" > /tmp/cur_show_file && \
 ffmpeg -re -i /tmp/cur_show_file http://127.0.0.1:8090/feed.ffm

update and select in one query in mysql using cron job

I guessed you want to update 30dexpf field to 1 if the expiry date is exactly 30 days far from current date. Otherwise the 30dexpf is supposed to have 0.

UPDATE user_pages
SET `30dexpf` = CASE
                WHEN (
                    DATE(expiry) = CURDATE() + INTERVAL 30 DAY
                ) THEN 1 ELSE   0 END;

Powershell sql query with logic inside cron job

You can create a shell script e.g. test.sh that will contain mysql query.

output=$(mysql -u myuser -p mypassword -e 'query')

Then use the variable output for further processing.

And to put the shell script in Cron Job use the following command:

crontab -e

Add new entry

*  *  *  *  *  sh path/test.sh  &> path/test.out

Here * * * * * denotes MIN HOUR DayOfMonth MonthOfYear DayOfWeek respectively.

---

Related Topics