And, make a backup.
Michele
From: Andrew Yochum
Sent: Thursday, November 04, 2010 11:00 AM
To: NYPHP Talk
Cc: NYPHP Talk
Subject: Re: [nyphp-talk] Searching an Entire MySQL Database
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_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
