Mysql mysqldump script with find and replace.

I need to create a linux shell  script where by i would  run the script with these arguments:
scriptname -dbnumber -olddatabase -newdatabase
1. Run mysqldump on datbase {olddatabase}
2. Do a find and replace within the dumpfile for all patterns that match "INSERT INTO WP_"
   with this new pattern: "INSERT_INTO_WP_766"  (assuming 766 was the dbnumber)
3. feed the resulting dumpfile back into {newdatbase}
     Example: mysql -uadmin -pfoobar database1 < dumpfile.sql

Note: I would like to have stip #1 output only tables which appear in a file called "usetables.txt"
for example:  the database being dumped my have 54 tables but I only want the 4 listed in usetabes.txt

For those of you who wonder what I am doing? I am attempting to create an automation script to migrate multiple standalone wordpress datbabases into a single new wordpress mu site, via manipulation of backend tables. Any addn tips will be favorably viewed when doling out the 500 poimts to best answer.  Thanks!

Louis CapeceAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

JesterTooConnect With a Mentor Commented:
In order to limit the tables being dumped/restored you could simply enumerate them on the command line in step 1 right after the dbname.  If you prefer to "pull" them from a text file then write a script to read/parse that text file as step 0 then generate the rest of the steps as a new script and invoke it from the first one.

Scripts that generate/run other scripts are very powerful for automation processes.
In order to do the find and replace all you need is the SED utility which I think on Linux is installed by default. To use it is very simple. Here you can see how:

So what you do is:

1. Create the mysql dump file
2. sed the file created at 1 with the replace you described
3. import it back to mysql server
Some versions of sed have maximum line lengths and a mysqldump output line
can be quite long. Apparently GNU sed does not have a line length restriction
according to this website:

Personally I would write a Perl script to read each line of the MySQL dump file
and generate output into a new file which is to be the load file. Perl is very
powerful, was designed for just this type of thing (read a line, check for a pattern,
if it matches a certain pattern, print out this, otherwise print out that. Then repeat)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.