Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Mysql mysqldump script with find and replace.

Posted on 2011-02-16
3
Medium Priority
?
514 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 27

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 22

Accepted Solution

by:
JesterToo earned 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

886 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