Automate Remote Backup of WordPress Database

8:35 am Linux

See my previous post, Subversion Over SSH on an Unmanaged VPS, to learn how to set up Subversion on Ubuntu (running on a DigitalOcean VPS). In this post, we will learn how to create a script to backup the WordPress database and copy it from the server to our local Windows client. We’ll also look at copying other files on the server to our local client’s hard drive. Finally, we will automate the execution of the backup script to run at regular intervals on the local client.

Install Windows SSH Tools

The backup script will use Unix tools, like ssh (secure shell) and rsync (remote sync), which are not included with Windows. Fortunately, there are free distributions of these tools for Windows. Let’s install them.

Get the ssh and rsync tools:

  1. Download the version of DeltaCopy without the installer (see “Download Links” located top-right).
  2. Unzip the downloaded “DeltaCopyRaw.zip” to “C:\Program Files (x86)\DeltaCopy”.
  3. Add DeltaCopy to the execution path and set the home directory (where we will save the public/private RSA key pair files later):
    1. Open up the “System Properties” dialog by running “Edit system environmental variables” (or “sysdm.cpl”). Click on the Advanced tab. Click on the “Environmental Variables” button near the bottom to launch the “Environmental Variables” dialog.
    2. In the “Environmental Variables” dialog, select “Path” under “System variables” and click the “Edit…” button.
    3. Add “;C:\Program Files (x86)\DeltaCopy” (without the double-quotes) to the end of the existing “Variable value” field. Click Ok to save the change.
    4. Back in the “Environmental Variables” dialog, click “New…” button under “System variables”.
    5. Set “Variable name” to “HOME” and “Variable value” to your home directory like “C:\home\myuser”. Click Ok to save the change.
    6. Click Ok to close the “Environmental Variables” dialog and “Ok” again to close the “System Properties” dialog.

Get the ssh-keygen (secure shell authentication key generation) tool:

  1. Download the free version of cwRsync (click on the Get tab).
  2. Unzip the downloaded “cwRsync_5.5.0_x86_Free.zip” to a temporary directory like “C:/temp/cwRsync”. We will only need to use ssh-keygen once to generate the public/private RSA key pair.
  3. Besides ssh-keygen, cwRsync includes ssh and rsync which we won’t use; cwRsync’s ssh and rsync is not as Windows-compatible as DeltaCopy. For example, cwRsync’s ssh and rsync require that the RSA key pair files stored on Windows have Unix-like 0600 permissions, which then require the chmod tool (ironically included with DeltaCopy, but not cwRsync). DeltaCopy doesn’t have such issues. (Both DeltaCopy and cwRsync are based on a tiny part of Cygwin and DeltaCopy is the most Windows-friendly option of the three.)

Get the scp (secure copy) tool:

  1. Download the “pscp.exe” file from PuTTY.
  2. Move it into the “C:\Program Files (x86)\DeltaCopy” directory.

