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');
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, databasename, username, password 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.
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 ?
You guys are awesome, Just saved my time of manually updating them,
Thanks Guys!
Thanks man
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.
The article was really helpful
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’);
This guide is really helpful. My site was stuck in a redirect loop but this one saved my life. Thank you so much man.
Thanks… Still working… Just used it to rename my tags url
it still worked in 2020, thank you for this article. You saved my site….
Still works! Thanks for the help
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!
Bookmarks / links can also need the treatment.
UPDATE wp_links SET link_url = replace(link_url, ‘oldurl’, ‘newurl’);
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);
————–
Greetings from the French Alps – Many thanks !!!
Just wanted to say thank you, friend. This was so helpful!
Thank you sir, the script above works perfectly.
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
Thank you for the SQL commands Working Perfectly
Thank you for the SQL commands!!!!! xoxoxo
Thanks a lot. Great help. Highly appreciated.
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?
Absolutely great resource.
Thanks for this guide! Just spent half a day scratching my head until I found this.
Thank you ! I have read it every time I change
Fantastic. Awesome A++++
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!
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
Awesome, I was looking for this. Thank you so much for posting this.
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?
Thanks a lot! This saved me so much time during migration to new, local hosting!
I was amazing… Thank You!
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
thanks for your very concise and accurate instruction !
Thank you so much for this!
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
Thank you so much for this good recomandation with mysql :)
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
thanks it work.
its easier via msyql query than edit config
thanks for the info
All is ok, thanks
ty works dident need to do part 2 serilzation probbaly cuz my site is simple
thnak you very much for your sql knowledge
thank you very much you saved my life http://comturkey..com
thanks buddy its working for me but could you pelase tell me the code for category change
Very helpful.Thanks.
Worked perfect thank you
Thank you for your help, this worked like a charm
This is absolutely invaluable! Thank you so much!
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 :)
good work
Thank you! Now it takes me 2 minutes to import a website
This post saved my bacon! Thanks!
Thank you for this great info/tool. This worked seamlessly for me.
Quick and easy, many thanks
Really Helped me
This was very helpful. I was able to replace links in the options table with ease, then everything else was straight forward
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 :)
Thanks for the blog post! It helps me for the migration of my wordpress website from one openshift gear to another gear.
Sathish
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. :)
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.
Thanks for the same.
Enkosi boss!
Awesome. Saved me from editing 723 entries one by one.
Thank you :)
You really save my life. Pretty neat and quick. I just love the php!
You really save my life, dude! 1 click gogo!
Thx