Solved

Mysql mysqldump script with find and replace.

Posted on 2011-02-16
3
487 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 4 169
MySQL Init Waits 25 100
html input clean up 3 55
How to Generate log or file on inserting duplicate records in mysql 6 44
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

777 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