Remotely Backup Your WordPress Database and Media with WP Migrate DB Pro CLI
Keeping regular backups of your WordPress installs is arguably one of the most important things you should be doing after you launch a site. In fact, if you search for the term “backup” in the WordPress plugin repository, you’ll find over 700 that claim to be up to the task.
Today we’re going to be setting up a highly reliable remote backup solution using a plugin that isn’t on that list, WP Migrate DB Pro. Are there plugins that could do a better job or are easier to set up? Almost certainly, but few of them offer a centralized remote backup solution and none of them are on my very short list of plugins that I rely on every time I build a website.
Overview
The setup here is pretty simple and assumes that you’ve got a server set up somewhere with a fresh install of WordPress, with WP Migrate DB Pro and the CLI and Media Files addons installed. You’ll want to use a fresh install that you only use to backup other sites with a unique table prefix from any of the sites you intend to backup, and you can even use a local development server like MAMP (Deciding which local development environment to go with? We reviewed the 4 Best Local WordPress Development Environments) so long as you have access to your system’s CRON.
From that starting point we’re going to be writing a bash script that will:
- Create a dump of the current database.
- Run a profile migration via the WP Migrate DB Pro CLI that pulls the database and possibly media files from a remote site.
- Package up the pulled database and media files and move to a backups folder.
- Restore the original database.
- Repeat for each profile.
The Build
1. Set Up Remote Site Profiles
This is the part that you’re probably most familiar with. Just grab the connection information from a site you’d like to start backing up and then create a new profile in WP Migrate DB Pro on your backup site. Use a pull migration and remove the prefilled find and replace rows, since we just want to backup the remote site as-is, not actually migrate the data to this server. Set the “Exclude Post Types” option to exclude revisions and then uncheck “Replace GUIDs” under “Advanced Options”.
If you’re going to backup your remote media as well, you can check the box next to “Media Files” and select the option to “Remove all local media files and download all remote media files.” This will ensure that we copy down all of the media from the site we’re backing up.
Finally, you need to check the box next to “Save Migration Profile” and give your new profile a name – then click “Save Profile.” Make sure you don’t click “Migrate & Save Profile” as we’ll only be using this migration profile in our script which will restore our original database for us.
Repeat this step for as many sites as you want to backup, then click on the “Saved Profiles” link at the top of the page so you can see the list of profiles you’ve created along with their IDs which we’ll use with the CLI Addon.
2. Configure the Backup Script
Grab the script here or copy the code below.
Save it to a safe place on your server as wpmdbpro-cli-remote-backup-script.sh and set the following variables based on your site’s settings:
SITE_PATH #The absolute path to your WordPress install.
BACKUP_DIR #The absolute path to a folder to keep the backups in.
MYSQL_USER #MySQL username from wp-config.php
MYSQL_PASS #MySQL password from wp-config.php
MYSQL_DB #MySQL username from wp-config.php
TABLE_PREFIX #Table prefix from wp-config.php (Should be unique, don't use 'wp_')
Next you’ll want to go to the list of profiles that you’ve already set up in WP Migrate DB Pro and add them to the SITES array of profiles to run through. If your profiles list in wp-admin looks like this:
1 - Backup foo.com
2 - Backup dev.anothersite.com
3 - Backup danger.zone
Then you’d set the SITES array up like this:
SITES[1]=foo_com
SITES[2]=dev_anothersite_com
SITES[3]=danger_zone
Remember that the numbers in the brackets should be the profile ID and the string on the right will be the filename base for your backup files and so shouldn’t contain any spaces or special characters.
You may also need to alter the variables PATH_TO_WP_CMD, UPLOADS_FOLDER, and APACHE_USER if these settings won’t work for your server.
Go ahead and save the changes to the shell script and make sure that it’s executable by running:
$ sudo chmod +x wpmdbpro-cli-remote-backup-script.sh
Now you can test the script out to make sure that everything’s working well. Here, I’m assuming that you’ve saved the script to your home folder:
$ ~/wpmdbpro-cli-remote-backup-script.sh
You should see output that looks something like this and repeats for each profile you’ve set up:
----------------- Tue May 19 15:35:05 PDT 2015 | foo_com | Migrate DB Profile: 1 ------------------- Backing up db...
Performing migration via WPMDBPro CLI
-------------------------------------------
Verifying connection...
Initiating migration...
Migrating tables 100%[========================================] 0:01 / 0:01
Initiating media migration...
Removing all local files before download of remote media...
Determining media to migrate - 39 of 39 attachments (100%)
Downloading files 100%[===========================================] 0:24 / 1:33
Cleaning up...
Success: Migration successful.
-------------------------------------------
Getting tables to save for backup of foo_com
Backing up tables: wp_commentmeta wp_comments wp_links wp_options wp_postmeta wp_posts wp_term_relationships wp_term_taxonomy wp_terms wp_usermeta wp_users Moving Uploads Folder
TARing Backup
Cleaning up...
If you have errors in your output or something looks like it didn’t work, make sure to double check that you’ve correctly filled out all of the variables and that you’ve got sufficient permissions for all of the folders that we’re manipulating files in. If everything looked good, check out the folder you specified to store your backups in and you should find a .tar.gz file for each of the profiles that you set up with a timestamped filename like foo_com-201505191535.tar.gz.
3. Set up a CRON job
Now that the backup script is set up, let’s set up a cron job to run this script once a week so that we’re creating full backups of all our remote sites every week.
First, open your crontab for editing
$ crontab -e
I’ll set up a job that will run this script every Sunday morning at 2am and append the output to a logfile.
0 2 * * 0 /home/my_user/wpmdbpro-cli-remote-backup-script.sh >> /home/my_user/backups.log 2>&1
Now you can save the crontab file and remember to check the log next Monday to make sure everything went according to plan.
Why?
Some readers may be asking themselves, “Why would I use WP Migrate DB Pro for this when I’m already on the command line? Couldn’t I just use rsync and grab the remote database over SSH?” Sure you could! There are a lot of ways that you could accomplish remote backups and some may be better options for you, but I like doing it this way for a few reasons:
- Low config: Once you’ve got this set up, the amount of configuration you need to do is pretty small. You don’t need to create SSH keys or store remote login and mysql credentials in cleartext on your backup server, all you have to do is copy your connection information into a new profile and add that profile to your bash script
- Portability: If the site you’re backing up will run WordPress, you can back it up, even on restrictive hosts that don’t give you SSH access or allow remote mysql login.
- Because I can: This backup solution might not be right for all situations, but it’s still pretty neat to see what can be done with a bit of config and some CLI knowhow.
That’s it!
This should be enough to get you going with backing up your remote sites, but there’s definitely room for improvement. For example: you could modify the script to send you an email if something went wrong, you could grab the profiles directly from the WordPress database so that you don’t have to hardcode them into the bash script, and you could delete old backups to make room for new ones.
If you’d like to add these features or scratch your own itch, please feel free to fork my gist and leave a comment with a link to your updates.
Do you like the idea of using WP Migrate DB Pro to backup your remote sites? We don’t currently have plans to add this functionality to the plugin itself, but let us know in the comments if you think our plans should change!
Source: https://deliciousbrains.com/remotely-backup-wordpress-database-media-wp-migrate-db-pro-cli/