Automate Remote Backup of WordPress Database

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.)

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:

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:

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:

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.