search and replace all database.......

[damien]

Have a website where we have just changed domain names, but am having a problem because content has been submitted that occasionally uses absolute URL's which has resulted in lots of links through the site pointing to the old domain.

What I want to be able to do is do a search and replace in MySQL that replaces all use of the old domain name with the new one. I've found several search and replace guides around the internet, but they all seem to do a search and replace on a specific field on a specific table like this on;


Code:
UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

Can anybody tell me how to do a search and replace on an entire database in all fields??
Thanx a million -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[chicken]

Follow these steps

• Dump the database with mysqldump to a text file.
• Then do a search replace on this dump file. Since it is a text file you can just use any search replace tool that you want.
• Then import the dump file to the DB again and you should be set.

If your DB is so big that you can't open the dump file in a normal editor you will probably have to use some search replace tool like sed or PERL.
Since I'm a PERL man I would do:

Code:
perl -p -i.bak -e 's/oldstring/newstring/g' yourDumpFile
That will substitute all occurances of oldstring to newstring in yourDumpFile and also save a backup file callec yourDumpFile.bak. If you don't want a backup file you can omit the .bak in the argument.

Hope it helps
Bye buddy -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --