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

7 Views

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.

Read More

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,

Post Views: 7

Related posts

Leave a Reply

8 comments

  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 🙂

  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.

  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');

  4. Your question has been posted on 2009 and during that year another guy developed a basic php tool to search for a string throughout all the tables of a certain database. Optionally you can also replace all its occurrences with a different string.

    Still after 5 years (at time of writing) the use of this tool against a WordPress installation is effective and much easier in my opinion than using a mysql dump (although you might want to create a dump as well before running the script for backup purposes).

    You can find more info in the blog of its author Eric Amundson and in the launchpad’s page of the project MySQL Search & Replace

    I don’t believe you are still looking for an answer after all this time, but I decided to post my hint as well, hoping it can help someone else popping over here in the future still searching for a solution to this issue.

  5. Addittionally to Brad Larson’s answer – to set variables like:

    SET @what_to_be_replaced = "what_to_be_replaced", @to_be_replaced_by = "to_be_replaced_by";

    And then use it as follows:

    UPDATE wp_commentmeta SET meta_value = REPLACE(meta_value, @what_to_be_replaced, @to_be_replaced_by );

  7. MySQL’s REPLACE or replacing the string manually in sql file or db in WordPress is not a good way. WordPress uses serialized fields that may break if you replace some string in them (that was my case)

    I used Better Search Replace plugin that worked for me perfectly

  8. I had to do a wordpress migration recently where I had to change the domain name of the site (1012 occurrences of the site name in post data and elsewhere).
    I tried the mysqldump method and used sed to replace the old with new domain names. This horribly failed after re-import, precisely because of the serialised array data present in wordpress records (my new domain name is 2 characters shorter than the old).
    What worked really well, was wp-CLI, a wordpress command line interface for admin tasks. wp-cli has a built-in search-replace function for database strings that also takes care of the serialised records. It uses the credentials stored in the wp-config.php, so directly targets the wordpress database (in case your mariadb instance also hosts other databases). It’s just a single php file you download and put in a folder in your path. See https://wp-cli.org/