Add search-replace to db export

[westonruter]

As I tweeted yesterday, it would be great if WP-CLI added support for doing search-replace during the db export process, so that replacements would not have to be done in-place. In order to migrate a database from staging to production, for example, the process with WP-CLI is currently a bit convoluted. I gisted a bash script which incorporates the necessary steps with WP-CLI (here to also export only the specific tables for a site in a multisite network), but in general they would be:

1. wp db export backup-snapshot.sql (snapshot restore point)
2. wp db search-replace staging.example.com www.example.com
3. wp db export production.sql
4. wp db import backup-snapshot.sql (rollback to restore point)

So then as I later tweeted, what if the wp db export subcommand accepted --search and --replace arguments to do the translations during the export process? For example, the above 4 commands could all be done with just one:

wp db export \
    --search=staging.example.com \
    --replace=www.example.com \
     > production.sql

This then would go along very nicely with the proposed wp db list-tables to export a single site from a multisite network for promotion from staging to production. See #708.

As @scribu points out, this means we can't use mysqldump which is really fast. True, but we would avoid having to accompany each wp search-replace with two calls to wp db export and once to wp db import.

[westonruter]

I actually coded up a quite fast db-export-with-replacements script last year which we've used internally, and I'll try to open a pull request sometime over the next week that shows how it could enhance WP-CLI.

[scribu]

@westonruter Can you just share that script in a gist?

[westonruter]

Yeah, sorry, this has been on my todo list for awhile but it got sidelined.

I've been thinking about it, and I think that actually the translating export functionality here would be better located in the search-replace command, rather than adding --search and --replace to the export command. For example:

wp search-replace www.example.com staging.example.com --export=staging.sql

If the --outfile option is supplied, then search-replace would not do a $wpdb->update in place, but rather would write $wpdb->prepare'd SQL out to a file (or to STDOUT if -).

Thoughts?

[scribu]

That makes sense. It would be similar to how sed works.

On Saturday, November 30, 2013, Weston Ruter wrote:

Yeah, sorry, this has been on my todo list for awhile but it got sidelined.

I've been thinking about it, and I think that actually the translating
export functionality here would be better located in the search-replacecommand, rather than adding
--search and --replace to the export command. For example:

wp search-replace www.example.com staging.example.com --export=staging.sql

If the --outfile option is supplied, then search-replace would not do a
$wpdb->update in place, but rather would write $wpdb->prepare'd SQL out
to a file (or to STDOUT if -).

Thoughts?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/709#issuecomment-29543037
.

http://scribu.net

[lkraav]

Hmm, so a few years later, as far as I can tell only https://deliciousbrains.com/wp-migrate-db-pro/doc/multisite-tools-addon/ has CLI interface available for one direction "subsite -> single site" implementation.

Is anybody here still interested in getting WP-CLI modules for all migration combinations going:

• subsite -> single-site
• single-site -> subsite
• subsite -> subsite

I guess https://gist.github.com/westonruter/6277456 does subsite -> subsite right?

BackupBuddy probably has the most complete implementation of all these migration options, but they gave up developing it further long ago because of GUI and networking issues complexity. There's some intricacies involved in the process, such as managing the relevant user records on exports/imports. I believe that CLI could get this to the finish line, since it would eliminate a lot of complex timeout problems etc that hinder the GUI.

Thoughts?

[danielbachhuber]

Thoughts?

If you want to work on subsite -> single-site, etc. database migrations, pull requests are welcome. This issue is for a separate enhancement: exporting a database with replaced values.