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

Aug 5, 2008 / By Keith Murphy

Tags: , , , ,

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.

6 Responses to “How Do You Edit a dump/exp/script File?”

  • In general, you have to be careful opening dumpfiles in editors of any kind. You can easily run into issues where special characters or binary data gets mangled. Strictly speaking, these are NOT ASCII text files.

    For most cases, there are command line switches to mysqldump (e.g. –replace) that can be used to get the data in the format you want.

    Another trick is to make two passes at mysqldump – the first one grabbing structure only (–no-data –routines –events), and the second one with –no-create-info. You can safely edit the first file even if you have blobs in your data model.

    But the problem I run into most often is that the dump file contains “USE DATABASENAME” at the head, and if you don’t remove it, you could end up accidentally restoring your database to an old copy.

  • Sheeri Cabral says:

    The great thing about sed being a stream editor is that you can pipe the output of mysqldump directly into sed and then save the file….

    so instead of

    mysqldump > file.sql
    you can do
    mysqldump | sed ‘s/^INSERT/REPLACE/’ > file.sql

    (^ represents the beginning of the line, and I didn’t put the global switch “g”. Without “g” it will only replace the first instance per line, which is fine in this particular example…and yeah, people should definitely read the sed manual!)

  • Sheeri Cabral says:

    Gregory — check your mysqldump options, you can stop mysqldump from throwing in “use databasename”.

  • LenZ says:

    The MySQL distribution actually ships with a small commandline tool that does exactly that – take a look at the “replace” command. I blogged about it some time ago:

    http://www.lenzg.org/archives/149-A-hidden-gem-in-the-MySQL-distribution-replace.html

    So in your case, you could do the following:

    $ replace “INSERT” “INSERT REPLACE” — file.sql

  • [...] Rusty Razor Blade’s Jon Haddad has a quick tip on executing MySQL queries within Vim. Along the same lines, Pythian’s Keith Murphy and his readers offer different ways to edit a dump/exp/script file. [...]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>