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
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
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
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
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
Many tutorials use in
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.
g means global replacement and
> redirects to a new file to avoid altering the original database dump.
This command replaces
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
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
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.