Writing a MySQL database find/replace script in PHP
When I had the same problem I ran a mysqldump of the database, then opened in a text editor and just search/replaced the values, before using the SQL to create the new database. Quite simple, surprisingly fast,especially for a one off.
As pointed out, you have the problem with serialized data, so you could do a similar thing with a simple PHP file:
<?php$handle = @fopen("/tmp/dump.sql", "r");if ($handle) { while (($buffer = fgets($handle, 4096)) !== false) { $newLine = preg_replace_callback('@s:(\d+)(:\\\"[^"]*xxxxxxxxxxx\.com)/dev@', create_function('$matches', 'return \'s:\'.($matches[1] - 4).$matches[2];'), $buffer); $newLine = preg_replace_callback('@s:(\d+)(:\\\"[^\\\"]*xxxxxxxxxxx\.com/public_html)/dev@', create_function('$matches', 'return \'s:\'.($matches[1] - 4).$matches[2];'), $newLine); $newLine = str_replace('http://www.xxxxxxxxxxx.com/dev/', 'http://www.xxxxxxxxxxx.com/', $newLine); echo $newLine; } fclose($handle);}?>Note: this works on a mysqldump, if you're testing, you'll need to remove the \\\ before the "s in the preg_replace_callbacks - this is just mysqldump escaping quotes.
Also Note: There are two preg replaces (one for normal URLs and one for server paths), and one str replace for standard URLs left over.
There is the serialization fixer wordpress plugin which does it for the non-programmer: http://davidcoveney.com/575/php-serialization-fix-for-wordpress-migrations/
You can also do it with PHP.
Also, here is a sample MySQL code which does it:https://data.stackexchange.com/drupal%20answersmeta/query/80128/sql-search-and-replace
Take care, since this is a very dangerous tool.