Fastest way (least amount of steps) to locally import a remote database using WP-CLI

Question

I'd like to automate the task of importing a remote database using the WP-CLI.

The current process is to ssh to the server, and run an export to file using WP-CLI, copy the file to a local directory via scp or rsync, and then import the file through the WP-CLI. I would like to utilize an @alias and remove as many steps as possible here.

While I'd like to think something like this is possible:

echo "$(wp @remote db export -)" | wp @local db import -

With a DB size > 5GB uncompressed, this seems like a more viable option:

DB_EXPORT=$(echo "$(wp @remote db export -)" | gzip | base64 -w0); echo "$DB_EXPORT" | base64 -d | gunzip | wp @local db import -

Unfortunately I may be hitting the limits of the terminal or the structure of this call should be cleaned up because my window just seems to hang.

Is there another solution where I can remove scp from this process? Are there any other commands I could utilize here? I've removed multi-site from the examples here but that's also something to consider which could be part of the alias.

Ideally, I would hope for something like this in the future:

wp @local db import @remote

---

Current example setup of using @alias with Basic Vagrant box.

~/.wp-cli/config.yml

@basic:
    ssh: basic.dev/var/www/wordpress/

~/.ssh/config

Host basic.dev
    HostName basic.dev
    User vagrant
    IdentityFile ~/sites/basic.dev/.vagrant/machines/default/virtualbox/private_key

---

Updates

Bases on @davemac it looks like this process could easily be simplified to

wp db import - <<< $(wp db export -);

Now I just need to take into account MU-Site's tables and site_url

wp @basic db export --tables=$(wp @basic db tables --url=http://basic.dev/site/ --format=csv) - | gzip > basic-dev-site.sql.gz

blog_id=$(wp @basic eval --url=http://basic.dev/site/ 'echo get_current_blog_id();');

prefix=$(wp @basic eval --url=http://basic.dev/site/ 'global $wpdb; echo $wpdb->prefix;')

site_url=$(wp @basic eval "echo site_url();")

Using search-replace - thanks @WestonRuter

sql=$(wp search-replace $(wp eval "echo site_url();" | cut -d ":" -f2) "//new-site.com" --network --skip-columns=guid --export); printf "%s" "$sql"

WP multisite - export a remote site to local import without files:

wp @remote db export --tables=$remote_tables - | sed "s#$remote_prefix#$local_prefix#g" | sed "s#$remote_site_domain#$local_site_domain#g" | wp @local db import -

---

Similiar

• #3162 - Support for using the local filesystem with wp --ssh=<host>
• Sparks - Syncing-Database
• Migrating A WordPress Site With wp-cli
• Migrating a Site Quickly with SSH and WP-CLI
• 10up/MU-Migration plugin
• How to utilize WP-CLI from inside WordPress, not SSH
• Are there WP-CLI methods for get_current_blog_id, get_blog_details or $wpdb->prefix?

Answer 1

Since WP-CLI 0.24.0 you can now use aliases which enable you to import a remote database quite easily.

By using aliases, you can run WP-CLI commands against another WP-CLI install. That install could be a remote machine.

With this in mind I've hacked together a bash alias that chains together several WP-CLI commands to pull a remote WP database into a local site. In this case, I have a local wp-cli.yml file where I have set @prod as an alias to my production site (which uses an SSH alias).

pullprod() {
    # make a backup of the current local database
    wp db export _db.sql
    wp db reset --yes
    # get current directory name, used for database and URL
    current=${PWD##*/}
    # connect to remote site and ssh the remote database down to our local directory
    wp @prod db export - > $current.sql
    echo "copying of remote database to $current directory complete."
    wp db import
    # database is now imported so we can delete it
    rm -rf $current.sql
    # get the remote site URL, remove the http:// for our search replace
    production_url=$(wp @prod eval '$full_url=get_site_url();$trimmed_url=str_replace("http://", "", $full_url); echo $trimmed_url;')
    wp search-replace "$production_url" "$current.localhost"
    echo "All done, enjoy!"
}

A pullprod command in the current WP site will do what you require, along as you have the alias set up (which could be automated as well).

It works, but my next task is to improve on how I get the $production_url variable, as at present I am pulling it from a local file.