How to search and replace all instances of a string within a database?

Question

I have a string that is contained inside of a wordpress install (the name of a server) thousands of times, across multiple columns, records and tables.

I'd like to update it with the location of another server - we are moving the content over.

So the source would be something like http://my-server1/some/link/to/something, and I'd want to replace it with http://my-other-server/some/link/to/something. I'm essentially looking to repeat this process for every instance of http://my-server1.

Is there an easy way to do this in MySQL? A tool? Or do I sadly have to update every record problematically?

Thank you,

Answer 1

A crude (but effective) way of doing it would be to dump the schema into a file, carefully apply the search-and-replace and then re-import.

As a matter of fact I did that today :)

Answer 2

Came across this in a google search, but this may help some people. If you know the tables and columns (you could find this using the wildcard search in phpMyAdmin),

UPDATE table_name SET column_name = REPLACE(column_name, 'http://oldsite.com','http://newsite.com');

Replace bold parts with your own.

If you had a large database you could apply this into a script that could loop through each table and column.

Answer 3

The MySQL dump method would be the best bet if you're happy to re-import the whole database. For anyone that doesn't want to do this - WordPress core installation only actually consists of 11 tables, of which few are content columns, so doing a replace by column would be equally easy. Assuming you don't have loads of plugin tables referencing your link or string this would be your SQL:

UPDATE wp_commentmeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');
UPDATE wp_comments SET comment_content = REPLACE(comment_content,'xcurrentx','xreplacementx');
UPDATE wp_links SET link_description = REPLACE(link_description,'xcurrentx','xreplacementx');
UPDATE wp_options SET option_value = REPLACE(option_value,'xcurrentx','xreplacementx');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_content = REPLACE(post_content,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_title = REPLACE(post_title,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt,'xcurrentx','xreplacementx');
UPDATE wp_term_taxonomy SET description = REPLACE(description,'xcurrentx','xreplacementx');
UPDATE wp_usermeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');