_Upstart Blogger

There are numerous ways to backup and restore your MySQL database, from the simple to the complex, both manual and automatic, some reliable and others spotty. In this post, I’ll outline several strategies for keeping your MySQL database safe and secure. Since I use WordPress, I’ll talk specifically about backing up a WordPress database, but one of these strategies is sure to work for you regardless of which blogging software you use.

Mini Rant: I never gave much thought to backing up my database, and I suspect you haven’t given much thought to it, either. You think it won’t happen to you, but believe me, when something does happen it can do more than ruin your day. If you don’t have a backup, you can lose days, months, or even years of hard work. You can’t trust your host, no matter how good they are. And you can’t ignore the possibility of “pilot error.” Just yesterday, I was making some corrections and in my haste clicked the wrong button, erasing two days of work since my last manual backup and losing several new entries. Now that I’ve figured out how to automate my backups, all I can say is: Have a backup plan!

Backup Strategies

If you use WordPress, bookmark this page at the WordPress Codex: Backing Up Your Database. You’ll find instructions for several methods of backing up, including many of those outlined below (I’m not going to give detailed instructions for these, but keep reading for my preferred method). Here are your options:

1. phpMyAdmin
phpMyAdmin is a web-based interface for accessing your MySQL database. Here are two links with clear, simple instructions: How to backup and restore a WordPress database and WordPress Backup with phpMyAdmin.

Be sure to select the proper settings:

2. Straight MySQL code
You’ll find instructions at Backing Up Your Database, but this method is not for beginners.

3. A MySQL client, like MySQL Administrator or CocoaMySQL
Administrator (pictured below) is a MySQL client, available for Windows, Mac, and Linux. Download it from the MySQL site. You can schedule daily, weekly, or monthly backups with MySQL Administrator (of course, there’s nothing to prevent you from creating multiple backup projects to run at different times to have several backups during the same day). The backups you schedule are executed using cron (see below for more cron options). If you want to download backups directly to your computer, with complete flexibility over what tables are included, this may be the best solution.

If you’re a Mac user, CocoaMySQL (pictured below) is another option. Be sure to get the latest version (the beta version as I write this), or you may have some problems. This is my preferred client for the occasional manual edit (but be very careful when you delete something, so you don’t accidentally delete your entire database, as I did!). You can also easily export and import databases.

4. WordPress database plugin
This plugin, available from Skippy at Skippy’s wp-db-backup page, lets you backup from within your WordPress admin. You can manually backup your database to your server, download it to your computer, or have it emailed to you. It automatically backs up the core WordPress tables and lets you choose whether or not to include additional tables, such as those created by popularity plugins (these tables can make the files very large, and the information is less critical, so it isn’t necessary to back these up).

Skippy also has a WP-Cron plugin, which lets you schedule a daily backup and have the file emailed to you.

Skippy’s plugins should be a good solution for most WordPress users, if you can get them to work. I wasn’t so lucky, so I had to look for another solution.

5. cPanel
If you’d rather not deal with any plugins or MySQL clients, probably the easiest way to manually backup your files is to go to Backups in cPanel and just download your database from there. Even if you’re not using cPanel, there’s a good chance your host’s control panel offers a similar feature.

Ideally, you want a solution that automatically backs up your database and sends the files to you (or a remote site), so they will be stored away from your host.

To automate your backups, you’ll need to use crontab (or cron). But you probably know as much about crontab as I do, namely, that it’s used to schedule the execution of commands. I couldn’t begin to tell you how to write a crontab file or a bash script. Fortunately, we don’t need to; there are several readymade scripts available.

6. Mutt-compatible cron script
If your host has Mutt (a Unix-like email client) installed, you can use this Cron Script from Tamba 2 (where you’ll also find an excellent selection of WordPress guides). This script is based on automysql, which also requires Mutt.

The problem with Cron Script and automysql is that your host probably doesn’t have Mutt and won’t install it on a shared server. But I found a script that doesn’t require Mutt:

7. Automated cron backup thru email
Otherwise known as cron_backup_1.2_without_mutt, this script uses uuencoding instead of Mutt (you can also use Mutt by simply uncommenting a line in the program). From the instructions: “This program works for CPanel on Linux/Unix operating systems and requires Linux commands such as such as gzip, tar, (mutt), uuencode, mail and mysqldump.”

Installation isn’t particularly complicated, but it can be tricky. Since I couldn’t find any documentation (other than that provided in the script), but got it working through trial and error, I’ll give you step by step instructions. This is currently my preferred method.

You can download the script here, but you’ll need to make some minor changes. I’ve made some of those changes for you. If you like, you can save yourself some time by downloading my version: upstartblog_cron_backup_1.2_without_mutt (my archive includes both versions).

Here are the instructions:

