Whether you’re transferring a WordPress site to another server, changing domains, or making a copy for staging, sometimes you need to have your WordPress database work in a different environment than where it was originally created.

If you followed the steps in my previous post on database copies, you probably ended up with some unexpected results. Like maybe you load the site and everything still points to the old site.

That’s because WordPress stores information about where it “lives” in the database. So when you transplant it to a new home it still thinks its living in the old home. To fix this, there are two kinds of database entries you need to find and replace.

  1. Anything with the URL, and
  2. Anything with the server file path.

So, for example, if your old domain was http://mylameolddomain.com and your new domain is https://myshinynewdomain.net you need to replace the old with the new in every place it occurs in the WordPress database.

Same with the server file paths. Your old file path might have been /home/myoldaccount/public_html and your new file path is /home3/new_account/public_html, or perhaps /home/myhostingacct/public_html/staging. Again, it’s a matter of searching the database for wherever the file path occurs and replacing it with the new one.

As always, please do any messing around on a copy of your database, and have a backup! Don’t try to update your actual database file or try this on your only backup. If this doesn’t work and you lose everything — well, you’ve been warned!

If you followed step 2 from my post on how to copy your database, you’ve already got a copy of your original database on your computer somewhere. If you downloaded a compressed (or ziped) version of it, you’ll need to uncompress or unzip it. But in either case, what you have left is basically just a text file. So, you can open it and run a find/replace on it in your favorite text editor.

There’s just one more hitch.

Sometimes, WordPress saves urls and file paths in the database within a serialized array. (What that means is that it translates a php array data type to a string data type before sending it to the database. But that’s a story for another day.)

The hitch is that in a serialized array, the number of characters each item takes up in the database gets recorded along with the information. So there may be a few places where that number will need to be adjusted to account for the difference in length of the url or path.

For example, say your old url was http://mysite.com. That’s 17 characters long. And say your new url is http://staging.mysite.com. That’s 25 characters long. A difference of 8.

In a serialized array, your old url was stored as s:17:"http://mysite.com". Once you’ve done your find and replace, your database now looks like: s:17:"http://staging.mysite.com". The number of characters no longer matches the actual number of characters in the url.

So you’ll need to go through the database again. Find each instance where your new url is stored, and if it’s in a spot where it’s preceded by that s:17 (or whatever number), you add back in that difference of 8 and change the number. So you end up, in our example with s:25:"http://staging.mysite.com" (because 17 + 8 = 25).

Same with the path. Find everywhere the new path occurs and if it has an s:xx preceding it, update the number with the new actual number of characters in the path.

So there you have the do-it-yourself way to get your database fixed to work in a new place.

There is, though, a plugin for that. that makes things much easier. It’s called Migrate DB. The free version works just fine. Install it on your original WordPress site, plug in your new site url and path, and it’ll download your database with all the changes you need. On the new site location, just import the database file generated by the plugin on your new WordPress installation.

And if you find yourself doing this all the time (like I do), there’s a pro version (use this link to get 20% off) that you install in the old and new sites, and they can update each other automatically without bothering with database imports/exports at all. Saves me hours.

Database Screenshot credit: WP Beginner who, by the way, are awesome folks you should get to know!