How to backup your MySQL database to a GMail account

So, you’ve got a MySQL database on your machine, and you want it backed up regularly. This little script can be put into your Mac’s /etc/periodic/daily/ or /etc/periodic/weekly directory. You probably then want to do a “chmod a+x” on it.

set YESTERDAY=`/bin/date +%Y-%m-%d`;/usr/local/mysql/bin/mysqldump -c -v --all-databases -uroot -pyourmysqlpassword --single-transaction --all | bzip2 | uuencode "your-mysql-backup-$YESTERDAY.sql.bz2" | mail -s"your-mysql-backup-$YESTERDAY.sql.bz2"

Substituting the appropriate passwords and e-mail address, of course.
The script uses mysqldump to create a text dump of the contents of all databases, uses bzip2 compression on this, then uuencodes the file so that it can be safely e-mailed (at a slight size overhead).
Best to do a test backup first and work out how much space your backup will take if you’re worried about exceeding the 3Gb limit or having the message rejected as too big for Google’s mail server, as I’ve just had happen with one of my databases. I obviously need to write a script that segments the file before sending 🙂
Of course, if you don’t want Google to read your database and feed it to the US Government or our New AI Masters (whom I, for one, welcome), you should probably add some encryption into the script, probably after the compression unless you wish to push entropy uphill.

Leave a Reply

Your email address will not be published. Required fields are marked *