How to Update WordPress domain url via Database

0

You might have notice that i have changed this blog domain several time, well it’s a hard decision, the harder part of it is making sure all your old domain url are reflecting to the new one, I made a search and I was able to find some tutorials that worked for me, so I borrowed the MySQL statements to share with others who might be interested.

Make sure you have done a MySQL database export of the old database on the old server, create a new database on the new server with new details or you use the previous one, and then import the old data with phpmyadmin or through MySQL using command line.

Make sure you have selected the new database, now run sql updates and replacement commands on the tables wp_options, wp_posts, wp_postmeta. Make sure you change your WordPress table prefix if you are not using the default “wp”

Use the code as below and swap in your old and new URLs, do not include the trailing slashes.

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');
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');

Or you can also do this through command line..

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');
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');

Which will display the following, just like mine.

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 46
Server version: 5.5.46-0ubuntu0.14.04.2-log (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> UPDATE wp_options SET option_value = replace(option_value, ''http://www.oldurl.com','http://www.newurl.com'); altwpble.org', 'http://www.newurl.com') WHERE option_name = 'home' OR option_n ame = 'siteurl';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 changed: 2 Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, ''http://www.oldurl.com','http://www.newurl.com'); p://www.newurl.com');
Query OK, 7778 rows affected (0.97 sec)
Rows matched: 8209 changed: 7778 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl.com', 'http://www.newurl.com');
Query OK, 3642 rows affected (2.41 sec)
Rows matched: 8209 changed: 3642 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl.com','http://www.newurl.com');
Query OK, 1092 rows affected (0.64 sec)
Rows matched: 79570 changed: 1092 Warnings: 0

NOTE: Do not forget to change the table prefix, also change the url to either www version or non www version in other not to mess up things.

Let me know if you have any problem.