Idowebdesign -

At some point you might have to change the url of a WordPress site for some reason. There is quick and easy way to update all the URL fields in the WordPress database to make sure there is no trace left of the previous URL.

At this point before doing anything, make sure you have a viable backup of you WordPress database if anything goers wrong. MySQL command are permanent, like the dark pen you took to make nice drawings in the living room when you were 6 years old. Here is an article to read if you never backup WordPress DB before: http://codex.wordpress.org/Backing_Up_Your_Database

Login to PHPMyAdmin and click on the WordPress database. Go to the SQL tab. Grab the following commands and change with the urls needed:

Search and Replace MySQL

UPDATE wp_comments SET comment_author_url = REPLACE(comment_author_url, ‘www.olddomain.com’, ‘www.newdomain.com’);
UPDATE wp_options SET option_value = REPLACE(option_value, ‘www.olddomain.com’, ‘www.newdomain.com’);
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, ‘www.olddomain.com’, ‘www.newdomain.com’);
UPDATE wp_posts SET post_content = REPLACE(post_content, ‘www.olddomain.com’, ‘www.newdomain.com’);
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, ‘www.olddomain.com’, ‘www.newdomain.com’);
UPDATE wp_posts SET guid = REPLACE(guid, ‘www.olddomain.com’, ‘www.newdomain.com’);
UPDATE wp_usermeta SET meta_value = REPLACE(meta_value, ‘www.olddomain.com’, ‘www.newdomain.com’);
UPDATE wp_users SET user_email = REPLACE(user_email, ‘www.olddomain.com’, ‘www.newdomain.com’);
UPDATE wp_users SET user_url = REPLACE(user_url, ‘www.olddomain.com’, ‘www.newdomain.com’);

In less then 3 minutes you are done with the task.

Little Bonus

This maybe useless to you but if you want to change all your post status to draft because of some obscur reason, here is the command:

UPDATE wp_posts SET post_status = ‘draft’ WHERE (post_status = ‘publish’ AND post_type = ‘post’);

UPDATE 31/05/2010

I just build a small script to generate the code automatically: http://idowebdesign.ca/mysql-replace/