How Do You Edit a dump/exp/script File?

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

or

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.