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,

having these kind of data (the data should be at least absoute paths) in a database is a pretty bad idea since you need to change it when you move. Why do you have the site's URL in a Wordpress install anyway? There should be only the one, global site pref. — Michael Kohler, Jun 19 '12 at 17:08

score 49 · Accepted Answer · answered May 07 '09 at 17:48

49

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 :)

answered May 07 '09 at 17:48

cherouvim

31,725
15
104
153

Glad it helps. Just make sure you apply the replacement carefully and only change what you really want. – cherouvim May 07 '09 at 18:06
How about the situation when I have to perform at-least 2000 replace instead of one? Could you please help? – SachinKRaj May 20 '15 at 10:16
@SachinKRaj: I'd very carefully write a shell script with 2000 `sed` statements. – cherouvim May 20 '15 at 11:53
3

this is a good method for single simple value but will probably fail in some cases of serialized array data where you also need to replace the character count of the string . ( wp has some of those in default db ) – Obmerk Kronen Jan 26 '18 at 03:34
As Obmerk mentioned, this fails with SERIALIZED data. Is there any way to achieve this with serialized data? To rerun the count – MastaP Jul 03 '21 at 13:31
Best Hack, It works and very easy. – Tayyab Hayat Feb 03 '22 at 11:55

score 23 · Answer 2 · edited Jun 19 '12 at 17:01

23

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.

edited Jun 19 '12 at 17:01

Thiem Nguyen

6,345
7
30
50

answered Jun 19 '12 at 16:31

Enbee

415
5
9

1

will `REPLACE ()` work on serialized array data as well ? – Obmerk Kronen Jan 26 '18 at 03:33
1

@ObmerkKronen In general, no, but it's ok if the replacement string is the same length as the one being replaced. – mc0e Mar 23 '18 at 04:48

score 18 · Answer 3 · edited Mar 24 '16 at 14:57

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

This is actually better answer. I don't know why other answer is selected as correct, and having so many votes — Parag, Apr 03 '16 at 18:43

score 2 · Answer 4 · answered Nov 23 '14 at 18:51

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.

score 1 · Answer 5 · answered Mar 20 '17 at 09:46

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

score 0 · Answer 6 · answered Nov 21 '18 at 11:12

0

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

answered Nov 21 '18 at 11:12

Ondrej

1

unfortunately this is a no-go, if you can't even log into your site because incorrect absolute URL's are already in the database. – klewis Nov 28 '18 at 15:00

score 0 · Answer 7 · answered Jan 15 '22 at 11:46

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/

score 0 · Answer 8 · edited Mar 31 '13 at 16:54

0

There is also a Wordpress plugin called Search Regex, which allows running grep search and replace across the database.

edited Mar 31 '13 at 16:54

Jean

7,623
6
43
58

answered May 08 '09 at 15:18

markratledge

17,322
12
60
106

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

8 Answers8

Linked