One of the best backup plugins for WordPress is Updraft Plus. I use it on many sites and I find it reliable at backing up and crucially the restoration process is pain-free. In addition, the free version lets you send the backups off-site – e.g. to Dropbox or Google Drive or another server through SFTP.
However, if you have a large affiliate store then Updraft Plus simply isn’t good enough. The technique it uses to perform the backup is running SELECT statements against your database and sending the output to a file. That’s a really slow way of doing things.
To make matters worse, if you have a huge affiliate store you will have hundreds of thousands of product images in your wp-content/uploads folder. Backing up these images through a PHP-based WordPress plugin is always going to be slow. On top of that, Updraft Plus doesn’t provide an incremental backup option – instead you have to backup all of your files every time even though you already have backups of them in an existing backup file.
The steps below are not restricted to WordPress websites – they can be used to backup any website that uses linux and MySQL.
How to back up your WordPress database without any downtime
For comparison purposes, if I run Updraft Plus just to backup the database of an affiliate store with 140,000 products it will take about 45 minutes to complete. The wp_postmeta table you have will be a decent size – about 4 million rows or so for 140,000 products, depending on how many attributes you have per product. Throughout this process, the wp_postmeta table is locked – so nobody can access your site (unless you have Cloudflare in front of it, but that’s another story).
If I run a mysqldump command instead, the backup of the entire database completes in under 5 minutes. If I add the –single-transaction parameter, other queries are not locked out either so users and admin can continue using your site as normal.
To run mysqldump, you will need to be using your own server – e.g. a VPS from Digital Ocean. If you’re on shared hosting or WordPress.com then you will have to stick with one of the plugins.
Here’s the command to do this – use Putty or SSH to log into your server and run this command to test how well your website responds whilst the database is being backed up. You can also marvel at the speed of this database backup compared to any WordPress backup plugin.
Note the use of the –single-transaction command. Provided you are using InnoDB on your tables, this means your backup will not lock the tables and your users can continue using your website.
mysqldump --single-transaction -u [username] -p[password] [databasename] > [filename.sql]
or to automatically add todays date and time:
mysqldump --single-transaction -u [username] -p[password] pgo > /var/remotebackups/yourdbname_$(date +\%Y\%m\%d\%H\%M\%S).sql
TIP: Leave the password out of the command line and it will ask you for a password. Later in this guide, when we schedule this, we’ll add the password directly into the crontab since only root can access that anyway.
Back up directly to Amazon S3
If you have this large a server, chances are you don’t have enough space on your server to save the backup file. If your site is using 100GB then you’ll need a similar amount (maybe 50GB) to store your backups. The answer is to mount a folder on Amazon S3 – it will appear to your server like you have a folder (e.g. /var/remotebackups) but any files written there will actually be written directly to Amazon S3 storage. Their pricing is decent – at the time of writing, 100GB of storage will cost you about $3 per month.
Run these commands to set up RIOFS which we’ll use to mount our bucket. Before starting, you should have an Amazon S3 account, a bucket created and you should have your ACCESS KEY and SECRET KEY from the AWS console.
sudo apt-get install build-essential gcc make automake autoconf libtool pkg-config intltool libglib2.0-dev libfuse-dev libxml2-dev libevent-dev libssl-dev wget https://github.com/skoobe/riofs/archive/master.zip unzip master.zip cd riofs-master ./autogen.sh ./configure make sudo make install
Edit the /usr/local/etc/riofs/riofs.conf.xml file (I use vim). Inside this file you will see ###AWS_ACCESS_KEY## and ###AWS_SECRET_ACCESS_KEY### – replace these pieces of text with your own access key and secret key from the AWS console. You’ll find them inside a comment – remove the comment markers too so the config is available to riofs.
To create access keys, visit the security credentials page in AWS. Only root will have access to this file, so no one else can see your secret key.
Now we’re ready to mount – note: if you wish, you can also use Amazon S3 for your wp-uploads folder.
The commands below will create a folder at /var/remotebackups and then mount that folder with your S3 bucket. You’ll need your bucket name. I named mine backups.affiliatewebdesigners.com.
mkdir /var/remotebackups riofs -c /usr/local/etc/riofs/riofs.conf.xml backups.affiliatewebdesigners.com /var/remotebackups
Next, test you are connected properly – create a file in your new S3 mount using:
Visit your bucket in the AWS console and you’ll see this file exists.
To make sure your S3 folder is mounted when your server reboots, edit /etc/rc.local and add the riofs command you used above before the exit 0 line in this file.
Set up daily wordpress database backups using CRON
We will run database backups using mysqldump. With InnoDB tables, it’s easy to run backups without locking tables. It’s safe to add your database password to the cron job since only root can access your crons. If someone has root access, they would already have access to your database passwords because they could read wp-config.php.
In this guide, I’m backing up the database to /var/www/yoursite/filebackups folder – make sure this folder exists and that ONLY ROOT has access to it. You don’t want it visible to the world.
I’m not backing up directly to S3 because you could have a network failure for some reason and you never want your database backup to fail. Instead, I backup to the root-only filebackups folder in your web dir and then we use Rsync and Riofs to copy that data to Amazon S3.
mkdir /var/www/html/yourfolder/filebackups chmod 700 /var/www/html/yourfolder/filebackups
Create a cron job by using the following command:
In the file, at the bottom, enter the following line: (Replace username, password, database and yoursite with your own values – note: no space between -p and your db password)
5 1 * * * mysqldump --single-transaction -u [username] -p[password] [databasename] > /var/www/yoursitefolder/filebackups/yoursite_$(date +\%Y\%m\%d\%H\%M\%S).sql
(the 5 is for 5 minutes past, the 1 is 1AM, so this runs daily at 1:05AM local server time)
Use Rsync to backup wp-content/uploads to Amazon S3
Rsync is a fantastic tool which synchronises folders (it’s what underpins Dropbox). You can use it one-way, so it doesn’t delete files from the destination folder. Using Rsync means you’ll have a copy of all of your files on S3. It’s important to understand how it works and how it differs to normal backups so you use it properly:
- With normal backups, you have multiple copies of your website at various points in time – with Rsync on its own, you have a copy of your website as it is right now
- If you perform an update on a file – e.g. a change to wp-config.php – with Rsycn, this change will be propagated to the other server immediately
- Rsync isn’t storing historical edits (versions)
- You can get around all of this by combining Rsync + traditional file zip backups
On a large affiliate store, the primary concern is getting backups of your images to an off-site location as well as backups of all your other files.
Using Rsync, we can set up one-way synchronisation – that means new files and updates to files are propagated but not deletes. That works well for images which is the main thing we’re trying to capture (we actually mirror EVERYTHING which makes a restore quicker)
We won’t have historic *versions* of images, but we’ll have all images every created.
The great thing about Rsync is that it’s only checking against changes – so it’s not backing up every image every time – they only get backed up once and then permanently exist on S3. That’s great news for backup speed and server performance.
We’ll run this command every night using CRON.
rsync -aP /var/www/html/yoursitefolder/ /var/remotebackups/yoursitefolder-mirror
To make it run nightly with CRON run crontab -e and add the following line:
*/5 * * * * flock -n /var/rsyncrunning -c "rsync -aP /var/www/html/yoursitefolder/ /var/remotebackups/yoursitefolder-mirror"
Use Zip to maintain ‘versions’ of all the other files
Using the above techniques, you will have historic daily versions of your database and the current version of your web folder copied to S3. For complete peace of mind, you also need to generate copies of what your files look like on a daily basis. Because we’re talking here about massive sites with massive amounts of images, the zip commands below exclude your uploads folder.
Since you already have Rsync copying all your /var/www/html files over to S3, all you need to do is set up a regular zip command to zip the contents of of your web folder into a subdirectory of the html website folder, except exclude the uploads folder (with the large volume of images we’re synching directly using Rsync) and exclude the backups folder itself too.
Note: You might wonder, looking at the command below, why I don’t just backup directly to /var/remotebackup – it’s to do with the inability of RIOFS (the S3 mounting software) to perform certain kinds of edits on files. That basically prevents a zip file from being built.
Instead, for these files you need to back them up to your server and then rely on the Rsync cron job to copy them across to S3. This is ok because we’re skipping the massive uploads folder – the rest of the files will be miniscule in comparison.
Add the zip backup to a cron job. Run crontab -e to open your cron editor.
30 1 * * * zip -r --exclude=/wp-content/uploads/\* -exclude=/filebackups/\*.zip /var/www/html/yoursitefolder/filebackups/websitefiles_$(date +\%Y\%m\%d\%H\%M\%S).zip /var/www/html/yourfolder/
Restoring from these backups
You will first need to create a database and database user first if you are restoring to a fresh server. You can do so using the following on your mysql server:
create database sitename; grant all privileges on sitename.* to "siteusername"@"localhost" identified by "sitedbpassword";
To complete a restoration, take the following steps:
- Extract the contents of one of the wpcontentfiles*.zip files to your web folder
unzip file.zip -d /var/www/html/
- Restore one of the SQL backups:
mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
- Run rsync in the opposite direction (by reversing the folder names) from Amazon S3 (mount your bucket first using steps above if it’s a fresh server):
rsync -aP /var/remotebackups/yoursitefolder-mirror/ /var/www/html/yoursitefolder
We have created complete daily backups of your database and all your files except your uploads folder which is instead incrementally mirrored. These files are copied and mirrored to Amazon S3.
- Using cron to run non-locking mysqldump every night
- Using cron to zip all files, except for the uploads folder, every night
- Using riofs to mount a remote Amazon S3 bucket
- Using rsync to mirror all files to S3 including sql dumps, zips of folders and the uploads folder
If you have any questions, ask away or you think I might have missed something, let me know in the comments below.
He is always on at least one of his computers when he's awake, so get in touch and he'll get right back to you.
Latest posts by Dave Hilditch (see all)
- My WordPress performance plugins and server stack have moved - July 31, 2016
- Price Comparison Pro 1.2 Released - July 5, 2016
- How to run backups on huge WordPress websites without your website being brought offline - February 4, 2016