Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Whenever I create a new website I first create a staging site on a subdomain like "stage.domain-name.com".

After everything works correctly I export the database, open it in notepad++ and do a find/replace for "subdomain.domain-name.com" and replace it with "domain-name.com"... finally I import it into a new database for the live site.

My question is... what SQL query would I need to run if I just wanted to do this simple find/replace on the entire database using phpmyadmin?

-CH

share|improve this question
    
If you're not familiar with queries try the Search and Replace plugin, wordpress.org/extend/plugins/search-and-replace –  t31os Jan 26 '11 at 14:19

6 Answers 6

up vote 6 down vote accepted

The table where your URL is saved is wp_options. You should do an update on the columns that use the URL for your site:

UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "siteurl"
UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "home"

I might be missing some value, but whenever you do this find/replace process again, you can notice the values and tables that should be updated and add them to this script.

WordPress Codex has a nice guide on how to change a site URL, maybe that's even handier for you: Changing the Site URL

share|improve this answer
    
is there not a way to do a find/replace on the entire database? In other words... I noticed for example that I need to replace the URLs in a bunch of different locations including the media library.... If there was a find/replace for the entire database essentially for every field then this would solve the problem. Thanks for your help –  NetConstructor.com Jan 25 '11 at 11:28
    
Check out that new link I added on the answer. I think that would be the way to go. –  Fernando Briano Jan 25 '11 at 11:29

Best to do options, posts, post content and post meta:

UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');

UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

Also see Searching Data using phpMyAdmin and MySQL | Packt Publishing. And Search RegEx is a good WP plugin to be able to search and replace with Grep through all posts and pages.

share|improve this answer
    
Don't ever change the guid - even if going to a new domain. It's used to uniquely ID the post as the ID can change if the posts are being exported/imported into a new database. For one thing, RSS readers will use the GUID to tell if a particular article has been read or not. Changing the guid will effectively republish all your articles. –  Taylor Dewey Jun 16 '12 at 17:42
    
@taylordewey said: "Don't ever change the guid..." Rubbish. –  songdogtech Jun 17 '12 at 18:55
    
@songdogtech Care to explain why it is rubbish? –  bungeshea Mar 6 '13 at 4:39

This is a great drop-in script that I use and it works beautifully with the serialized arrays that WP uses to store options. Just make sure to delete it from your remote server when you're done because it's a HUGE security risk.

http://interconnectit.com/124/search-and-replace-for-wordpress-databases/

share|improve this answer
1  
I don't know why I was -1'd. This script is much better than a SQL statement. Feedback please? –  lancemonotone Aug 3 '11 at 14:10
    
a tool that people who dont know sql can use, is upsetting to those who write in sql –  Jon Jun 23 '13 at 18:54

you do not have to do this , you can use relative paths.

when you are linking something instead of subdomain.soemthing.com/image.jpg - use /image.jpg for example

like this you won't face the problem in the first place.

otherwise for a mysql update statement you can use

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);
share|improve this answer
    
Thanks... yeah I will do that next time. The SQL statement does a find replacement on the entire database (including all tables)? –  NetConstructor.com Jan 25 '11 at 12:03
    
@NetConstructor.com The SQL statement mireille gave you above is the generic MySQL command for replacing a string in a specific field in a specific table. If you tried running this statement exactly as it was written, it wouldn't work. For this command to work, you'd need to change the TABLE_NAME & FIELD_NAME to a real field and table used by WordPress. –  Manzabar Jan 26 '11 at 3:15

To change the wordpress domain what we often need, may be to make the site live from localhost: This is a complete list of update queries:

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');
UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_url = replace(link_url, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_image = replace(link_image, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_usermeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

/*UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl' OR option_name = 'widget_text' OR option_name = 'dashboard_widget_options';*/
UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com');
  • We have to also add other tables which are not default with WP if necessary.
share|improve this answer

I too would recommend using a script like the one @lancemonotone mentioned earlier in order to avoid problems with certain widgets and plugins being affected by not serializing the URLs as you replace them.

http://interconnectit.com/124/search-and-replace-for-wordpress-databases/

Offers a great PHP script that can save you many hours of tedious rebuilding while moving complicated sites from production to live.

share|improve this answer

protected by Community Dec 3 '12 at 18:16

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.