How to back up your MySQL database automatically with Gmail

If you’ve got a dynamically generated website running on open-source software, odds are that its data is kept in a MySQL database. This is certainly true of this blog, and every blog that runs on WordPress. All your posts, links, comments — they’re all kept in a big database on your host’s server.

You can imagine how backing up that database might be a good idea.

If you subscribe to a hosting service, they may your site may be periodically backed up on a server-wide basis (my host recently switched to Righteous Backup), but the first principle of backups is the more, the better. As it turns out, it’s very easy to automate the backup process. Using this tutorial, every week, you’ll receive an email with a compressed copy of your database attached. These emails will auto-rotate, meaning that after the first month you’ll always have four (sometimes five) dated versions. On top of that, they won’t count against your Gmail storage limit. I can’t take credit for the one-line script that does the brunt of the work, but the Gmail tip is my own.

To get this working, you’ll need cron (you’ve already got it), mutt (get it here), and a Gmail account. Cron is the *-nix program that runs scripts at regularly-scheduled intervals. Mutt is an extremely light-weight email client — so light, in fact, that it can send messages from the command line. That’s what we’ll be using it for in this script. Finally, of course, Gmail is the place we’ll be storing all these backups.

The first thing to do is to set up your cron event. If your server uses CPanel, then half your work is done. Just log into it (search your host’s documentation for the URL you need) and find the icon labeld “Cron jobs.” Here you’re presented with a graphical interface to cron’s basic options. You should be able to easily set the frequency of your backups. You can skip the next paragraph.

If you don’t have access to CPanel, fear not! This is simply an invitation to become more familiar with the Linux command line! In basic terms, cron works by having a daemon (”crond“) wake up once per minute and scan a file called “crontab” to see if it has anything to do. So to set up our backup job, we’ll need to edit crontab. It lives in /var/spool/cron/, so you can open it up from the command line like so:$ nano /var/spool/cron/YourUsername. The Wikipedia article I linked above gives more information on crontab’s scheduling conventions, but for now let’s assume you want to generate your backup email every Sunday night at 3:30 am. Using the scheduling structure, you’d enter a new line that begins with 30 3 * * 7.

Now, whether you’re using CPanel or crontab, you’re ready for the mighty shell command itself. This comes courtesy of user “jaseone” in this thread from ASmallOrange’s support forum. In CPanel, you’ll enter it in the “command” field; if editing crontab directly, put it after a single space on the same line you entered your scheduling information. Without further ado:

mysqldump -Q -uuser_name -ppassword database_name | gzip > /backup/directory/path/`date +%A`.gz && mutt -s "`date +%A`'s backup gmailhook" -a /backup/directory/path/`date +%A`.gz you@gmail.com < /dev/null

Be sure to preserve this all as one line. When entering it in, you’ll want to change the placeholder variables to reflect your own details: user_name becomes the creator of your database, password becomes your database’s password (not your account password!), and database_name is the database that you want to back up (handy tip: if you’re backing up a WordPress database, all these values can be found in /wordpress/wp-config.php). You’ll also need to change both instances of /backup/directory/path to reflect the directory you want your backup created in before it’s emailed. If you don’t have a good place already, open up a new terminal window and run mkdir ~/backups/, which creates a new directory in your home folder called “backups.” Last but not least, substitute your own Gmail address near the end.

Once your cron job is set up, you’re almost done. You’ll have an email in your inbox every Sunday morning with a compressed copy of your database attached. The last step of this how-to will whisk these out of your sight automatically, and keep the attachments from counting against your storage limit in Gmail. The key here is to create a filter that will pick out these automated messages. From your inbox, click the “Create a new filter” link next to the search buttons at the top of the page. You’ll want to enter some characteristic here that appears in all your backup messages, and doesn’t appear at all in any other message. For our purposes, the phrase “gmailhook” in the mutt command we entered above will serve this purpose nicely, so enter it in the “Subject” field here. Click “Next step.” Now, it’s time to tell Gmail what to do with these messages. First, you’ll want to apply a label, so check that box and hit “Create new label…” at the bottom of the dropdown list. Call this label anything you’d like; it will help you find your existing backups in the event that you need to restore one. Next, also check the box next to “Delete it.” What?!

Herein lies the trick. Gmail automatically empties your trash of anything that’s been in there for more than thirty days. This keeps your backups current — once you build up your first four archives, you’ll always have at least that many. But there’s another trick to your trash: messages marked as deleted don’t count toward your storage limit! This is probably not a major deal, as MySQL databases don’t necessarily run large (at 1.42MB, my >250-post database could fit on a floppy disk before compression!), but it’s still a nice advantage.

At any rate, hit the “Create this filter” button, and you’re done! I hope this tutorial works for you, and that — should the day ever come — your backups will come in handy!