Solved

Mysql mysqldump script with find and replace.

Posted on 2011-02-16
3
506 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
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. . .
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 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