Create the “.ssh” directory under the home directory and test the environmental variables by running the “Command Prompt” (or “cmd.exe”) and inputting these commands. (Don’t type the comment lines below that start with the # pound character.)

# Test the HOME variable
echo %HOME%
c:\home\myuser

# Create the .ssh directory
mkdir %HOME%\.ssh

# Test the PATH variable; ssh should be found and executed
ssh -p 3333 mynewuser@mydomain.com

Server, Trust Me

To enable the backup script to run without requiring password input from the user, we need to establish trust between the remote server and the local client. To do so, we will create a client public/private RSA key pair and configure the server to trust the client public key. Tools like ssh and rsync can then authenticate against the server using the RSA key pair to avoid requiring the user to input a password.

Open the “Command Prompt” and do the following:

# Go the directory where we unzipped the ssh-keygen tool to
cd /temp/cwRsync/bin

# Generate client RSA key pair (for security, 2048 bits is the new minimum)
ssh-keygen -b 2048

Generating public/private rsa key pair.
# When prompted, select the current directory to write to;
# if you keep the default, it will fail
Enter file in which to save the key (/home/myuser/.ssh/id_rsa): ./id_rsa
# Keep the default; do not input a passphrase
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in ./id_rsa.
Your public key has been saved in ./id_rsa.pub.

# Move client RSA key pair to .ssh directory
move ./id_rsa* /home/myuser/.ssh

# Copy client public key to the server
pscp -P 3333 /home/myuser/.ssh/id_rsa.pub mynewuser@domain.com:/home/mynewuser/

# Secure shell into the server; you will be prompted for password
ssh -p 3333 mynewuser@mydomain.com

# On server, double-check that we are in the home directory
pwd
/home/mynewuser

# Create the .ssh directory
mkdir .ssh

# Create authorized_keys file and append the client public key to it
cat id_rsa.pub >> .ssh/authorized_keys

# Delete the client public key (no longer needed)
rm id_rsa.pub

# Optionally, restrict access to .ssh directory
chmod -R 700 .ssh

# Exit the secure shell
exit

# Secure shell into the server again; you won't be prompted for the password
ssh -p 3333 mynewuser@mydomain.com

On the last secure shell attempt, you should be able to log into the server without having to input a password.

Create and Schedule Backup Script

Create a file “C:\home\myuser\backups\backup_wordpress.bat” and input the following content:

@echo off

REM Display current date and time

date /t
time /t

REM Dump the wordPress database
REM The -v verbose flag is optional

ssh -p 3333 -v mynewuser@mydomain.com "mysqldump -uwordpress -pmypassword wordpress | gzip -c > /tmp/wordpress.sql.gz"

REM Download the database dump file to local directory.
REM Using rsync over ssh to avoid the need for a rsync server on VPS.
REM The %date:~10...% below helps to date-stamp the file,
REM resulting in a filename like 2015.04.23-wordpress_4.4.2.sql.gz.

mkdir \home\myuser\backups\wordpress
cd \home\myuser\backups\wordpress
rsync -vrt --progress -e "ssh -p 3333 -l mynewuser -v" mydomain.com:/tmp/wordpress.sql.gz %date:~10,4%.%date:~4,2%.%date:~7,2%-wordpress_4.4.2.sql.gz

REM Sync all Nginx server block files to local directory
REM Note: Be careful, the --delete flag allows Rsync to delete local files
REM       if they do not exist on the server also!

mkdir \home\myuser\backups\nginx
rsync -vrt --progress --delete -e "ssh -p 3333 -l mynewuser -v" mydomain.com:/etc/nginx/sites-available/* .

REM Rsync may not set local permissions correctly, so we'll fix with DeltaCopy's chmod.
REM Note: chmod fails for files with Windows-style perms already set, but that is ok.

chmod 660 *

You may wish to add additional rsync commands to download the WordPress configuration file (“/var/www/wordpress/wp-config.php”) and pictures uploaded to WordPress (“/var/www/wordpress/wp-content/uploads/*”).

Schedule the script:

  1. Run the “Task Scheduler”.
  2. Click on “Create Basic Task…” on the right sidebar.
  3. Input a name like “Backup WordPress”. Click Next.
  4. Select your schedule. I recommend “Weekly”. Click Next. Select a specific day and time that works for you. Click Next.
  5. Keep Action as “Start a program”. Click Next.
  6. Input “C:\home\myuser\backups\backup_wordpress.bat” into the “Program/script” box.
  7. Input “> C:\home\myuser\backups\backup_log.txt 2>&1” into the “Add arguments (optional)” box. This will redirect any standard or error outputs from the “backup_wordpress.bat” to “backup_log.txt” for review at your convenience.
  8. Click Next and Finish.
  9. To manually test, click on “Task Schedule Library” on the left sidebar, right-click on the “Backup WordPress” task in the top-center panel (if you don’t see it, click on the Refresh action to the right first), and select Run.

Getting PuTTY to Use Private/Public Key Pair

You may notice that the PuTTY pscp tool still requires a password to be inputted. Unfortunately, PuTTY does not use the RSA key format or the %HOME% environmental variable.

If you wish to use the pscp tool in the backup script, we’ll need to convert the RSA private key to the PPK (PuTTY Private Key) format:

  1. Download the “puttygen.exe” file from PuTTY.
  2. Run it.
  3. Go to menu Conversions and select “Import key”. Browse to the client RSA private key at “C:/home/myuser/.ssh/id_rsa”.
  4. Click the “Save private key” button. Answer Yes to the “Are you sure you want to save this key without a passphrase to protect it?” dialog.
  5. Input filename “id_rsa.ppk” and save to the same location as the original RSA key pair files.

When running the pscp tool in the script, use the “-i” option to tell it where to find the PPK file like so:

REM Copy the WordPress config file to local directory

cd \home\myuser\backups\wordpress
pscp -P 3333 -i /home/myuser/.ssh/id_rsa.ppk mynewuser@mydomain.com:/var/www/wordpress/wp-config.php .

Hopefully the above will help you to sleep well, knowing that your WordPress data is safe.

See my followup post, Free SSL Certificate from Let’s Encrypt for Nginx, on how to install a free SSL certificate for HTTPS access and as a result, maybe give your Google ranking a boost.

Leave a Comment

Your comment

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.