Backup WordPress Files and Database

I’ve been meaning to create a backup of my WordPress files and databases on my VPS (ChunkHost). After a minimal amount of digging (on WordPress’s Codex site), I wrote this script:

#!/bin/bash

WPBAK=/var/backups/wordpress
tar -cvJf $WPBAK/$(date +%F)_files.tar.xz /var/www || exit 1
mysqldump --all-databases --verbose | xz -c > $WPBAK/$(date +%F)_databases.sql.xz || exit 2

Just change the “$WPBAK” variable to where on the server you want to store the backups. Also, you’ll need to create the ~/.my.cnf file (I did it for the root user):

[mysqldump]
user=root
password=secret

You’ll need to change user to the appropriate MySQL user, and the password accordingly. Be sure to set .my.cnf to have the permissions 600 (“chmod 600 ~/.my.cnf”), that way the script doesn’t need to contain the MySQL user password in order to make the dump.

I put all of this in /etc/cron.daily/wordpress, and made it executable (“chmod +x /etc/cron.daily/wordpress”). This way my system will back up automatically on a daily basis.

The next step is to update my rsnapshot configuration so it will store the backup on my Network Attached Storage (NAS). First, I had to copy the SSH public key for my NAS admin user to my VPS, and add it to the user doing the work. Then, I added a single line (port number changed to protect the innocent):

backup  root@eldon.me:/var/backups/wordpress    eldon.me/       ssh_args=-p 4321

I’m waiting for another rsnapshot backup process to start naturally, rather than kicking off a manual run. I’m curious to see if this works as well as I thought.

The next step is to write a cron job that will remove old backups. This is simple enough (put in /etc/cron.daily/wordpress_backup-cleanup, and make executable, as above):

#!/bin/bash
find /var/backups/wordpress/ -mtime +7 -exec rm {} \;

This will delete backup files greater than seven days old. This will hopefully keep my li’l VPS’s disk from filling up. And that’s it!

Migrate install to new VPS…

I have a ChunkHost Virtual Private Server (or VPS), and I ran into a predicament. I will admit my Debian knowledge is ever increasing, and now that sid has “thawed” its package selection is much more mercurial. Currently, this poses a problem for my web sites hosted on the VPS: amberandtrey.us, and this site (eldon.me). At the moment, Sid is going through a particular transition: apache-2.2 to apache-2.4. Thus many of the packages I need are in flux, and on any given day it could be horribly broken. I decided to switch back to Wheezy, but there’s no real way to downgrade. I’d have to reinstall.

ChunkHost gives a really (insidiously) easy way to fire up a new VPS (called a “chunk”). Within minutes I had my betachunk. My first chunk was called “alphabouncer” by me because I originally intended it to be an IRC bouncer (so if someone were to attack my IP address they’d be attacking my chunk, not my home IP address), but I digress. It wasn’t a simple matter of just copying files; I had to back up the MySQL databases, back up the WordPress files, copy my WeeChat configuration, restore it all, set the DNS server to point both domains at the new IP address, and coax it into working.

