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:
# 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?