Correct Search & Replace Queries for Changing URLs in a WordPress Database?

Question

Have developed a site on the production box using a temporary address (http://IP-adddress).

Now it's time to go live. Once, ages ago, I successfully used phpMyAdmin and did a search/replace, but am wondering if it's still "the best" way to go.

Will these work okay, still? The site is in WordPress 3.0.1.

update wp_options set option_value=replace(option_value,'OLD URL','NEW URL') where option_value like "%OLD URL%";    
update wp_posts SET guid=replace(guid, 'OLD URL','NEW URL');
update wp_posts SET post_excerpt=replace(post_excerpt, 'OLD URL','NEW URL');
update wp_posts SET post_content=replace(post_content, 'OLD URL','NEW URL');    
update wp_postmeta SET meta_value=replace(meta_value, 'OLD URL','NEW URL');

I've read a number of related posts here, but none with the actual queries.

I thought this search and replace plugin sounded promising, but the only documentation I found was in German, which I can't read, so I'm too cautious to try it.

And this thread about moving from development to production has lots of information in it, but:

1. much of it is over my head, and
2. no queries listed.

TYIA,

Mike

Answer 1

Yes, these queries still work.

This is a complete how-to: http://web-kreation.com/tutorials/migrating-a-wordpress-site-ftp-phpmyadmin-and-sql-queries/

Answer 2

This one will cause you some problems:

update wp_options set option_value=replace(option_value,'OLD URL','NEW URL') where option_value like "%OLD URL%";

It will corrupt any active widget data which is stored in the options table, because that information is most likely serialized, and the query doesn't account for serialized data. That was the gist of the wp-hackers thread. However, nobody on that thread seemed to come up with a foolproof query for handling serialized data.

You might want to just change the options for 'siteurl' and 'home' in wp_options individually, then modify your widgets from the dashboard.

Answer 3

The others have given you suggestions on the SQL queries which you asked for and I hope learning them helps you with the process; I know I always helps me to learn one level below where I need to operate.

But you also asked for the best way to do it and I'd like to suggest you look at the post you reference again (Moving from Development to Production) but this time look at the plugin I reference in the post, a plugin to fixup the data after going from one server to another. Rather than get your hands dirty periodically with SQL you have to relearn why not delegate that to a plugin that handles it?

The plugin is fully hookable so when you do need to get down to SQL to handle some other data that one of your other plugins introduced you can extend it and thus it can handle anything that needs to be fixed-up when you move from host to host. Probably the best part is that it gives you a place in /wp-config.php to list all of the details of each host; i.e. domains, root paths, databases, dbusers, passwords and even things like Google Maps API keys when you need it.

The plugin is still in alpha which is why it is here on StackExchange and not listed in the WordPress plugin repository yet:

• WP Migrate Webhosts