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 Comments. Leave new
Hi Keith!
sed?
what about Perl?
https://datacharmer.blogspot.com/2008/08/editing-dump-file-from-airport.html
Cheers
Giuseppe
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.
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!)
Gregory — check your mysqldump options, you can stop mysqldump from throwing in “use databasename”.
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:
https://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. […]