Solved

Mysql mysqldump script with find and replace.

Posted on 2011-02-16
3
492 Views
Last Modified: 2012-06-21
I need to create a linux shell  script where by i would  run the script with these arguments:
scriptname -dbnumber -olddatabase -newdatabase
Steps:
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!
 

0
Comment
Question by:Louis Capece
3 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34915505
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:

http://www.sedtutorial.com/

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
0
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 34920013
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:
http://www.gnu.org/software/sed/manual/sed.html#Limitations

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)

0
 
LVL 21

Accepted Solution

by:
JesterToo earned 500 total points
ID: 34920914
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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question