Sure you migrate WordPress databases on the regular and have a pretty good idea of what you’re doing, but is there anything that could make your migrations go more quickly or smoothly? In this post, I’m going to help you level up your WordPress development workflow with some pro tips for the best product in the Delicious Brains lineup – WP Migrate DB Pro.
OK whatever Pete, you’re just saying that because that’s the product you work on…
Well that may be true, but there’s some lesser known stuff about WPMDB that I think might blow some people’s socks off! We’ve covered some power user tips in the past, but I wanted to dig back in now that the Theme & Plugin Files Addon has been released. So let’s dig in and get learning something 🎉.
Avoiding Failed Database Migrations and Migration Errors
The first tip I’d like to cover is more of an anti-tip. What do you do when your migration fails? Throw your arms up and run away from your laptop?
Well you could do that, or you could put your debugging hat on and get to work 🐛. You may not realize, but there are some types of migrations that are more likely to fail. Push migrations that are transferring media cause a lot of problems. This is because some server software, like ModSecurity and some plugins (I’m looking at you Wordfence) don’t like getting specific types of HTTP POST requests fired at them.
The way these “firewalls” work are by string and pattern matching URL query and POST body contents and outright blocking requests that contain certain characters.
/\\.(p(h(p|tml)[0-9]?|l|y)|(j|a)sp|aspx|sh|shtml|html?|cgi|htaccess|user\\.ini)($|\\.)/i
If something about your request matches one of these patterns, you’ll get an error of some type. In these cases you might see a 500 server error, a 403 server response, a 404 server response, or nothing at all.
We have a doc about these kinds of errors but the best place to understand what errors are occuring is your server’s error logs.
What can you do about this problem? In some cases, your best bet is flip the migration and run a pull. It’s not always possible, but you can use ngrok as Jeff mentioned.
Another option is to temporarily disable whatever security plugins you have running on your site, run the migration, then re-enable them afterward. This isn’t always an option if your server is running ModSecurity, however.
You could run a migration without media and transfer media items another way (perhaps with a hook 😉).
A final option is to skip the network component altogether and export your database and import it on the remote site. This wouldn’t migrate your media, but you’d be part of the way there.
I should also mention that we’re currently working on getting around these kinds of issues migrating media files, but it’s helpful to know what kinds of errors can occur and how to get around them.
Spend Less Time Setting Up Dev and Staging Sites
The release of WP Migrate DB Pro’s most recent addon, the Theme & Plugin Files Addon, is a real game changer for developer productivity.
The Themes and Plugins add-on for WP Migrate DB Pro from @dliciousbrains is pure genius. So many use cases I didn't think of when they released it.
— Clifton Griffin 🛒 (@clifgriffin) January 22, 2019
Mainly:
1) Staging sites
2) Plugin support
Saves me time every week.
Some of the main use cases we thought of when brainstorming for the the addon were:
- No need to wait on SFTP credentials to get started working on a client site
- Easily set up staging sites
- Developer laziness
Seriously, developer laziness is a real driving factor. The main use we saw for the TPF addon was for pulling theme and plugin files to your local dev environment.
It’s also helpful to download updated plugins from remote sites. Say your client added a new plugin, or worse updated a plugin on the live website. With the Theme & Plugin Files Addon you can download this new or updated plugin when you grab the latest version of the database.
What if you’re looking to download just some theme and/or plugin files, and not the database? Fortunately, WP Migrate has an option to completely exclude the database during migrations.
Swap Out SFTP for a Faster Theme and Plugin Download Workflow
That’s all fine and dandy, but did you know that the TPF Addon is actually faster than SFTP when downloading themes and plugins with a large amount of files? Don’t believe me? Have a look at the video below:
TPF: 23s (including wp_links database table) SFTP: ~32s
Because we’re batching requests and packing/unpacking payloads, it’s actually faster to download large amounts of files with the Theme & Plugin Files Addon than with SFTP.
So yeah, it’s fast, and a quick way to get your development and staging environments set up!
Hook it Up
Some customers may know about the Tweaks Plugin, a set of example filters that can be used to extend WP Migrate DB Pro. But that’s only the tip of the iceberg!
We currently have 128 apply_filters() calls and 48 do_action() calls in the core and addons codebase. Many of these hooks are in place for the addons themselves to use, but that doesn’t mean they’re not available for developers to use as well! Let’s look at a few of these hooks and the ‘whys’ and ‘hows’ of using them.
Get MOAR Records: Speed Up Large Migrations
The first hook we’ll look at is very simple, it manages the number of records to migrate in each batch. By default WP Migrate DB Pro only sends 100 records per batch. If you have a large database with tens or hundreds of thousands of records this can take a long time to migrate.
The wpmdb_rows_per_segment filter allows you to override this number. For example, to bump this up to download 10,000 records per request, all you need to do is add something like this in a plugin/mu-plugin on the remote server:
add_filter( 'wpmdb_rows_per_segment', function () {
return 10000;
} );
I should mention that this will seriously spike your server memory and CPU, so use with caution.
SQL filters
Similar to the wpmdb_rows_per_segment filter above, there are filters for pretty much every option or setting used when migrating tables. You can control the SQL you get with the wpmdb_rows_sql filter and you have access to the various parts of the select SQL. You can run a find for apply_filters on the Github repo for the free plugin to get an idea of how many there are!
A commonly used filter here is the wpmdb_preserved_options filter. This filter allows you to specify records in the wp_options table to not modify/migrate. This is helpful for preserving settings and license keys used by other plugins. Otherwise these would be wiped out after a migration.
To use this filter is pretty simple. As above, in an plugin/mu-plugin add something like the following:
add_filter( 'wpmdb_preserved_options', function( $options ){
$options[] = 'option-to-preserve';
$options[] = 'another-option-to-preserve';
return $options;
});
The wpmdb_preserved_options filter is fired on finalize, so it would need to be in place on local for a pull and remote for a push.
Automate Even More
The last filter we’ll cover is wpmdb_migration_complete. This is actually a do_action() call so there are no values to filter here. It’s fired when, you guessed it, a migration is finished.
There are lots of cool things you could do with this action. You could fire a request to your Slack webhook URL to let you know that a migration is finished. You could run a git pull or git push to get your latest code, or even run a script to rebuild your site assets.
Example Usage
Earlier I mentioned that if a media files migration isn’t working because of a “firewall” you could use a hook solution to get around it.
Below is example of a mini-plugin you could use to send over your media library with the scp CLI command. SCP is a command line tool to “securely copy” files from one place to another. It has the best chance to work cross platform over something like rsync.
<?php
add_action( 'wpmdb_migration_complete', function () {
$server_address = 'user@remote';
$local_dir = WP_CONTENT_DIR . '/uploads';
$remote_dir = '/your/remote/dir';
$cmd = sprintf( 'scp -r %s %s:%s', $local_dir, $server_address, $remote_dir );
//Run SCP command
exec( $cmd );
$webhook_url = 'https://hooks.slack.com/services/SECRETSTUFF';
//Send remote request to Slack
$data = wp_remote_post( $webhook_url, array(
'headers' => array('Content-Type' => 'application/json; charset=utf-8'),
'body' => json_encode(['text' => 'Media migrated.']),
'method' => 'POST',
'data_format' => 'body',
));
} );
The key part is the exec() call. That’s PHP’s access to the host operating system. The exec() call lets you run any CLI command from a PHP process. Most web hosts have this disabled, but if you’re running a push from your local machine it’s likely enabled already.
In the script we essentially just compose the scp command and fire it off. Simple as that. The cool thing is that this will even work with shared hosts that support SFTP. You may have to use an additional layer like sshpass or a similar tool, but I’ve tested with WP Engine and it works fine.
Since we’re using the wpmdb_migration_complete we’ll just include this on our local site as a plugin, since this filter fires on both sites at the end of a migration.
The last section of code is optional, but just sends a POST to a Slack webhook to let you know it’s complete. You could also log something or send an email/SMS/pigeon to let you know it’s all done.
Productivity++ FTW
There you have it, some pro tips for the slightly advanced WordPress developer and power users of WP Migrate DB Pro.
We covered some common issues we’ve seen customers encounter when running migrations and highlighted the power and speed of the Theme & Plugin Files addon 💪.
There are also many more filters and actions, but providing examples for all them would be enough for a book. If you’d like to find all of the filters I suggest running a ‘find’ for apply_filters in the WP Migrate DB Pro plugins.
Let us know in the comments if you have any pro tips you’re using in your development workflows!
