SQL Backups

With thanks to Andy Budd's Page, I have finally worked out how to do decent backup/restores.

I created a text file called sqlbackup, in the file is this:

#!/bin/sh

# echo start message echo "Backup Script Processing"

# navigate to backup dir if cd ~/backup.sql/latest then echo "...successfully navigated to backup dir" else echo "could not locate backup directory" exit 0 fi

# echo message echo "exporting SQL dump"

if #dump the db into a .sql file mysqldump --user=SQLUSERNAME --password=SQLPASSWORD.... .... SQLBLOGNAME --opt | gzip -c > backup-mt.sql.gz; then #echo success message echo "SQL dump successful" ls -la else #echo error message echo "mysqldump error" exit 0 fi

My crontab has:

# Backup MySQL 5 0 * * * /home/murk1e58/sqlbackup >> ~/error.log 2>&1 56 23 * * * cp ~/backup.sql/latest/backup-mt.sql.gz ~/backup.sql/daily/$(date +\%A).sql.gz 58 23 28 * * cp ~/backup.sql/latest/backup-mt.sql.gz ~/backup.sql/monthly/$(date +\%B).sql.gz

(The lines marked .... should be one one line, I have split them here to make sure the line is narrower than most screens). To restore the backup, one types:

gunzip backup.sql.gz mysql --user=username --password=password sqldatabasename < backup.sql

All good stuff!

Of course, what I really need is a completely seperate server, with similar features to this one (cpanel, command line etc), then I could sent the backup to it automatically and have it mirror this server!

In the short term, I would like to work out how to email the resulting file..... any ideas?