Advanced WordPress database search and replace on Linux using sed

When migrating a WordPress or WooCommerce store site to a new domain, you may find some URLs or paths not updated. Likewise, you can get mixed content errors when switching from HTTP to HTTPS, which can usually be fixed by updating the database. Traditionally there are some plugins and scripts to help you update your database to fix these problems, if you still have problems, this post should help you fix paths and urls in your WordPress database.

We first encountered this problem when we discovered the plugin Ultimate VC Add-onsto encode colon and forward slash differently like this http%3A%2F%2F

I am assuming you already have a WordPress database dump generated with PhpMyAdmin, a plugin Adminer, dump MySQL or WP-CLI.

Advanced WordPress database search and replace on Linux using sed

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

Search for occurrences

We can simulate with Grep using -c a flag that will count the number of times it finds the domain.ru string

grep -c 'andreyex.ru' database.sql

You will get an integer as output if you see 0, then there is no domain.ru entry in the database

234

URL encoded

Some plugins use URL encoding which uses this format for HTTP and HTTPS migrations %3A = :and %2F = /

To check if you have any of them in your database, use this command.

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

Slashy

Some plugins avoid и / and can easily be overlooked.

grep -c 'http://domain.ru' database.sql

The WordPress way

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

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

Actual replacement

Many tutorials use in /as separator sed, I use #, to make it easier to read. This also means that you don’t have to search for any forward slashes (/) on your SED team.

means global replacement and redirects to a new file to avoid altering the original database dump.

This command replaces domain.ru on newdomain.ru in the dump.sql file

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

This example has a subfolder for the WordPress installation, # helps maintan keep readability.

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

URL encoded

Replacing URL encoded string

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

This is how to replace URL encoded strings of domains in subfolders

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

Slashy

If you find forward slashes this command will replace them

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

WordPress search and replace path

Replacing the path

sed 's#/public_html/andreyex.ru#/public_html/newfolder#g' dump.sql > staging-or-newdomain.sql

This should work in most WordPress databases, let us know in the comments if you find any other fancy templates!

Using Gzip to Compress New Dump

We can compress the modified dump with Gzip easily, it will 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.

Sidebar