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;
Subscribe to this blog's RSS feed
Backup your Wordpress installation with rsync
for wordpress by gillesklein
What this article is about
Daily Incremental backups of your Wordpress blog files
Daily report by e-mail on the status of the backup
What it does not cover
This script does not make a backup of your Wordpress MYSQL server. In the near future I will write another script that does this automatically every night [...]
Tuning your blog for Google with some SEO magic
When a visitor comes across your website in Google what is the first thing they notice? And what is the second thing they notice?
The first thing to hit your eyes is the title. And in this example my blog is giving a boring, non-descriptive introduction to my site. It is just the title and [...]
Speeding up Wordpress with WP Super Cache
A Really Fast Wheelie by DavidHT
Your moment of fame has arrived and with it the unwashed masses of the Internet flock to your site to gawk at your latest insights. As you have bought the promotion priced hosting with its retirement age web server it dies a horrible dead just seconds into the rush.
No budget [...]
Showing Code Snippets on your Blog
Code is Poetry by Ikhlasul Amal
A blog is a great place for displaying text but sometimes you would like to illustrate a little code and cutting and pasting it will just leave a mess. The problem is of course that a lot of the special characters (such as < and >) are removed or misinterpreted [...]
A human readable sitemap
In an earlier post I touched on the sitemap.xml file, and how it is used by search engines to quickly detect any changes made to your website and to make sure that they do not miss a page when indexing your site.
An older but also tried approach is the human readable sitemap. Not only [...]
Plugin: Google XML Sitemaps
In the good old days, web spiders employed by the search engines has to earn their keep. They had to crawl each page to find links to other pages and so slowly discover the world wide web. Its a good way of finding every page, but it is also very time consuming.
Chewing on their [...]
Self Analysis with Google Analytics
There are many different ways to measure your blogs visitors. It is not only good for your ego but can also a be an indication of whether its time to upgrade your hosting or a measure of how to predict your advertising income.
One of the more traditional approaches is to run a nightly [...]
Setting your Blog on Fire with Feedburner
The first stones are in place, you are no longer looking at the default blue wordpress theme and your first blog entries shine on the front page. Or maybe you decided to skip of all this and got right to this point. Its a free world.
Now you need to promote your blog, and one [...]
What is in a domain name?
After I had figured out what I wanted to blog about — blogging tools and technologies — I needed to find a name for my blog.
So I came up with a name I really liked, and found out that the matching “.com” domain name was already registered.
I made a little list, and tried [...]
