How do I find and replace individual strings in a MySQL database?

Question

I am switching my Wordpress-powered website to a new server and changing domain names at the same time, which means I have to find and replace many fields in my MySQL database.

Specifically, I must replace "olddomain.com" with "newdomain.com" whenever they appear in a field in MySQL such as "http://olddomain.com/?p=34".

How can I do a massive find a replace in my MySQL database? I know that the LIKE SQL command can find fields with "olddomain.com" in them, but how do I replace specific portions of fields?

Answer 1

You can use this search and replace script for this purpose

http://interconnectit.com/124/search-and-replace-for-wordpress-databases/

As the author said raw replace is not good in these cases

When you’re migrating WordPress (or any other platform using serialized PHP strings in the database) between domains, you must use a safe search and replace method that preserves the integrity of the serialized string lengths. A simple of a dump file for localhost to, for example, thenewdomain.com is problematic because the length of the string changes but the indexes for the serialized strings does not. Consequently settings are lost and widgets disappear. Not good.

Referred in Wordpress.org doc itself.