12

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

  • You could write a bash script that is called via cron job to scp/rsync file to remote server. Then on remote server you could have another bash script that runs via cron job that watches folder for the backup file and once the file is there it will import it into the remote DB. This is similar to how I keep a "hot standby" server in sync. – brass Aug 14 '16 at 11:57
  • For me personally, I wouldn't want to do this in a cron job because it adds another dependency to the mix and it's more for just trying to get local in sync with remote when I'm working on it. "Remote" being one of many aliases -- same with local. Another reasons to reduce the function calls is because I tend to like one-line/copy/paste/go! lines of code ;) – jgraup Aug 14 '16 at 13:21
  • Have you tried to write the dump into a temporary file instead of a variable? – David Aug 15 '16 at 6:26
  • That's how you need to do it now which requires functions outside of WP-CLI to handle the file transfer --- and more authentication per location. – jgraup Aug 15 '16 at 12:37
  • @jgraup Has this question been resolved? – Ethan Jinks O'Sullivan Aug 28 '16 at 3:24
6

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.

  • This is great. Honestly I was really just looking for the wp @alias db export - > $localfilename.sql . It gets a little more complicated for MU-Sites, but if you want a prod URL, try production_url=$(wp @prod eval "echo site_url();"); echo "The URL is $production_url"; – jgraup Aug 16 '16 at 17:31
  • What exactly does wp db reset --yes do? Ah... clears all the tables so the new files has a clean db. DB Reset – jgraup Aug 16 '16 at 17:44
  • 1
    Thanks for that, I took your prod URL technique and modified it a little to remove the http:// from the production URL (as this makes for a more comprehensive search and replace) – davemac Aug 17 '16 at 1:59
  • 1
    Note also that wp search-replace also supports an--export arg so you can export and search/replace all at once: wp-cli.org/commands/search-replace – Weston Ruter Aug 17 '16 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.