[lug] cron and mysql

[Calvin Dodge]

On 3/12/06, Luke P <linuxluke_20 at hotmail.com> wrote:
>
> I am thinking about running a cron job that runs a mysql script, is that
> possible and if so, can you point me in the direction of some docs on doing
> that? In addition, I am not sure if cron is the best method for running
> scheduled sql scripts, if you have a better idea, let me know.

Sure you can!  For example, I have one which tells me about new movies
on the "Turner Classic Movies" in MythTV.

The following script does 4 things:
1) create temporary table of new movies
2) select that table (outputs to email to me)
3) adds movies to the list of existing ones
4) deletes movies before today's date, to keep table at manageable size.

#!/bin/sh
mysql --password=mythtv    -u mythtv mythconverg <<END
create temporary table newturner as
select n.title,n.starttime from program as n
  left join oldturner as o
    on n.title=o.title
    and n.starttime=o.starttime
where n.category_type='movie'
  and n.starttime>=current_date()
  and n.chanid='1067'
  and o.title is null;

select title,min(starttime)
from newturner
group by 1
order by 1;

insert into oldturner(title,starttime)
select title,starttime
from newturner;

delete from oldturner
where starttime<current_date();

END


Note that you can do parameter substitution (i.e. in a line before the
"mysql" line put something like "TITLE='Buffy the Vampire Slayer'",
then put $TITLE wherever you want the title to be embedded in the SQL
lines.


This file is called "new_turner.sh", and lives in my home directory,
so I run "crontab -e", then add the following line:

0 12 * * * /home/calvin/new_turner.sh

It runs every day at noon.

Calvin