Search and Replace for Love and Profit

Moving databases between environments is tough. Many things can break in the process, and a single issue can sink hours of your valuable time. Migrating WordPress databases between environments is especially tricky for two reasons:

• Instead of relative paths, WordPress stores full URLs in the database, in many different columns and tables.
• These URLs, along with other data, can be stored as PHP-serialized blobs.

WP-CLI's search-replace command solves both of these problems for you, with its awareness of WordPress' tables, and intuitive handling of serialized data. On Pantheon, wp search-replace is an integral part of the workflow.

Let's take a look at how it works.

Getting Started with Search and Replace

WP-CLI search-replace only requires two parameters: <old-string>` and <new-string>. On Pantheon, here's how it could be used to transport a database cloned from Live into Test:

wp search-replace 'production-domain.com' 'test-my-site.pantheon.io'

By default, WP-CLI will search through all tables registered to the $wpdb object. In each column of each table, it will inspect the column's rows to see if serialized data is present. If it discovers serialized data, then it iterates through all of the rows, de-serializes data as relevant, recursively performs a search and replace procedure, and updates the row in the database. When no serialized data is present, the procedure is a much simpler MySQL UPDATE statement.

On multisite, WP-CLI will default to only performing search-replace on a specified site. You can search-replace across all sites in the network with the --network` flag.

Want to inspect the results of your search-replace command without making changes to the database? Use the --dry-run flag to mock the entire operation, and see a summary of how your database would've been modified.

Pantheon's Sponsored Improvements to Search and Replace

Last month, Pantheon generously sponsored 15 hours of my time to address some of the long-standing search-replace bugs in the backlog, and make a few substantial enhancements too.

Here are the big search-replace improvements you can expect to see in WP-CLI v0.22.0, to be released in the next few weeks:

• Performance boost! Instead of running a MYSQL LIKE` statement every 1000 rows, we've switched to running it once. On a post meta table of ~3.5 million rows where 75,610 rows were affected, this change improved execution time from 734.926s to 225.509s (3.3x faster)

• Use the --export=<filename> argument to create a SQL file of your transformed data, instead of making updates to the database. This can be helpful if you'd like to prepare a database for a new environment without having to import and then run search-replace.

• Wildcards can be used in table names. Search and replace against meta tables with wp search-replace <old-string> <new-string> '*meta*'. Note: the table pattern needs to be quoted, as * is a special character in Bash.

• Execution time is indicated when running search-replace with the --verbose flag, in case you're curious to see how long each replacement operation is taking.

If you'd like to check these enhancements out early, use wp cli update --nightly to install the latest nightly release.