NOTE: All of the commands were performed within my backup directory, /root/backup/2013-06-15, unless otherwise noted.

  1. The first thing I did was to backup the databases. Since both websites use their own databases within the confines of the single MySQL instance, I needed to ensure the mysqldump command captured all databases. To conserve space, I ran the results through bzip2. The command I used was this:
    mysqldump -u root -p --all-databases --events | bzip2 -c - > websites_$(date +%F).sql.bz2

    All events may not have been necessary (and possibly problematic if the database is in active use at the time of the dump), but mysqldump will issue a warning if that flag is not passed. These are small enough databases that grabbing the events should not pose a problem.

  2. The next task was to back up the WordPress files. These lived in two places: /usr/share/wordpress for amberandtrey.us, and /var/www/eldon.me. I wanted to move amberandtrey.us to /var/www/amberandtrey.us to be more orthogonal with my directory structure, but more on that below. I also grabbed /etc and /root in case I needed them (I definitely did, mainly for SSL certificates, apache2 and WordPress server files). In order to back these up, I just used the standard tar commands:
    tar -cvjf amberandtrey.us_$(date +%F).tar.bz2 /usr/share/wordpress tar -cvjf eldon.me_$(date +%F).tar.bz2 /var/www/eldon.me/ tar -cvjf etc_$(date +%F).tar.bz2 /etc tar -cvjf alphabouncer-root_$(date +%F).tar.bz2 --exclude=backup --exclude=downloads --exclude=.cpan /root

    I used the excludes because the destination is in /root/backup, and I didn’t need to have all the cruft lying around on the new server.

  3. To copy my Weechat configuration, I created a matching username on the new VPS (betachunk). Rather than tar up my ~/.weechat directory, I used Midnight Commander (or “mc” for short) on alphabouncer to select the directory and copy it over. Since the two VPSes are in the same data center, I thought the transfer would occur quickly. It did, but some of the logs were still outrageously large, so it took a few minutes to complete.
  4. Restoring it all was a little tricky, compounded by the fact that I wanted to make minor structural changes to some of the WordPress directory layout. I copied the contents of alphabouncer:/root/backup/2013-06-15/* to betachunk:/root/backup/2013-06-15/. I can’t remember exactly, but I think I used mc to copy the entire backup directory over. I first changed directory (“cd”) into the backup directory, and untarred the etc backup file to the current directory (a nonstandard location) because I was going to copy the files one at a time using the command:
    tar -xvf etc_2013-06-15.tar.bz2

    I first copied over all of my SSL files for both sites, and the StartCom (my cert provider) files as well. This included everything for /etc/ssl/certs and /etc/ssl/private.

  5. Next I unpacked amberandtrey.us, created the directory where I wanted the files to ultimately live, and move the resulting files into that directory:
    tar -xvf amberandtrey.us_2013-06-15.tar.bz2 mkdir -p /var/www/amberandtrey.us mv usr/share/wordpress/* /var/www/amberandtrey.us

    I then repeated the process with eldon.me:

    
    tar -xvf eldon.me_2013-06-15.tar.bz2
    mv var/www/eldon.me /var/www/
    
    
  6. The next step was the tricky part. I had to restore all databases, but the database server MySQL hadn’t been installed yet (installing the wordpress package did NOT resolve this dependency!):
    aptitude install mysql-server

    Installing the mysql-server package prompted me to set the root password, but I still needed to copy the /etc/mysql/debian.cnf file from the previous installation. This allowed the MySQL daemon to start cleanly.

  7. The final step in transferring the WordPress sites to betachunk was to set up the /etc/wordpress/config-*.php files. Copying them from the restored /etc backup location, and we were almost in business.
  8. Since I was using SNI (Server Name Indication, see my KeePassX database merge topic for a brief discussion of this), I’d need to tie the new IP address to both domains. ChunkHost has a neat little DNS tool that can be used to do this. Since at my domain registrar (GoDaddy, I use them because they were convenient when I bought my first domain, and inertia keeps me with them) already has the ChunkHost nameservers for both domains, I just needed to update ChunkHost’s DNS server with the new IP address, and I was running!

Well, I thought I was running. amberandtrey.us worked no problem, but eldon.me had issues. Apparently I have the error log for eldon.me in a weird file (it’s not /var/log/eldon.me-error.log, like it probably should be). This has now been corrected. Anyway, I had to run “ls -al /var/log/apache2/” in a watch command to see what file was changing when I tried accessing eldon.me (and was getting a 500 Internal Server Error):

watch -n 1 ls -al /var/log/apache2

I saw the nonstandard log file (eldon.me-ssh-error.log) changed size and modification date/time, so I investigated that file. I found these errors:

[Sat Jun 15 03:33:14 2013] [error] [client 216.207.245.1] PHP Warning: require_once(/etc/wordpress/config-eldon.me.php): failed to open stream: Permission denied in /var/www/eldon.me/wp-config.php on line 19, referer: https://eldon.me/wp-admin/post-new.php [Sat Jun 15 03:33:14 2013] [error] [client 216.207.245.1] PHP Fatal error: require_once(): Failed opening required '/etc/wordpress/config-eldon.me.php' (include_path='.:/usr/share/php:/usr/share/pear') in /var/www/eldon.me/wp-config.php on line 19, referer: https://eldon.me/wp-admin/post-new.php

That led me to believe that /etc/wordpress/config-eldon.me.php did not have the correct permissions. I changed the ownership to www-data, and set the permissions to match the config-amberandtrey.us.php. Now eldon.me works!

Whew, what a long post! It took me longer to write than I would have expected because I ran into the problems on eldon.me (the server providing the WordPress post form I’m using). We’ll see if it lets me post it!

WordPress install on Debian Sid (post-Wheezy)

For my first post, I will discuss how I installed this website on my Debian Sid installation on my EDIS Virtual Private Server (VPS). I first started with the EDIS VRS STARTER, which includes 512MB RAM, with a 10GB disk, 1Gbps network bandwidth, and 2TB total bandwidth allowed per period. Not much to work with, but it’s only ~$7 per month! The nice thing is, the Wheezy image I started with only takes up 200MB of disk space, so there’s plenty to work with.

Here’s the procedure I followed:

  1. First thing was to install my bare essentials. This includes vim and tmux. I installed them with the following command, as root:
    aptitude install vim tmux
  2. Now that vim was installed, I could remove nano and edit /etc/apt/sources.list properly:
    
    # aptitude purge nano # DIE EVIL NANO!
    # vim /etc/apt/sources.list
    
    

    The /etc/apt/sources.list file ended up like this:

    deb http://debian.uchicago.edu/debian sid main contrib
    
    

    Since this server is in Chicago, IL, USA, I changed the Debian repository server from ftp.at.debian.org (EDIS is based in Austria). The “contrib” parameter was used because several key MySQL components (necessary for WordPress) are not available in the “main” repository because of licensing issues. With that I added the following alias to root’s bash run control file (~/.bashrc):

    
    alias upgrade='aptitude update && aptitude full-upgrade'
    
    

    I do the above on all of the Debian servers and workstations I manage, to make it a simple matter to update the system to the latest available packages. Sourcing the .bashrc file source .bashrc(or what I really use) . .bashrc loaded the new alias. Executing the “upgrade” alias proceeded to update my Wheezy install to Sid, and took about twenty minutes or so. Note that lsb_release -a still returns Wheezy; I’ll have to investigate that when I have time.

  3. Now that my machine had been updated, I rebooted it to load the new kernel (with the command reboot. Next, I installed wordpress and mysql. I note that mysql-server is *not* a dependency of wordpress (it merely suggests mysql-server); I discovered this when I tried to start WordPress for the first time and noticed mysql-server was not installed (this led me to adding “contrib” to sources.list above). The command below installed many libraries and helper packages, including apache2 and php5 related packages:

    
    # aptitude install wordpress mysql-server libssh2-php
    
    

    I installed libssh2-php since I wanted to use SSH keys for WordPress to apply updates, plugins, and themes (rather than use FTP or FTPS [I’ve always found installing and securing FTP repositories to be a major pain]). I was then ready to set up apache.

  4. Setting up apache was as simple as creating the /etc/apache2/sites-available/eldon.me:
    
    
    DocumentRoot /var/www/eldon.me
    ServerName eldon.me
    ServerAlias www.eldon.me
    ServerAdmin trey@blancher.net
    ErrorLog /var/log/apache2/wp-error.log
    TransferLog /var/log/apache2/wp-access.log
    RedirectPermanent / https://eldon.me
    
    
    
    SSLEngine on
    SSLCertificateFile /etc/ssl/certs/eldon.me.pem
    SSLCertificateKeyFile /etc/ssl/private/eldon.me.crt
    DocumentRoot /var/www/eldon.me
    Customlog /var/log/apache2/eldon.me-ssh-access.log combined
    ErrorLog /var/log/apache2/eldon.me-ssh-error.log
    HostnameLookups On
    
    
    
            Options FollowSymLinks            
            AllowOverride Limit Options FileInfo
            DirectoryIndex index.php
                                                
    
    

    I obtained my SSL certificates from StartSSL, based in Israel. The main reason I use them is they offer level 1 certificates for personal use completely free/gratis! The only downside is that many older, out of date browsers and smartphones don’t recognize their certificate authority credentials, which means many browsers issue an SSL warning (as if the site used self-signed certificates). If that becomes a major problem, I may switch.

    The next step was to enable the site:

    
    # a2ensite eldon.me
    
    
  5. The next step is to set up the WordPress MySQL user and database (adapted from here). In Debian, when mysql-server is installed apt/dpkg directs you to set root’s password for the MySQL server. The first command below will prompt for that password:
    
    # mysql -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 308
    Server version: 5.5.29-1 (Debian)
    
    Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> create database eldon;
    Query OK, 1 row affected (0.00 sec)
     
    mysql> grant all privileges on eldon.* to "wp_agent"@"localhost"
        -> identified by "SUPERSECRETPASSWORD";
    Query OK, 0 rows affected (0.00 sec)
      
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> exit
    
    

    Since wordpress installs to /usr/share/wordpress on Debian, I made a symlink, and then restarted apache2:

    
    ln -s /usr/share/wordpress /var/www/eldon.me
    service apache2 restart
    
    

    If I want to add other sites to this VPS, I’ll just need to set up the site as above, and place it in /var/www/. Note thttps://www.startssl.com/hat I’ll have to be careful when setting up SSL. I had originally found that multiple SSL sites on a single IP was impossible. However, when I tried to find the page that explained that, I found this post instead: “Configure Apache to Support Multiple SSL sites on a single IP address”. I could have saved some money, but having multiple VPS services makes sure I have access to one of them should the other go down (I use my other VPS for IRC as well).

  6. Now, I could finally set up WordPress. I navigated here (you’ll see that it has already been set up). I won’t go into setting up WordPress from there, but this is precisely the same as “Run the Install Script”.

And that’s it for now! Hopefully I didn’t miss any steps.