On Nov 4, 2010, at 9:41 AM, Rob Marscher <rmarsc...@beaffinitive.com> wrote:
>> On 11/4/2010 08:32, p...@blu-studio.com wrote:
>> > I am wondering if anyone has had to search an entire database and do a
>> > search and replace on a string. That is to search in every table, in
>> > every row, in every field.
>> export the entire database into a file
>> use PHP (or a good editor) to make the replacement line by line
>> drop the database
>> re-create it by importing the modified file
> I've had to do something similar. Yes, use mysqldump. Then use sed (if
> you're on a *nix machine). Sed does search and replace by streaming the file
> rather than buffering the whole thing so it won't run out of memory no matter
> how big your dump is. You can send the output to a new file. Definitely
> test out importing it back in on a non-production machine.
This is a sane method. But you probably want to either:
1. Run on a schema-only dump and make sure it doesn't touch any columns or
table DDL before you run it on a data dump
2. Make sure your search pattern matches things within a SQL quoted string.
Dan's solution of looping over tables and columns works well too. Mysql has
regex funcs you can use if necessary.
HTH.
Regards,
Andrew
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
