Search and replace across all the content of a database or application

You may need a bulk search and replace when you change domain or switch on HTTPS. In WordPress this must not be done with an arbitrary UPDATE ... REPLACE: many values are serialised and a direct SQL replacement can corrupt their lengths.

Required preparation

1. Create a staging environment from a recent copy of production.
2. Export the database before each trial run and verify the file:

umask 077
wp db export "$HOME/wp-antes-reemplazo.sql" &&
test -s "$HOME/wp-antes-reemplazo.sql" &&
sha256sum "$HOME/wp-antes-reemplazo.sql" > "$HOME/wp-antes-reemplazo.sql.sha256"

Use a new name for each backup and check that wp db export finishes successfully. The checksum lets you detect changes to the file; a test restore is what shows whether it can actually recover the database. Keep the files protected outside the public folder:

chmod 400 "$HOME/wp-antes-reemplazo.sql" "$HOME/wp-antes-reemplazo.sql.sha256"
sha256sum -c "$HOME/wp-antes-reemplazo.sql.sha256"

3. Download the backup and do a trial import into a separate, empty database.
4. Define the exact source value, target value, tables and columns. Do not run a search over the whole database if you only need to change post content.

Preview with WP-CLI

Before you continue, install and check WP-CLI on the account. WP-CLI handles serialised data correctly. To change links inside post content, limit the operation to the table and column needed and run --dry-run first:

wp search-replace 'http://dominio.example' 'https://dominio.example' wp_posts \
  --include-columns=post_content \
  --precise \
  --report-changed-only \
  --dry-run

Replace wp_posts with the actual name if your installation uses a different prefix. --dry-run mode reports matches but does not change any records: to see how the posts end up, you need to run the real change on the clone.

Review the expected number of changes. If it includes tables or columns you did not intend, narrow the search before running the real change on staging. Then check a sample of the modified posts before applying the procedure to production.

Once the trial run, the backup and the way back have been validated, repeat the same command without --dry-run, first on staging. For production, create a new export, stop writes, run exactly the validated command and check links, widgets, menus, blocks and error logs before reopening the website.

If validation fails, keep writes stopped and restore wp-antes-reemplazo.sql. Confirm the website is back to its previous state before you take it out of maintenance.

Alternatives with an interface

WordPress has plugins that do these tasks through a graphical interface, where you set the search, the replacement and the affected tables. They are slower than WP-CLI on large databases, but they must also handle serialised data correctly.

With these plugins too, the export, the trial run on staging, the preview and the explicit choice of tables are required. Do not use a global option without checking its scope.

One option with a preview is:

• Better Search Replace

If you are changing the whole domain

The example only changes post_content; it does not change settings, widgets, menus or plugin tables. List those locations and prepare the matching scope. Do not replace the guid column automatically: it is not a browsing URL. On WordPress multisite, review each site and the network configuration before widening the command.

See the options for wp search-replace. For other applications, use a tool that understands their format: WP-CLI does not make a non-WordPress database a compatible case.