Use Gmail for free and easy database backups

I wrote a post about backing up databases to email a while ago, but didn’t really explain what could be done.

I use a budget web hosting company for a website that collects data that’s important to me. I don’t want to rely on the hosting company to maintain this by themselves, as I’ve been burnt this way before. I also don’t want to pay for a further hosting solution to look after my backups either.

The code below will get a sql dump of a MySQL database and email it to any address. The SQL will be the body of the email – we’re not going to try and attach it as a separate file or anything too clever.

mysqldump -h DB-SERVER -u DB-USERNAME –password=”DB-PASSWORD” DB-NAME | mail -s mysqlExport EMAIL-ADDRESS

I’ve installed this on two machines – one uses Plesk so installation was simply pasting in the command as a new automated (or scheduled) task. The other allowed me to create my own Crontabs, so I puttied into the web server and typed ‘crontab -e’ and added the new task to run every day.

In Gmail, after I was happy everything was working I set up a filter to delete the email from the server the minute it arrived. This way my database didn’t eat away at my mailbox size. Since mail is kept in my trash folder for 30 days, that’s 30 days of backups I can access whenever I need to.

Update 04/11/08
Thanks sxeraverx for pointing out that this is only suitable for smaller databases. I forgot to mention that – please only use this method on databases small enough so that creating the SQL dump file won’t be too big for your email application to accept.