How Do You Edit a dump/exp/script File?
Aug 5, 2008 / By Keith Murphy
If you work with databases long enough, you run into a certain problem. You have a mysqldump file* of a table or a database, and you need to import it into your new database. However, you need to change something in the file first. Maybe the
INSERT statements need to be changed to
INSERT REPLACE. You fire up
vi and load the file, but when you go to search and replace,
vi runs out of memory and doesn’t complete the operation. Or maybe the dump file is just so big it won’t even load in the first place. What do you do in this situation?
Well, one simple solution is to use the
sed tool to modify the file. Sed actually stands for “stream editor”. The
vi editor would be considered a static editor in that it loads all of the file into memory at once. If you run out of memory, you are out of luck. With
sed there is a very limited amount of data in memory at any time because it streams the data “through”, manipulating it as it goes. So
sed can work with files that are huge, and only use a minimal amount of memory for processing.
The format of the search and replace also is similar to
vi's search and replace. For example:
cat file | sed 's/INSERT/INSERT REPLACE/g' > newfile
sed 's/INSERT/INSERT REPLACE/g' < oldfile > newfile
If you have more interest in
sed, here is a good sed tutorial. I’m sure there are other ways to do this, and I am curious to know what you do. Comment and let me know.
* Or, for some of those other DBMSs: an export, database script, or pg_dump file.
6 comments on “How Do You Edit a dump/exp/script File?”
Pingback: Log Buffer #109: A Carnival of the Vanities for DBAs