[thelist] cron & mysql permissions issue

Phil Turmel pturmel-webdev at turmel.org
Wed Mar 28 15:06:15 CDT 2007


Brian Cummiskey wrote:
> 
> I'd like to set up a cron job to do this, as I'm forgetful, and last 
> week i let the drive go to 100%, thus causing the database to error out 
> and killed my site for 12 hours before i noticed.
> 
> The problem is, a 'web' user doesn't have root permissions.  The only 
> way to run this is through ssh or the webhost control panel logged in as 
> root.
> 
> 
> can this be done?
> 
Hi Brian,

Access via ssh is precisely what you need to set this up (any ssh login 
that can run the mysql command line tool).

First thing you'll want is a shell script that runs the mysql utility, 
passes the required command(s), and exits.

The shell script will look something like this:

#! /bin/bash
#
# Run a MySQL command via cron
mysql --user=whatever --password=whatever \
	--execute="mysql command here" \
	dbname &>~/result-text-file \
|| cat ~/result-text-file


Then, create a user crontab to run the shell script at whatever interval 
you like.  Note that cron will email any text output of a program to the 
owner of the crontab, or to a specified e-mail address if given inside 
the crontab file.  The script above stores the output of the mysql 
program in "~/resulttextfile".  If an error is returned, the file 
contents will be mailed out.

I suggest you type "man 5 crontab" at an ssh prompt to get the precise 
syntax for the schedule, and "man 1 crontab" for the syntax of the 
command to set a schedule.  If you want to run it every day at 1am, 
it'll be something like this:

echo "* 1 * * * /path/to/my-reset-shell-script" |crontab -

Also make sure you set the script file to mode 700, since it'll have a 
visible password in it.

HTH,

Phil



More information about the thelist mailing list