In this article I aim to show you how I backup my WordPress sites on my Digital Ocean droplet. The same method should apply to many if not all VPS services.

Digital Ocean backup feature

Perhaps one of the reasons Digital Ocean is currently such a popular VPS provider is its low-price affordable plans. Its $5/month plan makes perfect sense for small, low-traffic sites, especially if you bundle together a bunch of them on the same server.

Digital Ocean lets you take full weekly backups of your server for a modest price:

These are backups of the entire system that you can later restore.

You can also take snapshots of your VPS, but you must first shutdown your server. This makes sense if you have setup a large system with replication and load balancers, but not as much in the low-cost setup where one small droplet serves a number of sites. Additionally, weekly backups seems to me as not enough granularity. Especially if your sites include e-shops or other sites where users frequently perform transactions, you’ll want to have frequent backups, perhaps daily.

Do it yourself

Fortunately, with only a few lines of code, you can roll your own backup system that will perform a full WordPress database backup in any way you like. Of course you can read on even if you have your server on another VPS service, or if you want to backup anything that uses a MySQL database, not just a WordPress site.

I opted not to go for any online storage solutions. The method I present here is what I chose to do based on my requirement of a low cost, fully custom solution that I can tweak any way I like. It requires a second machine where the backups will be kept. This machine lives at home and can be any old piece of hardware that’s lying around as long as it can connect to the internet.

The plan is to have the backup machine tell the VPS at regular intervals to take backups of the WordPress databases, then download these backups and store them by date/time. We will achieve this using cron, mysqldump, and rcp.

Set up passwordless remote access

This is the first thing you need to do. I will not go into detail because there’s a ton of articles on this (and really, you should have already done this). Here’s the official guide. The article says it’s for Ubuntu servers but the same process applies to Debian and friends.

Long story short:

1. Go to your local machine, and do

   ssh-keygen

   to generate a key pair if you don’t already have one.

2. Copy your public key to the droplet with

   ssh-copy-id user@droplet

   replacing user and droplet as needed.

Set up a “backup” user

Not strictly necessary, but for extra security it would be a good idea to not use the master database password that WordPress uses to create your WordPress backups. You can setup a read-only user on your MySQL database that has read access only to the databases you want to backup. Here’s an example of what to type into the MySQL command line interface. (You can also do this via phpmysql if you have it installed and configured.)

First, connect to your droplet with ssh

ssh user@droplet

where user is your actual user name and droplet is your server’s IP address.

Make sure the MySQL CLI is installed. We’ll also need mysqldump which is included in the same package. So, do this:

sudo apt-get install mysql-client

Once the package is installed, fire up the CLI with:

mysql -u root -p

On the next line you will be asked for the MySQL root password. (This is done because it’s not secure to type passwords on the shell’s command line.)

Once you’re in, you should be seeing the mysql> prompt. First, create a user and give it a password. I called my backup user wp_bu:

CREATE USER 'wp_bu'@'localhost' IDENTIFIED BY 'PASSWORD';

Pro tip: For maximum security, don’t use ‘PASSWORD’ as your password. This database user will have read access to all the data on all your websites. Choose a strong password, like you did with the root DB password and WordPress passwords.

Then you will need to choose which databases the user can read. If you’re not sure which databases you have, type this:

show databases;

Ignore the mysql database and other metadata such as information_schema or performace_schema. The other databases listed should correspond to all your sites. Let’s say you want to be able to backup the database with the name wordpress_db. Type this in:

GRANT SELECT, LOCK TABLES ON wordpress_db.* TO 'wp_bu'@'localhost';

Select and lock tables are the minimum access rights that you need to dump a database to disk. Repeat this line for every database you need to backup. When finished exit the CLI:

exit

The cron job

We’re now ready to setup a cron that will create the backups and copy them somewhere safe, hopefully. I assume you have a low-power machine somewhere in your house that you already leave on 24/7. This would typically be your torrent box, media center, bitcoin wallet, NAS, git upstream repo, trac server, etc. It will now also be the backup server for your sites.

Log in to your backup machine and do a crontab -e to edit your crontab. We’ll need to do three things:

1. Create the backup
2. Copy the backup
3. Delete the backup

Here’s an example of how to do this every day at 3 a.m. for a database named wordpress_db:

Create the DB backup

This is the most important part of our custom backup solution:

0 3 * * * ssh -t user@droplet "nice -n 19 mysqldump -u wp_bu -pPASSWORD wordpress_db |gzip -9 >/home/user/backups/wordpress_db-`date --rfc-3339=date`.sql.gz"

This crontab entry says that at 3 a.m. every day, your local machine is to use ssh to execute the command in double quotes as the user “user” on your server, where “droplet” is your server’s IP. The command itself uses mysqldump to connect to the local database with the wp_bu DB user we created earlier, and dumps the wordpress_db database to a gzipped file. The file name contains given the current date. This whole process is run with the nice -n 19 prefix, to make sure that the process is not given priority over our webserver.

Create the files backup

I’ll allow two minutes which should be plenty of time for my database to be dumped to file, but if you have a really large database, check to make sure

Now we want to also take a snapshot of the file contents of the website. Assuming that your WordPress install is at /var/www/wordpress:

2 3 * * * ssh -t user@droplet "nice -n 19 zip -9r backups/wp-`/bin/date --rfc-3339=date`.zip /var/www/wordpress/wp-content/* "

Copy the two backup files

After a few more minutes, I’ll use rcp to pull the files from the server.

10 3 * * * rcp user@droplet:/home/user/backups/* /path/to/droplet/backups/

This cron entry will again use the passwordless access that we have setup to copy the file to your local path.

Deleting the backup

It is a very very bad idea to keep backups on the same server, not only because you’re filling up your precious VPS space, but also for security reasons. Let’s wait another minute or so for rcp to finish (your mileage may vary), and then we’ll shred the files:

20 3 * * * ssh -t user@droplet "shred -u /home/user/backups/*"

This last cron entry will keep our backups directory clean on the server. You could just use rm to delete the files, but that’s way too non-paranoid for my tastes. (Also, those are rented SSDs, so no need to worry too much about wearing them out.)

Recovering

If something goes horribly wrong, you can import the latest .sql.gz file using phpmyadmin or simply using the mysql CLI. And just unzip the .zip file into a new wp-content dir. Make sure the files are owned by www-data, or whatever your server runs as, and you should be good to go.

In fact I recommend that you test restoring the backup on a local machine before something goes wrong. Really. Go test it now!

Lean backups

You’ll notice that these backups are not incremental. Assuming you’re using this backup method on small sites, the backup files shouldn’t get too large. But in any case you might want to make sure that your WordPress databases are not full of useless stuff. There are plugins out there that help you clean up databases from old edit revisions which take up space, as well as other useless data. I use wp-optimize every now and then. This also helps save space on your VPS.

Shameless referral link plug

If by any chance this article has convinced you to sign up to Digital Ocean (and why not, it’s a great service), please use my referral link https://m.do.co/c/44d4d2184573. You’ll instantly get $10 credit and if you keep using it I might get something out of it too. Thanks!