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
Advanced WordPress Search Replace Database with Linux using sed
I am going to use the Linux tools
sed for maximum speed. If you do not have access to Linux then you can use the WordPress plugin Better Search and Replace.
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
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
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
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.
g signifies a global replacement and the
> redirects to a new file so you do not modify the original database dump.
This command replaces
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
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.