Free Cloud Based Database Backup Solution
This morning I altered my gmail based database backup script to allow for larger than 25 Megabyte databases. My previous solution worked great until my DB went over the gmail attachment limit of 25M.
This works great for me. I have a small 80m (uncompressed) database that I need to make nightly backups of. It may not for you. The idea here is to use gmail as a free cloud based backup solution. If you have a multi-terrabyte database I hope you have enough money to pay for a fancy solution.
As I said, my DB is about 80M uncompressed or just over 25M compressed. Just over the gmail attachment limit for a single message. So to get the job done I need to dump the DB, compress it, and split the files in to < 25m chucks, then encode and mail to my gmail backup account.
I toyed around with “mpack” as a one command solution for splitting, encoding, and emailing the files but ran into problems when I tried to reassemble the file. mpack works great but when it splits and emails the files, the first file has MIME header info to make it appear as an attachment. The second or subsequent files are emailed as plain inline (encoded) text. I know mpack is used to send huge amounts of data to binary newsgroups but in my 15 minutes of screwing with it, I could not get it to add a MIME header to each part (email) of the file. “munpack” is supposed to read the mpacked files and do this for you but it could not work with the files that I produced. The mpack output was unacceptable as it required way to much by-hand processing to re-assemble the gzip. I still used mpack to email the files but i use “split” to break them up before mailing.
What sucks about my script? Everything except the fact that it currently works for me and will work until the database size doubles which should be a while. Specifically it’s not dynamic at all. The script assumes we’re breaking the database into just two files (50M limit). Also it has no logging or error handling. But hey, I’m not an SA nor a scripter. Just a guy who needs to back up his database. Here’s the script:
export d=`date +%F`
mkdir YourDBName.db.""$d""_backup
cd YourDBName.db."$d"_backup
mysqldump --opt -u YourDBusername --password=YourDBpassword YourDBName |gzip -f >YourDBName.db."$d".gz
split -b 20m YourDBName.db."$d".gz YourDBName.db."$d".gz_part_
mpack -s YourDBName.db."$d".gz YourDBName.db."$d".gz_part_aa YourBackupAccount@gmail.com
mpack -s YourDBName.db."$d".gz YourDBName.db."$d".gz_part_ab YourBackupAccount@gmail.com
cd /root
rm -Rf YourDBName.db."$d"_backup
That’s it. To restore the database just do this: Check your email. Download the attachments and combine them. I just use “cat attachment_ab>>attachment_aa” which appends the contents from the 2nd file (ab) to the first (aa). Then you end up with a gziped version of your database under the filename of the first emailed file (*aa). To make it easy, rename the _aa file to YourDBName.gz. Then gunzip and do a “mysql -p YourPassword YourDBName < YourDBName”
Viola. Enjoy your Free Cloud Based Database Backup. It’s saved my ass more than once!