To use crontab to set mysql dbase backup

[Bullit]

Hi there,
I have entered the world of cron jobs and am less than experienced!

I want to use crontab manager in my webhosting control panel to automate dbase backups...

I think this should work for a database backup:

In a db_backup.pl file: run from the crontab manager at a specific time.

/usr/bin/nice -20 /usr/bin/mysqldump -q --hdbhost.com --user=username --password=password dbname /usr/www/users/ourname/backup/database_name _`date "+%Y%m%d"`.sql ; cd /usr/www/users/ourname/backup ; /usr/bin/find *.sql -mtime +7 -delete
>/dev/null 2>&1

##comments###
# hostprovider want us to nice the task with val 10+
# -q to avoid exceeding memory lims for large tables
# host user and pass and dbname provided
# output to database_name with date.sql
# change dir to the backup folder
# delete any .sql older than 7 days
#If any output is produced by a command executed from a crontab, the cron daemon will normally email the user that output.
#thus >/dev/null 2>&1
#
#could change dir to a success_email.txt and send that email to me@myhost.com
#not sure of the syntax to do this
#how can we find out about errors?

I would value input from anyone who is familiar with this task.
I'd love a report emailed to me of success / fail etc

Cheers

database is mysql

 

[philg]

The > /dev/null 2>&1 at the end of the command send all output to stdout and stderr to /dev/null, deleting that bit means cron will catch all output including any error messages and email to the user running the cron job.

The rest of the command looks allright to me, just change the username, password, database name, file paths to suit your setup

 

[Bullit]

Thanks for the reply.

I put > /dev/null 2>&1 to switch off the email report, as I think it will go to my boss who's addess is registered for the account.
Plus wouldnt it send the finished .sql file too? I dont want that.

I would however like to send an email report to myself of success or fail. Would you know how to do that?
That'd be the icing on the cake for this project.

 

[philg]

No it wouldn't send the sql file by email.

You can set the MAILTO directive in the crontab to have output from cronjobs sent to you, or you can pipe the output of the command through mail and send it to you that way.

If you just want a Success or Failure in your email then you'll have to catch exit codes, normally programs should return 0 on a successful execution, you should check all your commands to make sure. example:

#!/bin/bash

EXIT_STATUS_TOTAL=0

[i]command one[/i]
EXIT_STATUS_TOTAL=$[ $EXIT_STATUS_TOTAL + $? ]
[i]command two[/i]
EXIT_STATUS_TOTAL=$[ $EXIT_STATUS_TOTAL + $? ]
[i]command three[/i]
EXIT_STATUS_TOTAL=$[ $EXIT_STATUS_TOTAL + $? ]

if [ $EXIT_STATUS_TOTAL -eq 0 ]; then
    RESULT="Success"
else
    RESULT="Failure"
fi
echo $RESULT | mail -s "SQL Backup" [email]you@somewhere.com[/email]
exit 0

 

[Bullit]

You've been a great help philg. Thanks for the input.
If i get this up and running its bonus points with the boss!
(Especially after nearly losing the whole client database on Monday... can you beleive we didn't have a backup. Nightmare!)

Thanks again.

 

[philg]

Your welcome

 

[Bullit]

My cron job is running well.
I'll post the code here soon - maybe it will help others.

Just one snag left to iron out on it...

When this line runs:
/usr/bin/mysqldump -q -hdbhost.com -uusername -ppassword dbname /usr/www/users/ourname/backup/database_name_`date "+%Y%m%d"`.sql ;

The result should be:
database_name_20060128.sql

but I get:

database_name_.sql

Any idea why?

I run the cron via crontab manager using the command:
sh cronjobfilename.sh

If anyone knows why my date function is not executing please give me a shout.
Thanks

 

[philg]

You probably need to specify the absolute path to date, eg on my system it is /bin/date

 

[Bullit]

so:
dbname /usr/www/users/ourname/backup/database_name_`/bin/date "+%Y%m%d"`.sql ;

should do it i guess?

will let you know.
ta once agin!

 

[Bullit]

Hi Philg,
The script is now creating the files, but I cant transfer / delete them in ftp software. When I telnet in and ls I see ?'s after each filename.
Do I have to close the files after mysqldumping to them or something?

Help appreciated.
Thanks


#!/bin/sh
/usr/bin/nice -20 /usr/local/bin/mysqldump -q -hhost.com -uusername -ppassword databasename > /usr/home/backup/databasename_`/bin/date +%Y%m%d`.sql
cd /usr/home/backup
/usr/bin/find *.sql -mtime +7 -delete
echo "Database backup complete. Please FTP in and collect resulting .sql files" | /usr/bin/mail -s "Database backup complete " me@me.com ;