First, edit the cron_backup.sh file (if you’re using upstartblog_cron_backup.sh, you can skip steps 3 thru 6).

1. Change lines 65 through 67, replacing {dbname}, {dbpass}, and {dbuser} with your information (make sure you delete the { }).
2. Change line 70, inserting your email address within the quotes (keep the quotes). If you’d rather not fill up your primary mail box, use a Gmail account, which offers 2.5 gigabytes of storage.
3. Comment out (or delete) line 76. Just add a # before the first character on the line.
4. Comment out (or delete) line 81.
5. Comment out (or delete) line 95.
6. Comment out (or delete) line 98.

After editing the file, upload cron_backup.sh ( or upstartblog_cron_backup.sh) and cron_uudecode.sh to your server. Be sure to upload the files to a private directory, NOT to your public_html directory, where everyone can see them. I recommend putting it in your /etc/ directory (go up one from your public_html directory).

Chmod (change the file permissions) of cron_backup.sh to 711 (-rwx–x–x).

Now, you’ll need to set up your cron job.

1. Go to Cron Jobs in cPanel and click the Standard button.
2. Enter your email address so you’ll get the output from the cron job if there are any errors.
3. Enter the path to cron_backup.sh where it says Command to run. This will be something like:
   /bin/sh /home/yourlogin/etc/cron_backup.sh
4. Select your schedule. Choose a schedule that matches your blogging activity.
5. To test that everything is working, enter Every Minute for now. In short order, you should receive an email with the subject, MySQL Backup.

There’s just one problem. The attached file will be named ._etc_MySQL.sql.gz.UU. In other words, it’s going to be invisible if you just drag it to your desktop. Instead, save the attachment and change the name when the Save dialog pops up. You can delete ._etc_. and .UU (if you don’t delete the .UU extension, StuffIt will give you an error message when you decompress the file). Leave the .gz extension intact and you shouldn’t have any problems. You can save the messages in Mail (or GMail) until you need them, so this shouldn’t be too much of a hassle.

8. RSS Feed
What does your feed have to do with backing up your database? This is the last resort, pull a rabbit out of a hat, save your ass method.

Usually, I compose posts offline, then paste them into WordPress. But sometimes, when I’m writing up some short posts, I just compose in WordPress. (I know I should use something like ecto , but I don’t. I just today discovered that I can post entries with the outstanding freeware app, Journler, which is what I’ll be using from now on).

Let’s say you’ve been prolific today, posting four or five new entries, composing in WordPress (or your system of choice), and the bomb goes off before your next scheduled backup. If you publish the full text of your entries, you can just pull the recent entries from your feed. If you only publish summaries, you’ll be out of luck.

I publish summaries because I want people to come to the site and see what else I’ve got (including my ads). But after yesterday, I’ve created another, private, feed that publishes the full entries. Just for emergencies.

If you want to publish summaries, but still have access to your full entries, the easiest way to manage this is to use FeedBurner. Set WordPress (or your blogging system) to publish full text (in WordPress, set this under the Options>Reading Options panel in your WordPress admin), then set Feedburner to publish summaries (under the Optimize tab for your Feedburner feed, select Summary Burner and activate it). Add the full feed URL to your newsreader and make sure it gets updated frequently. It could be a lifesaver.

9. Google Cache (The Bonus Strategy!)
When all else fails, see if your pages have been cached by Google (you may have to wait a day or two before new entries are cached). Click on Cached under the summary, then copy and paste. Google adds highlighting to your search terms, so you can’t just cut and paste the source code, a hassle if your post has lots of links, but I saved my work from yesterday with Google cache.

Restoring Your Database

First, MAKE SURE YOU HAVE A GOOD BACKUP. If you haven’t already lost your existing database, it’s a good idea to back it up first. Some of your data is better than none of your data.

Then, empty your existing database. You can delete everything before restoring, or just delete the database in cPanel and create a new one.

Here are three easy ways to restore your database:

1. phpMyAdmin
To restore your database in phpMyAmin:

1. Open phpMyAdmin and select your database.
2. Click on Import, and click on the Choose File button.
3. Find the file and upload it.

Note: If your file isn’t too large, you can also click on the SQL tab and simply copy and paste your backup into the window (see Tamba2’s instructions for restoring your data with phpMyAdmin). I’ve done this with files of several megabytes without any problems.

2. cPanel
If you’re using cPanel, just go to Backups, click Choose File (under Restore a MySQL Database), and upload the file.

3. MySQL Client
If you used MySQL Administrator or CocoaMySQL to backup, it should be pretty easy to figure out how to restore your database. Click on the Restore icon in Administrator or File>Import in CocoaMySQL.

If you made it this far, don’t stop here. Put your backup plan in place NOW! You can thank me later.