Love and profit for wp search-replace — thanks Pantheon!

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. Because PHP serialization includes the string length in the blob, replacing one value with another value of a different length also needs to update the encoding as well.

WP-CLI’s search-replace command addresses both of these problems for you, with its awareness of WordPress’ tables, and intuitive handling of serialized data.

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

Let’s take a look at how search-replace works, and then we’ll review the new features coming in WP-CLI v0.22.0.

Making search-replace an integral part to your workflow

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

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

By default, WP-CLI searches through all tables registered to the $wpdb object. In each column of each table, it first inspects 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 in any of the rows, the replacement procedure is a much simpler MySQL UPDATE statement.

On multisite, WP-CLI defaults to performing search-replace on a single site. You can search-replace across all sites in the network with the --network flag.

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

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-replace

Here are a few of the improvements you can expect to see in the forthcoming WP-CLI v0.22.0:

• A huge performance boost! Instead of running a MYSQL LIKE statement every 1000 rows, WP-CLI now just runs 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 is a helpful feature when you want 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-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.

Happy to see these new features? Please share the love on Twitter 🙂