Advanced WordPress Search Replace Database with Linux using sed

When you migrate a WordPress site or WooCommerce store to a new domain you may find some URLs or paths were not updated. Similarly when switching from http to https you may get some mixed content errors, usually this can be fixed by updating the database. Traditionally there are some plugins and scripts that help you update the database to resolve these issues, if you still have problems this post should help you fix paths and URLs in your WordPress database.

I first encountered this issue when I discovered a plugin (looking at you Ultimate VC Add-ons) likes to encode the colon and slashes differently like this http%3A%2F%2F

I will assume you already have a WordPress database dump created with phpMyAdmin, Adminer (plugin), MySQL dump or WP-CLI.

Advanced WordPress Search Replace Database with Linux using sed

I am going to use the Linux tools grep and sed for maximum speed. If you do not have access to Linux then you can use the WordPress plugin Better Search and Replace.

Finding Occurences

We can simulate a dry run with grep, using the -c flag it will count the amount of times it finds the domain.com string

grep -c 'wp-bullet.com' database.sql

You will get an integer as output, if you see 0 then there are no occurrences of domain.com in the database

4513

URL Encoded

Some plugins use URL encoding which uses this format for http and https migration %3A = : and %2F = /.

To verify whether you have any of these in your database use this command

grep -c 'http%3A%2F%2F' database.sql

Serialized Arrays – Escaped Slashes

Some plugins escape slashes with \/ and can easily be missed because of the serialized content in json format.

grep -c 'http:\/\/domain.com' database.sql

WordPress Path

Sometimes plugins store information about the storage path on your host, find out by using this command

grep -c '/public_html/domain.com' database.sql

Actual replacement

Many tutorials use / as the delimiter for sed, I am using # to make it easier to read. It also means you will not have to escape any slashes (\/) in your sed command.

The g signifies a global replacement and the > redirects to a new file so you do not modify the original database dump.

This command replaces domain.com with newdomain.com in the dump.sql file

sed 's#domain.com#newdomain.com#g' dump.sql > staging-or-newdomain.sql

In this example, there is a subfolder for the WordPress installation, the # helps maintan readability

sed 's#domain.com/oldfolder#newdomain.com/newfolder#g' dump.sql > staging-or-newdomain.sql

URL Encoded

Replacing URL encoded strings

sed 's#http%3A%2F%2Fdomain.com#http%3A%2F%2Fnewdomain.com#g' dump.sql > staging-or-newdomain.sql

This is how to replace domains with subfolders and URL encoded strings

sed 's#http%3A%2F%2Fdomain.com%2Fsubfolder#http%3A%2F%2Fnewdomain.com%2Fsubfolder#g' dump.sql > staging-or-newdomain.sql

Escaped Slashes – Serialized Data

If you have escaped slashes this command will do the trick

sed 's#http:\/\/domain.com#http:\/\/newdomain.com#g' dump.sql > staging-or-newdomain.sql

WordPress Path Search and Replace

Replacing a path

sed 's#/public_html/wp-bullet.com#/public_html/newfolder#g' dump.sql > staging-or-newdomain.sql

That should take care of most WordPress databases, let me know if you find any other unconventional patterns in the comments!

Using Gzip to Compress New Dump

We can compress the modified dump with gzip easily to shrink the .sql file significantly

cat dump.sql | gzip > dump.sql.gz

Now you can re-import the modified dump using WP-CLI, phpMyAdmin or Adminer.

Sources

Save sed Output to a File
Count the Number of Occurrences of a String
URL Encoding
URL Encoding Reference