How to update your WordPress database when changing your domain name

As a WordPress developer I’m constantly called upon to move a website either from a development environment (http://localhost/unicornsandrainbows) to the live domain (http://www.unicornsandrainbows.com) or from an old domain (http://unicornsandrainbows.com) to a new one (http://unicornsridingrainbows.com).

I’ve been using the MySql replace method to get the job done for quite some time now, yes yes I know there are other ways like online services or plugins. The thing is, not every site readily provides the ability to use easily available methods that could automatically get this done, either due to VPN issues or file permissions that were incorrectly setup by a zonked server administrator. (That nobody can get hold of anymore as he is now sitting in a basement somewhere wearing a tinfoil hat because he thinks aliens want to steal all of his brilliant ideas.)

I have come across a couple of these sites now especially in the corporate arena.

Also, I’m kinda paranoid when it comes to security (I don’t really want to be saving ftp and mysql details to some random account online) so I choose this method.

If you’re moving your site from one hosting server to a new one while keeping your domain name the same then none of the below is relevant. This tutorial is only valid if you are implementing a website url change.

This method makes use of running 3 Mysql queries in your database to search for and replace the domain name in the most common places it is stored in a WordPress database.

Feel free to use your choice of Mysql administration tool, I’m quite a fan of PhpMyAdmin and SQLYog.

In each of these calls you need to update 3 variables, first the table you’re updating, In my examples I’m using the standard WordPress table names.  The second variable to update is the old domain name, this is the name of the existing domain. The final change is the new domain name, Yes, you guessed it, this must be replaced by the new domain name.

Update the options table:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';

Now update the posts table guid links:

UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');

Finally, update the content in the posts:

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl'); UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');

That should take care of the site move and the blank page you see when visiting the new domain. Refresh the page and you should see the magical unicorn riding the rainbow that the designer seriously asked you to implement which the client approved.

NOTE: Keep in mind that there is a shortfall when using this method, you see WordPress stores widget data in the form of a serialized array.

Below is an example of how a text widget might be stored in the database:

a:2:{i:2;a:4:{s:5:"title";s:0:"";s:4:"text";s:22:"Wordpress is awesome.";s:6:"filter";b:1;s:6:"visual";b:1;}s:12:"_multiwidget";i:1;}

See the s:5:”title” piece, the “s” defines that the content is of a string format, the “5” tells you that the content of the following saved value “title” has 5 characters. When changing the url of your site, you invalidate the widget because the count of the url string length now changes from 20 characters to 28 characters while the count recorded still remains 20 characters.

Like so: s:20:”https://www.design2code.co.za”

See the problem? If the widget is invalidated in this manner, it simply will not show on the site at all and it will have to be manually recreated.

Using this replace method is really a matter of preference for me, it gets done what I need done without creating to much hassle. Not many of the sites I build make use of widgets and if they do, its usually custom built to display what is needed.

Give it a shot and if you have a another way let me know, better yet if you have a good workaround for the widget issue that can be used in a difficult case… Call me. Seriously, pick up the phone and dial my number, its at the top of the website.

Hope this helps, Nathaniel.