MySQL backups emailed via cron and cPanel

If you have a website hosted by a hosting service, and it uses a MySQL database, and you rely on regular backups of that database, maybe you need to handle that yourself. If the database is small enough, it might just pay to have cron email you a backup of the database periodically, which is easy enough to set up on a hosting account with cPanel as the control panel.

[Update: I’ve replaced this script with one written in Python, but still use cron just as I do below]

And yes, I decided this was a wise move after I lost a chunk of data! In the process of “upgrading” me, my host accidentally deleted my hosting account — and with it, my email accounts, my blog, SWMBO’s blog, my company website, and most importantly (from a lost data perspective) my MantisBT bug tracking database. Sure, they had backups, but the last one was about a week old so I lost quite a few entries in MantisBT. Hopefully I’ll be able to recover most of them from emails (my next job, after this blog!) but I wanted to stop that happening again.

First, I needed to determine what tools I had available to do this on my hosting server. Most good hosts allow you to ssh into your server to run commands directly, and that gave me a way to test the toolset and see what I could do. I discovered that I could run mysqldump to generate a backup file, and I could use some standard Linux tools to compress and email it to myself. But I had to fiddle around with a few things to make it usable.

I didn’t want to provide the database username and password on the command, in case someone looked at the running commands on the system and thus got access to my database. So I set them in a .my.cnf file, so that the standard MySQL utilities would know how to login without me specifying that on the command line.

MantisBT stores image attachments in the database by default. This means that the mysqldump file of the MantisBT database contains binary data. To get this to email properly, and be editable at the other end, I had to tell mysqldump to encode that data as ASCII. The option I picked was to dump binary columns in hexadecimal, –hex-blob.

The mail tools my hosting service provides don’t allow attachments. Bugger! Ah, but I can just pipe mysqldump into the email… except emails have a line limit, and that buggers up the dump file by wrapping lines in odd places. Also, it would be nice to compress that file before emailing it. So, gzip and base64 encoding to the rescue — gzip compresses the data, and base64 transforms it into ASCII-friendly characters neatly wrapped at 76 characters per line. I can then use base64 and gunzip at my end to decode and uncompress the dump file when I receive it.

So, this is now my dump to email command:

mysqldump --hex-blob mysql_mantisbt | gzip | base64 | mail -s 'mantis dump'

I ran it and checked the email, and the data came through perfectly. I was able to copy the text of the email into a file, and use base64 -d and gunzip to get back the original dump file which then loaded into my local MySQL database. Yay!

Scheduling it to run daily was also a snap, because cPanel provides a nice GUI front-end to cron; just look for the cron icon:

the Cron jobs icon on cPanel

And pick the desired run frequency off the Common Settings drop-down, set the run times, and enter the command to run:

add new cron job panel on cPanel

Voila! Job is done once a day, and dropped into my email where I can regularly test the backup files!