NYCPHP Meetup

[Andrew Yochum]

On Nov 4, 2010, at 9:41 AM, Rob Marscher <rmarscher at beaffinitive.com> wrote:

>> On 11/4/2010 08:32, ps at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20101104/d6b37840/attachment.html>