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
    # updates the URLs
    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.

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.

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
    # updates the URLs
    wp search-replace "$production_url" "$current.localhost"
    echo "All done, enjoy!"
}

So 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.

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
    # updates the URLs
    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.