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 the popular and robust  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');

 

Change WordPress URLs for new web host

MySQL change and update WordPress URLs

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 encrypted 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 Search & Replace

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 popular robust plugin that is easy to use,  you install on your original site and run from there doing a find replace on URL string and Webroot, a new database dump is exported and that’s the one you import into the new URL hosted database. WP Migrate Pro can find and replace data inside serialized arrays. Get it here.

65 Comments

  1. Nora on August 8, 2022 at 3:19 pm

    Hello. Thank you very much for this article but this approach does not work with all extensions. For example : buddypress groups and members avatars urls are not updated. How to update urls in all tables please ?

  2. Andy Globe on May 18, 2022 at 4:45 am

    You guys are awesome, Just saved my time of manually updating them,
    Thanks Guys!

  3. Emmanux on September 5, 2021 at 12:17 pm

    Thanks man

  4. Andreas on May 12, 2021 at 1:23 pm

    Thanks for great tip. However I get:

    UPDATE wp_posts SET guid = replace(guid, ‘https://mysite.com’,’https://test.mysite.com’);
    ERROR 1406 (22001): Data too long for column ‘guid’ at row 202137

    Any idea much appreciated.
    Thanks.

  5. Bomoza on March 31, 2021 at 6:02 am

    The article was really helpful

  6. Joel on December 31, 2020 at 2:18 am

    very helpful! I did not even read it entirely I was just looking which tables and columns need to be updated and it’s that simple. for some reason some will go and install a plugin for a simple task. Thank you.

    I just focused on the main part copied below:

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

  7. Wino on November 29, 2020 at 2:48 pm

    This guide is really helpful. My site was stuck in a redirect loop but this one saved my life. Thank you so much man.

  8. Olasiyan Oluwaseun on November 6, 2020 at 6:02 pm

    Thanks… Still working… Just used it to rename my tags url

  9. Trannhom on August 17, 2020 at 8:32 am

    it still worked in 2020, thank you for this article. You saved my site….

  10. Ronan O'Driscoll on May 2, 2020 at 7:50 pm

    Still works! Thanks for the help

  11. Luis Medilo on October 26, 2019 at 1:21 pm

    This is really helpful. I used the script from interconnectit but it didn’t work, so I followed the instructions using the SQL queries. Thanks!

  12. Kenneth Purtell on June 17, 2019 at 10:36 am

    Bookmarks / links can also need the treatment.

    UPDATE wp_links SET link_url = replace(link_url, ‘oldurl’, ‘newurl’);

  13. Projectile Creative on June 4, 2019 at 11:46 am

    Been using this for years now! Thanks for the post.

    To make life easier, I thought I’d drop my modified version of your code. I normally use this in PhpMyAdmin.

    You just have to replace old_url in line 1 and new_url in line 2 and it will do them all for you.

    Hope this helps!

    ————–

    SET @FIND_URL = ‘old_url’;
    SET @REPLACE_URL = ‘new_url’;

    UPDATE wp_options SET option_value = replace(option_value, @FIND_URL, @REPLACE_URL) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
    UPDATE wp_posts SET guid = replace(guid, @FIND_URL, @REPLACE_URL);
    UPDATE wp_posts SET post_content = replace(post_content, @FIND_URL, @REPLACE_URL);
    UPDATE wp_postmeta SET meta_value = replace(meta_value, @FIND_URL, @REPLACE_URL);

    ————–

  14. David R. Giorgi on April 24, 2019 at 1:54 pm

    Greetings from the French Alps – Many thanks !!!

  15. Bob on March 28, 2019 at 7:57 pm

    Just wanted to say thank you, friend. This was so helpful!

  16. Farid on December 27, 2018 at 7:55 am

    Thank you sir, the script above works perfectly.

  17. sudochap on November 14, 2018 at 4:37 pm

    Friggin’ LIFE SAVER!
    If I can do anything to help you back, please reach out and simply remind me of this. It’s good for an IOU!
    ThanQ ThanQ ThanQ

  18. sam on October 20, 2018 at 1:30 am

    Thank you for the SQL commands Working Perfectly

  19. kiai on August 11, 2018 at 5:18 pm

    Thank you for the SQL commands!!!!! xoxoxo

  20. Mayank Gangwal on June 6, 2018 at 9:08 pm

    Thanks a lot. Great help. Highly appreciated.

    • Sara on July 21, 2018 at 8:28 am

      Somebody pls help me. May I know where did you guys changed your url? Is it on the wordpress setting? So, I need to change the url at the setting first and then follow the steps above? And for the DB_NAME, DB_PASSWORD and so on do I need to change it also?

  21. Racoon Media on February 27, 2018 at 8:17 pm

    Absolutely great resource.

  22. Michael on February 11, 2018 at 9:58 am

    Thanks for this guide! Just spent half a day scratching my head until I found this.

  23. VietNet on December 27, 2017 at 9:20 am

    Thank you ! I have read it every time I change

  24. Shahid on November 13, 2017 at 9:04 pm

    Fantastic. Awesome A++++

  25. Jeremiah on November 2, 2017 at 11:05 pm

    This post was immensely helpful. The steps were kinda obvious once I got into it, but this got me started and I was able to resurrect a 4-year old website for a portfolio listing in a matter of minutes. Thanks!

  26. 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

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

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

  28. 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?

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

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

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

    I was amazing… Thank You!

  31. 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

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

    thanks for your very concise and accurate instruction !

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

    Thank you so much for this!

  34. 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

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

    Thank you so much for this good recomandation with mysql :)

  36. 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

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

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

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

    thanks for the info

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

    All is ok, thanks

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

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

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

    thnak you very much for your sql knowledge

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

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

  43. 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

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

    Very helpful.Thanks.

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

    Worked perfect thank you

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

    Thank you for your help, this worked like a charm

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

    This is absolutely invaluable! Thank you so much!

  48. 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 :)

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

    good work

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

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

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

    This post saved my bacon! Thanks!

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

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

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

    Quick and easy, many thanks

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

    Really Helped me

  55. 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

  56. 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 :)

  57. 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

  58. 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.

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

    Thanks for the same.

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

    Enkosi boss!

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

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

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

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

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

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

Leave all Comment