Change and Update WordPress URLS in Database When Site is Moved to new Host

After migrating a WordPress site to a new URL either to a live production site or a testing development server, the new URL strings in the mysql database need to be changed and updated in the various mysql database tables.

This method just uses the whole mysql database rather than a WordPress export/import from within, and is best suited for a straight swap. So you would copy all the WordPress files/folders to the new destination, set the correct ownership to those files,  then do the database switcheroo.

If you are not comfortable with interacting directly with the mysql database I suggest you check out and use WP Migrate Pro – otherwise read on.

WordPress Database Switcheroo

First, do a mysql database export of the old database on the old server, create a new blank database on the new server, import the old data either in phpmyadmin or mysql directly in the command line.

Make sure you have the new database selected, then run some sql updates and replacement commands on the tables notably, wp_options, wp_posts, wp_postmeta.

Use the code as below and swap in your old and new URLs, no trailing slashes. Also if necessary change the table prefix values where applicable (ie wp_ )

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');

 

mysql-updates-wordpress

mysql-updates-wordpress

or via command line:

username@[~/Desktop]: mysql -u root -p databasename
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 892
Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');
Query OK, 0 rows affected (0.05 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');g
Query OK, 0 rows affected (0.01 sec)
Rows matched: 686 Changed: 0 Warnings: 0

Finally update your WordPress config file to reflect the new database, wp-config.php” which should be in your web document root – change, databasenameusernamepassword and host values:

define('DB_NAME', 'databasename');

/** MySQL database username */
define('DB_USER', 'username');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', 'localhost');

Now everything should link up perfectly.

djave has created a nice and easy script that takes the old and new URLs and hands you the sql code for the WordPress swap, nice!

Serialized Data

Sometimes issues may arise with a problem called serialized data which is when an array of PHP data is somewhat like encyrpted with the actual URL, so if the URL is changed the data is gone.

There are 2 brilliant tools that can handle serialized data and do a search and replace on the old and new databases for the URL and leave serialized data intact.

interconnectit

First up is a script you run via uploading it and browsing to it after migrating and importing your old database into the new – this will then make those necessary changes. Get it from here.

 

WP Migrate Pro

Second up is a proven and robust plugin which you install on your original site and run from their doing a find replace on URL string and webroot, a new database dump is exported and thats the one you import into the new URL hosted database. WP Migrate Pro can find and replace data inside serialized arrays. Get it here.

49 Comments

  1. Frank on March 10, 2017 at 2:42 pm

    Not Found

    The requested URL /wordpress/index.php was not found on this server.

    Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

    Only the home page displaying links not working

  2. Gsmitt on January 5, 2017 at 10:56 pm

    Awesome, I was looking for this. Thank you so much for posting this.

  3. TB on November 7, 2016 at 8:57 am

    Thank you, Queries work fine.
    But still all Featured Images that were set to posts in before are missing.

    Do you have an advise on this issue aswell?
    Were are the paths to Featured Images stored in SQL?

  4. Daniel on November 1, 2016 at 10:05 pm

    Thanks a lot! This saved me so much time during migration to new, local hosting!

  5. Dzinyna on October 17, 2016 at 2:03 pm

    I was amazing… Thank You!

  6. Corey on September 27, 2016 at 1:54 pm

    I’m getting an error could you please help:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘://caterpillartobutterfly.net’) WHERE option_name = ‘home’ OR option_name = ‘sit’ at line 1

  7. JIMz on September 27, 2016 at 9:55 am

    thanks for your very concise and accurate instruction !

  8. mark on September 16, 2016 at 9:08 pm

    Thank you so much for this!

  9. Aurelio Jargas on June 8, 2016 at 1:33 pm

    Excellent guide, thank you!

    But note that the official WordPress guide tells us to:

    “Never, ever, change the contents of the GUID column, under any circumstances.”

    https://codex.wordpress.org/Changing_The_Site_URL#Important_GUID_Note

  10. Fotograf nunta brasov on April 13, 2016 at 7:11 am

    Thank you so much for this good recomandation with mysql 🙂

  11. Thomas on April 4, 2016 at 11:25 am

    Hi

    After hours of research i finally found this guide.
    I run the 4 sql lines and now the wordpress and the pages works fine 🙂

    Thanks

  12. dieng on March 22, 2016 at 3:31 pm

    thanks it work.
    its easier via msyql query than edit config

  13. blablaname on February 21, 2016 at 6:30 am

    thanks for the info

  14. Greg on February 19, 2016 at 10:18 am

    All is ok, thanks

  15. me on February 11, 2016 at 7:28 pm

    ty works dident need to do part 2 serilzation probbaly cuz my site is simple

  16. tanju on February 2, 2016 at 1:24 pm

    thnak you very much for your sql knowledge

  17. tanju on February 2, 2016 at 1:13 pm

    thank you very much you saved my life http://comturkey..com

  18. jawad on November 6, 2015 at 3:18 pm

    thanks buddy its working for me but could you pelase tell me the code for category change

  19. Jordan on October 21, 2015 at 2:32 pm

    Very helpful.Thanks.

  20. Nic on October 19, 2015 at 1:08 am

    Worked perfect thank you

  21. Nikunj on October 12, 2015 at 5:38 pm

    Thank you for your help, this worked like a charm

  22. Dawid on August 14, 2015 at 9:24 am

    This is absolutely invaluable! Thank you so much!

  23. yvan on August 12, 2015 at 12:41 pm

    Hi, i tried your tools but i always get errors when a try to open my website. Also connecting to my admin page seems impossible. If you have some idea please 🙂

  24. mwafusi on August 12, 2015 at 10:20 am

    good work

  25. Adrian Pintilei on July 1, 2015 at 5:49 am

    Thank you! Now it takes me 2 minutes to import a website

  26. JonB on June 24, 2015 at 3:18 am

    This post saved my bacon! Thanks!

  27. Tamara Y on May 28, 2015 at 10:59 pm

    Thank you for this great info/tool. This worked seamlessly for me.

  28. iLouis on May 28, 2015 at 4:47 pm

    Quick and easy, many thanks

  29. […] In der Datenbank direkt, z. B. mittels phpMyAdmin oder einem vergleichbaren Tool, durch ein paar SQL-Befehle. Hier ein (angepasstes) Beispiel von WP Beaches: […]

  30. Priyanka on April 28, 2015 at 6:58 am

    Really Helped me

  31. Mark on April 16, 2015 at 7:57 am

    This was very helpful. I was able to replace links in the options table with ease, then everything else was straight forward

  32. mike rigley on April 10, 2015 at 8:34 pm

    I’d tried a few migration plugins – all failed. I was about to get my hands dirty with sql and then found this post – worked perfectly and saved me hours – thank you 🙂

  33. Sathish on February 13, 2015 at 9:52 am

    Thanks for the blog post! It helps me for the migration of my wordpress website from one openshift gear to another gear.

    Sathish

  34. […] Vía / WP Beaches […]

  35. Jayapal chandran on January 6, 2015 at 11:10 am

    It worked. Good. Thank you.

    First, i installed wordpress into a new database. Then i deleted the tables of the new database. I imported old tables into the new database. I ran your queries. It worked well. Then ran //sitename.com/wordpress/wp-admin. WordPress said the database has to be upgraded. It showed an upgrade button and when clicked it did without any problems. And then things were fine. Yet the user can update wp_users with their preferred username and password. 🙂

    • Jayapal chandran on January 6, 2015 at 11:17 am

      oh… The home page is not appearing. its blank. i cannot see posts in the admin section were as it shows the comments… any idea. hope you don’t have to approve the both comments i made to prevent misleading the users. 🙁 . over looking.

  36. […] Change the URLs in the database from old to new […]

  37. Ranvir Sharma on December 6, 2014 at 12:53 pm

    Thanks for the same.

  38. Mthe on November 22, 2014 at 9:36 am

    Enkosi boss!

  39. […] Change the URLs in the database from old to new […]

  40. […] You can read the original article by Neil Gee over at Coolest Guides On The Planet. […]

  41. 總算買了新網域 | Tsai I-Ta's Blog on January 13, 2014 at 12:55 am

    […] Change and Update WordPress URLS in Database When Site is Moved to new Host 提供了幾個簡單的 SQL 指令可以 update db,但因為我有改變 Permalink […]

  42. Hosting Move | PAPPP's Rambling on December 25, 2013 at 7:13 am

    […] is full of only-slightly-wrong documentation. The only trouble I had relocating was a little bit of touching up to repair some prior sloppyness with my canonical domain between www and naked domain, but that was […]

  43. […] via Change WordPress URLs in mysql Database When SIte is Moved to new Host. […]

  44. Giusep on November 24, 2011 at 4:43 pm

    Awesome. Saved me from editing 723 entries one by one.
    Thank you 🙂

  45. Anonymous on September 20, 2011 at 2:38 pm

    You really save my life. Pretty neat and quick. I just love the php!

  46. gofree on September 20, 2011 at 2:37 pm

    You really save my life, dude! 1 click gogo!
    Thx

Leave a Comment





− 3 = 2

%d bloggers like this: