Backing up your Wordpress Database

What this article is about

• Daily backups of your Wordpress database
• Daily e-mail with a compressed copy of the database

Last week I managed to setup a nightly automatic backup of my Wordpress files. That still left securing the database as an unfinished task. And of course it will be handy to have a good backup when I prepare to upgrade to Wordpress 2.5

So today I wrote a little script that backups the database on my hosting provider nightly. This is no great difficulty. It just expands the example from the Wordpress codex a little. Here is what you can do to setup your own database feel-all-good backup scheme:

Step 1 - The Script

After logging in through SSH to your hosting provider, create a place to store the backup files. This should not be in your webserver directory (otherwise a smart person might just get lucky and find the location of your files).

mkdir ~/wordpress_backup

The script itself is called “backup_db.sh” and placed in this directory.

The username and password information comes straight from your wp-config.php file.

#!/bin/bash

# Setup
USER=username_for_wordpress_db
PASSWORD=password_for_wordpress_db
DATABASE=your_wordpress_database
HOST=localhost

DIRECTORY=~/wordpress_backup

# Your e-mail address for reporting errors
SYSOP=youremail@gmail.com

MailError()
{
 MSG="`hostname` reports wordpress database backup failed!"
 SUBJ="Database Backup Failed"
 echo $MSG | mail $SYSOP -s"$SUBJ"
}

# Keep up to three copies
if [ -f $DIRECTORY/db.2.gz ]; then
 mv $DIRECTORY/db.2.gz $DIRECTORY/db.3.gz
fi

if [ -f $DIRECTORY/db.1.gz ]; then
 mv $DIRECTORY/db.1.gz $DIRECTORY/db.2.gz
fi

if [ -f $DIRECTORY/db.today.gz ]; then
 mv $DIRECTORY/db.today.gz db.1.gz
fi

# Make the backup
mysqldump $DATABASE --add-drop-table -h $HOST --user=$USER --password=$PASSWORD | gzip > $DIRECTORY/db.today.gz

if [ $? -gt 0 ]; then
   MailError
   exit 1
fi

# Optional: mail the backup file to ourselves
perl $DIRECTORY/mail.pl

As the file contains password information make sure to make it less accessible:

chmod 0700 backup_db.sh

To see if this works simply run the script (sh backup_db.sh). Make sure to unpack the created zip file to verify that the database was properly dumped.

Step 2 - Running the backup automatically

This step depends on your hosting provider. My own provider (Hostmonster) does not provide the “crontab” facility I has used earlier on my own computer for nightly backups.

CPanel - Cron Jobs - Advanced Unix

In the cpanel interface they do provide a “Cron Jobs” option. So here I simply setup a similar entry that runs every morning at 2am.

If neither of the above options are possible the WP-Cron plugin might also be an option you can consider.

Step 3 - Emailing the database backup

The simplest solution for securely storing the database is to e-mail the backup file to myself.

My hosting provider does not provide the most common options for sending mime attachments (such as by using mutt) from the command line.

They do however provide Perl, and with a little searching and hacking the following Perl script nicely handles the mailing of my backup file every night.

Save the script as mail.pl in the ~/wordpress_backup directory. It will be called automatically from the above “backup_db.sh” script.

#!/usr/bin/perl
# Akadia AG, Arvenweg 4, CH-3604 Thun                 send_attachment.pl
# ----------------------------------------------------------------------
# File:       send_attachment.pl
# Autor:      Martin Zahn / 05.01.2003
# Purpose:    Email attachments in Perl
# Location:   $ORACLE_HOME\Database
# Certified:  Perl 5.6.1, MIME-Lite-2.117 on Cygwin / Windows 2000
# ----------------------------------------------------------------------

use MIME::Lite;
use Net::SMTP;

### Adjust sender, recipient
my $from_address = 'me@myblog.net';
my $to_address = 'me@gmail.com';

### Adjust subject and body message
my $subject = 'Daily backup of Wordpress Blog';
my $message_body = "Here's the attachment file(s) you wanted";

### Adjust the filenames
my $my_file_zip = 'db.today.gz';
my $your_file_zip = 'db.today.gz';

### Create the multipart container
$msg = MIME::Lite->new (
  From => $from_address,
  To => $to_address,
  Subject => $subject,
  Type =>'multipart/mixed'
) or die "Error creating multipart container: $!\n";

### Add the text message part
$msg->attach (
  Type => 'TEXT',
  Data => $message_body
) or die "Error adding the text message part: $!\n";

### Add the ZIP file
$msg->attach (
   Type => 'application/zip',
   Path => $my_file_zip,
   Filename => $your_file_zip,
   Disposition => 'attachment'
) or die "Error adding $file_zip: $!\n";

### Send the Message
$msg->send;