A quickie-techie follow-up to an old post in which I exposed the simple, yet overlooked idea of having backups of your DB automatically sent to a gMail account.

I just had to implement that for a client, and did it without using neither cpanel nor a canned PHP script: this time, it’s all self-contained (provided you have a *nix server), so I thought I’d share. It’s actually even easier than before…

All you need is access to your cron jobs (cpanel gives you that, otherwise ssh) and a way to upload a script to your server (either ftp or ssh).

1. Create a new MySQL account for your backups. Give it SELECT privileges on all the DB you want to save, no other privs.
2. Create another file (pico will do that for you if you are using ssh) and insert the following commands:
   rm auto-db-backup.gz
mysqldump -udb_backup_user -pdb_backup_pwd -B db_to_save_1 db_to_save_2 | gzip > auto-db-backup.gz
echo “Automated DB backup” | mutt -s “Auto DB backup” -a auto-db-backup.gz your-address@gmail.com
3. Make your file executable by typing (assuming you have named it ‘auto-backup.sh’):
   chmod a+x auto-backup.sh
4. Append the following line to your cronjobs file:
   08 01 * * * /usr/home/path-to-your-script/auto-backup.sh
   (this line will send the backup every day at 1:08am, check out cron documentation for details).

That should do it!

Ok, time to get the hell out of the office…