Using GMail to backup your MySQL database

I have few gmail accounts and some are really under utilized in terms of storage.

So I put up a simple script to be ran as a cron job to backup my database and auto email it to gmail for good.

NOTE: This should be feasible only if you have relatively small database.

Requirements: You will need to have mysqldump, tar, and nail programs installed.

Create this simple shell script in your home directory, I call it backup_db.sh

#!/bin/bash
mysqldump --user=your_db_username --password=your_db_password --all-databases > mysql.db.backup.sql
tar zcvf mysql.db.backup.tar.gz mysql.db.backup.sql
rm mysql.db.backup.sql
nail -s "Database backup" -a ./mysql.db.backup.tar.gz dummy@gmail.com < /dev/null
rm mysql.db.backup.tar.gz

Then in your cron job, add this line:

01 23 * * 0 /home/your_username/backup_db.sh 2>&1

This will run the back up script once a week on Sunday, 1101pm. You can modify the timing and frequency to your preference.

In this way, you will see an email sitting in your gmail inbox with your database backup, on every monday morning

Assuming your mysql database is relatively small, say about 10MB in size, and you have 7G gmail account; then 7G / 10MB / 54 weeks = 12.9. In another word, with a gmail account, you can backup your data for almost 13 years. Heh.

Then again, this solution is probably only good for small web application, say personal blog kind of thing. But shall be a good way to utilize your gmail storage (seriously, anyone really using > 1G of gmail space?).

Have fun backup-ing.

$ssh username@linode-ip-address -D 9999