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



More information about the thelist mailing list