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

0

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?

James Valente

Posted 2010-06-26T01:23:53.367

Reputation: 51

Answers

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)

David Z

Posted 2010-06-26T01:23:53.367

Reputation: 5 688

I wouldn't mark the checksum part as optional. The day the db crashes and you're all happy because you have a recent backup, only to find out it was corrupted is not something I would like to experience.

In addition, I'd probably use xz instead of gzip, only because the resulting file is smaller in size. – tamtakos – 2010-07-14T17:49:06.540

@tamtakos: if you skip the checksum step, as long as nothing goes wrong with the other steps, the database dump will be transferred. That makes it optional. (As opposed to, say, the scp step which cannot be omitted) What I think you mean to say is that the checksum should be highly recommended, and with that I agree. – David Z – 2010-07-14T21:17:17.113

Asked: 2010-06-26T01:23:53.367

Viewed: 324 times

Active: 2010-07-14T21:17:59.483