I run this blog on a VPS from Tektonics. It’s usually more available to the Internet than the server in the basement.
The only concern is that it’s not my server and backups are important. I had been using Tamba2’s script for backing up on a cron job but forgot that the script only got tables that you told it to. Donncha’s script gets all the tables, but I like getting one file for the data. Also I want to be able to retrieve the file backup without using a password onto the system but make sure that the file can’t be used by other people. For me that means putting it on the web server but encrypting the backup so that only my private key can decrypt the data.
The idea is to
- Backup all the tables in the WordPress database
- E-mail the database backup to an e-mail address of my choosing
- Create a file backup
- Encrypt the file backup and put it somewhere it can be downloaded
- Retrieve the backup and store it somewhere else
The GPG file encryption is over the top; I’m practicing for solving a problem at work where scripts and passwords are a huge no-no.
GPG Key
For storing the backup I am using the directory that Skippy’s backup plugin created. It could be any directory as long as you can write to it and your webserver can read the directory and file stored in there.
First create a gpg private/public key pair. Using the command line gpg –gen-key:
ubuntu@localhost:~> gpg –gen-key
gpg (GnuPG) 1.4.6; Copyright (C) 2006 Free Software Foundation, Inc.
This program comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to redistribute it
under certain conditions. See the file COPYING for details.
Please select what kind of key you want:
(1) DSA and Elgamal (default)
(2) DSA (sign only)
(5) RSA (sign only)
Your selection? 1
DSA keypair will have 1024 bits.
ELG-E keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is 2048 bits
Please specify how long the key should be valid.
0 = key does not expire
<n> = key expires in n days
<n>w = key expires in n weeks
<n>m = key expires in n months
<n>y = key expires in n years
Key is valid for? (0) 2y
Key expires at Fri 26 Dec 2008 10:17:20 AM EST
Is this correct? (y/N) Y
You need a user ID to identify your key; the software constructs the user ID
from the Real Name, Comment and Email Address in this form:
“Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>”
Real name: Jan Dembowski
Email address: gpg@someaddress.com
Comment: WordPress backup key
You selected this USER-ID:
“Jan Dembowski (WordPress backup key) <gpg@someaddress.com>”
Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.
gpg: gpg-agent is not available in this session
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
++++++++++….++++++++++++++++++++.++++++++++..+++++++++++++++++++++++++..+++++++
+++++++++++++++++++++++.+++++…+++++.+++++.++++++++++.+++++++++++++++++++++++++>
++++++++++>+++++…>+++++………………………………………………..
………………………….+++++^^^
gpg: key 8C62B03D marked as ultimately trusted
public and secret key created and signed.
gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0 valid: 2 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 2u
gpg: next trustdb check due at 2008-12-26
pub 1024D/8C62B03D 2006-12-27 [expires: 2008-12-26]
Key fingerprint = 2BC3 7CB7 27D0 FD07 5F03 45A1 4BDB C4C4 8C62 B03D
uid Jan Dembowski (WordPress backup key) <gpg@someaddress.com>
sub 2048g/1754855B 2006-12-27 [expires: 2008-12-26]
ubuntu@localhost:~>
Then just run gpg –export –armor gpg@someaddress.com
This needs to be run the server you just generated the key with. That output needs to be installed on the wordpress server in the account that you are running the backup script as, just run gpg —import, copy the public key into the window and press CTRL-D. Use gpg —edit-key gpg@someaddress.com to assign the public key ultimate trust.
Backup Script on WordPress host
Here’s what my modified script looks like. The shell script mostly from Tamba2’s.
#!/bin/sh
#The usual variables for your WP database
DBNAME=wordpress_db_name
DBPASS=wordpress_db_password
DBUSER=wordpress_db_username
# Get the table names
DBTABLES=`echo “SHOW TABLES” | mysql -u $DBUSER -p$DBPASS -D $DBNAME | grep -v Tables_in_`
DATE=`date +%Y%m%d`
#Keep the ” around your address
EMAIL=”name@someaddress.com”
# Directories
WORKDIR=/tmp
WPDIR=/var/www/wordpress
WPBACKUP=$WPDIR/wp-content/backup-3c42f
GPGKEY=gpg@someaddress.com
cd $WORKDIR
# Dump all the contents of the WordPress database
mysqldump –opt -u $DBUSER -p$DBPASS
$DBNAME $DBTABLES >backup.sql
gzip backup.sql
mv backup.sql.gz $DBNAME-backup-$DATE.sql.gz
# E-mail the database
echo ‘Your Blog: Your mySQL Backup is attached’ |
mutt -a $DBNAME-backup-$DATE.sql.gz $EMAIL -s “MySQL Backup”
# Now for the file backup
# Make the backup and exclude the old backup file
tar czf wordpress-files.tar.gz
–exclude=wordpress-files.tar.gz.gpg
$DBNAME-backup-$DATE.sql.gz $WPDIR
# Encrypt the file with the gpg public key you are using
gpg -e -r $GPGKEY –yes wordpress-files.tar.gz
# Delete the old copy and move the encrypted one
rm wordpress-files.tar.gz
mv wordpress-files.tar.gz.gpg $WPBACKUP
Restore Script on Backup Server
On your backup server as any user run
wget http://yourblog.com/wp-content/backup-3c42f/wordpress-files.tar.gz.gpg
or
curl -O http://yourblog.com/wp-content/backup-3c42f/wordpress-files.tar.gpg
If you want to use https then
wget –no-check-certificate https://yourblog.com/wp-content/backup-3c42f/wordpress-files.tar.gpg
or
curl –insecure -O https://yourblog.com/wp-content/backup-3c42f/wordpress-files.tar.gpg
Automated once a day in this script
#!/bin/sh
DATE=`date +%Y%m%d`
URL=https://yourblog.com/wp-content-/backup-3c42f/wordpress-files.tar.gz.gpg
cd /where/you/put/the/backup
wget –no-check-certificate $URL >/dev/null 2>&1 &&
mv wordpress-files.tar.gz.gpg wordpress-files-$DATE.tar.gz.gpg
# Delete backups more than 30 days old
find . -mtime +30 -name “wordpress-files*.tar.gz.gpg” -print | xargs rm -f
When the “Bad Thing” Happens
Restoring the backup is not really a big deal.
On your backup server copy the wordpress-files-date.tar.gz.gpg to another directory and run the following commands in that directory:
gpg wordpress-files-date.tar.gz.gpg
This will prompt you for the pass phrase of the private key. When that is done you will have created a new file without the .gpg extension.
tar xzf wordpress-files-date.tar.gz
This will extract the files and directories for you. In the directory is a file called wordpress-backup-date.sql.gz
Move the files to the server you want to restore the files and directories to. On the same server create your empty wordpress database and make sure you assign the correct rights.
Once the files are in place, try running
zcat wordpress-backup-date.sql.gz | mysql -D wordpressdb -u wordpressdbuser -p
You will need to substitute the wordpressdb and wordpressdbuser with the correct values. The -p argument will prompt you for the database user password.
