What is the most efficient way to implement a cron job to back up a MySQL database and e-mail it to myself?

Question

What would be the most efficient way to automatically back up a MySQL Database, gzip it, and e-mail it to a specific account?

I am a bit paranoid about losing my forum's database and I'm usually busy during the week and often forget to do a weekly backup (instead, doing bi-weekly, or monthly, which isn't too bad, but less frequent than I'd like). Having it set up with a cronjob and an e-mail (or FTP/SFTP as I've just realized one of the databases would be too large to attach in an e-mail) would be a lot easier, as well as less prone to me forgetting.

How I'm looking at it now would be:

mysqldump dbname > YYYY-MM-DD_dbname.sql gzip YYYY-MM-DD_dbname.sql Either send YYYY-MM-DD_dbname.sql.gz as an attachment to an e-mail address or {S,}FTP it to another server and send an e-mail letting me know that the backup and transfer was successful.

Is there a better way to accomplish this? Any examples of how you'd accomplish it?

Answer 1

I don't think you can do much better than what you've got. I'd probably use something like the following:

• create the database dump
• gzip it or otherwise compress it (technically optional)
• use scp or rsync to copy the file to another server
• run a checksum on the remote server to verify the copy (optional)
• delete the original database dump (optional)
• send an email indicating success

Or if any step of this fails, quit and immediately send an email explaining the failure. (I'm assuming you have the necessary tools available, e.g. rsync or scp, sha1sum or md5sum, gzip, mail or sendmail or equivalent)