Quick Search and Replace in MySQL for WordPress Database

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/

8 Responses

[…] Quick Search and Replace in MySQL for WordPress Database – Idowebdesign […]

[…] a script against the database to change all instances of her old domain to her new domain.  This mysql replace script from IDoWebDesign will generate […]

01.01.11

I’m sorry to tell you this Guillaume, but this can do very bad things.

The problem is the update of the options table. Quite often there will be references to the domain name in a widget. Widget settings are stored within a serialized string in the options table. If you are changing to a domain name that is of a different length than the original, running this query will corrupt the widget configuration and all of your sidebar content will go away. Permalink values suffer from the same problem. So you’d have to go into the permalinks page, change it to something else and change it back to reset them all. When I do this process, I usually adjust the widgets by hand, or use a php script I wrote that can handles the serialized array values.

FWIW, I’d recommend removing the options line and telling people to do the permalinks reset and adjust any references in the widgets.

01.01.11

Oh and I think you need a tweak here:
UPDATE wp_users SET user_email = REPLACE(user_email, ‘www.olddomain.com’, ‘www.newdomain.com’);

😉

01.04.11

@Brian: Yeah good points for the widget, however I have been using this method for a couple of sites and it works fine, maybe this could be a problem for certain widget and not all the widgets. Also for the user email this was just to reset the admin or other email related to the old domain. Thanks!

03.18.12

Thank you very much for the information.
I used your script and worked perfectly.

Regards.

Admin

05.18.12

Hi!
I would like to search and replace multiple paragraphs in several posts in WordPress. How can I do that?

for example, I want to replace :

– Day 1 is sunny
– Day 2 is cloudy

– Day 7 is stormy

With this text

The weather this week was changing

This would save LOTS of time.
Thanks in advance for your help.
Sandra

06.22.12

@sandra: You might want to check out this plugin for more flexibility:

http://wordpress.org/extend/plugins/search-and-replace/

Leave Your Response

* Name, Email, Comment are Required