Using WP-CLI to import a remote database

June 5, 2021

This blog post is more for my own future recollection than anything else, but I figured it might be useful for some of you, too.

If you’re used to working with multiple stages of development (e.g., “local”, “develop”, “staging”, “production”), it’s useful to be able to easily migrate files and data between them. For the longest time, I used a tool called Wordmove to do this (or, to quote Mitch Hedberg, “I still do, but I used to, too”, just not for my own site).

What I simply love about Wordmove is how easily it makes it to move plugins, themes, mu-plugins, and the WordPress database itself between environments. Unfortunately, since I switched to a Composer-based install and moved my WordPress installation into its own directory, Wordmove is no longer an option. I’ve simply dealt with this for the most part by being annoyed by logging into my server, exporting the database, downloading it, then re-importing it on my local.

Fortunately, there’s an easier way.

If you didn’t know already, WP-CLI has a db export command, and I use it all the time. You might not also know that you can set up WP-CLI with a configuration file where you can define login credentials for different environments.

In my local development instance, I now have a wp-cli.yml file that looks like this:

@production:
    ssh: <my-username>@<my-server>/<my-production-wordpress-server-path>

What this allows me to do is run any WP-CLI commands from my local installation against my production installation. Those commands will look something like this:

wp @production post list
wp @production rewrite flush
wp @production db query
wp @production shell

The most important command for this blog post, however, is wp @production db export – I want to get an export of my production database and be able to pull it down into my local instance.

Normally, when you export a database using the wp db export command, it writes a file to the directory from which you called the command, and you can give it a <file> option so that it has whatever filename you choose when you’re done. However, you can also pass a simple “-” option to the command to tell WP-CLI to write to stdout, which is perfectly useful for importing the database locally.

What does this mean? It means that using a combination of WP-CLI commands, I can export my production database directly into my local database, then rewrite the URLs to match my local setup:

wp @production db export - | wp db import - && wp search-replace '//jmichaelward.com' '//my-local-domain'

Voilà!

Update

Thanks to Alain Schlesser for the following recommendation:

Did you notice that the search-replace command actually has an –export flag, so you can use it as a replacement for the db export command?
— Alain Schlesser (@schlessera) June 6, 2021

This means that it’s possible to shorten the above command by excluding the export command altogether and using the --export flag on the production URL.

wp @production search-replace '//jmichaelward.com' '//my-local-domain' --export | wp db import -

I tested this locally and it works great! --export accepts a file name as an option, but if you exclude it, it writes to STDOUT just like wp db export does, so it’s ultimately a combination of the two statements.

Because wp db is such an explicit command, I hadn’t considered that there might have been an export option as part of the search-replace command. It looks like this issue was introduced for discussion and ultimate inclusion into WP-CLI quite some time ago, where it was ultimately decided to include this flag on the search-replace command for performance reasons (plus, it has fewer flags!).

All that said, the only thing that gives me pause about using the above approach is that, if one were in a hurry and forgot to include the --export flag, theyd’d be left with a production database that had the replaced string everywhere. As with anything, there are ways to mitigate this, such as wrapping this command in an alias so one needn’t type it out each time, but knowing my own wariness about doing any modifications to a production database, I may personally continue to use the export -> import -> replace approach. However, the above demonstrates just one of the many powerful aspects of WP-CLI, and whichever approach you use in your own work, hopefully this adds a wonderful new tool to your toolset.