1

I have this query that outputs updates older than 7 days from a database. How would I make a script that takes the MySQL statement and puts it into a script to be executed by cron.

echo "select name, reported_at from nodes where reported_at < curdate() -7;" |
mysql dashboard

Sample output:

name    reported_at
xadf.edu    2012-03-21 14:39:02
xadf.edu    2012-03-22 15:30:01
adsfsadf.edu    2012-03-14 14:40:02
ekdahlj.edu 2012-03-23 03:40:04
adfasdf.net 2012-03-21 14:42:02
eqrsdr.edu  2012-03-15 14:42:02
qwerwfva.edu    2012-03-13 14:42:03
qerqwer.edu 2012-03-23 14:40:01
adfasde.edu 2012-03-05 17:42:03
bsfdgs.edu  2012-03-23 15:20:01
adfadsf.edu 2012-03-23 14:43:01
  • 2
    What do you have exactly? All I see is a path & a report output or something. – Mat Mar 30 '12 at 16:24
3

You could create a file ~/path/to/myquery.sql:

select name, reported_at from nodes where reported_at < curdate() -7;

And to edit your crontab execute

crontab -e

And in your crontab add a line

* * * * * mysql dashboard < ~/path/to/myquery.sql > ~/path/to/query/output

To edit how often this command is run, you have to edit the five *'s at the start of that line. To understand how to do this properly, you can check out this page.

1

Just save it in a file, then redirect it into the mysql tool in your crontab.

* * * * * mysql ... < savedstatements.sql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.