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.
65 comments
Nora
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 ?
Andy Globe
You guys are awesome, Just saved my time of manually updating them,
Thanks Guys!
Emmanux
Thanks man
Andreas
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.
Bomoza
The article was really helpful
Joel
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’);
Wino
This guide is really helpful. My site was stuck in a redirect loop but this one saved my life. Thank you so much man.
Olasiyan Oluwaseun
Thanks… Still working… Just used it to rename my tags url
Trannhom
it still worked in 2020, thank you for this article. You saved my site….
Ronan O'Driscoll
Still works! Thanks for the help
Luis Medilo
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!
Kenneth Purtell
Bookmarks / links can also need the treatment.
UPDATE wp_links SET link_url = replace(link_url, ‘oldurl’, ‘newurl’);
Projectile Creative
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);
————–
David R. Giorgi
Greetings from the French Alps – Many thanks !!!
Bob
Just wanted to say thank you, friend. This was so helpful!
Farid
Thank you sir, the script above works perfectly.
sudochap
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
sam
Thank you for the SQL commands Working Perfectly
kiai
Thank you for the SQL commands!!!!! xoxoxo
Mayank Gangwal
Thanks a lot. Great help. Highly appreciated.
Sara
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?
Racoon Media
Absolutely great resource.
Michael
Thanks for this guide! Just spent half a day scratching my head until I found this.
VietNet
Thank you ! I have read it every time I change
Shahid
Fantastic. Awesome A++++
Jeremiah
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!
Frank
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
Gsmitt
Awesome, I was looking for this. Thank you so much for posting this.
TB
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?
Daniel
Thanks a lot! This saved me so much time during migration to new, local hosting!
Dzinyna
I was amazing… Thank You!
Corey
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
JIMz
thanks for your very concise and accurate instruction !
mark
Thank you so much for this!
Aurelio Jargas
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
Fotograf nunta brasov
Thank you so much for this good recomandation with mysql :)
Thomas
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
dieng
thanks it work.
its easier via msyql query than edit config
blablaname
thanks for the info
Greg
All is ok, thanks
me
ty works dident need to do part 2 serilzation probbaly cuz my site is simple
tanju
thnak you very much for your sql knowledge
tanju
thank you very much you saved my life http://comturkey..com
jawad
thanks buddy its working for me but could you pelase tell me the code for category change
Jordan
Very helpful.Thanks.
Nic
Worked perfect thank you
Nikunj
Thank you for your help, this worked like a charm
Dawid
This is absolutely invaluable! Thank you so much!
yvan
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 :)
mwafusi
good work
Adrian Pintilei
Thank you! Now it takes me 2 minutes to import a website