Edit huge sql data file

Question

I have a 23GB file and I would like to edit the 23rd line, but I have only 200 MB RAM available on the server. I do not want to open the file entirely because I have left only 20GB available disk space.

How can I do this. I tried to use head, tail sed but it seems it creates a temporary file. Is it possible to do it without a temporary file?

Answer 1

The solution is to edit the file with a hex editor. Hex editors are built to handle huge files, even whole disks and partitions.

You may find hexedit (ncurses based) or ghex (Gnome/Gtk based) useful. They are common utilities, therefore you will most probably find them in your distributions's repo.

All hex editors I have used, use a twin panel view with the left panel showing the bytes of the file in Hex, and the right panel trying to show an Ascii representation when that is possible.

In order to find and edit your 23rd line:

sed -n '23p' my_huge_dump.sql : Will print the contents of this line
sed -n '23p' my_huge_dump.sql | od -A n -t x1 : Will print the contents of this line in hexadecimal format.

or open the file with less -N my_huge_dump.sql and view the contents of line 23. (-N in less enables line numbering)

Now, knowing the content of the 23rd line:

• If the text of this line is somewhat unique and different from surrounding lines, you may find it from the right (ascii) panel and navigate to this line with the arrows. In hexedit you use the Tab key to move between the Hex and Ascii panels. In gHex you can use your mouse as well. You may also search for the string you're interested: Move to the Ascii panel and press / in hexedit or use the menu in gHex.
• If the line you want to edit has similar contents to other lines and you can't find it in the ascii panel, then you must count the "newline" separators to find the 23rd line. New lines (LF) are represented as 0A in hex. In the ASCII panel, new lines are represented as dots .

Then assuming you found the line you want to edit, you have the following options:

• Hopefully, the new content of the 23rd line is shorter or equal in length to the existing content (so you won't need to grow and move the whole file). In this case, you have to enter the Fill-mode i.e. the mode in which you overwrite existing content typing over the old text. This is the default mode in both gHex and hexedit. Move to the location you want to edit and start typing. Pressing Backspace will undo your changes. If the new content is shorter than the existing, you may fill up the line with spaces to avoid truncating the file.
• If the new content is longer than the existing one in this line, then you have to enter the Insert mode. You can do that using the Menu in gHex. In hexedit you have to use the EscI keybinding. Then start typing and the new characters will be appended in the current location.

In the first case, it is guaranteed that the editing and saving of the file will be instantaneous since an in-place edit will happen. In the later case, I'm not sure how the growing in size and the moving of following bytes will be handled, but I hope the filesystem uses a larger non-continuous block to move some of the contents and not move the whole file.

If you're happy with your changes, save the file:

• Use the menu in gHex
• Use Ctrlx in hexedit and answer (Y)es when questioned about whether to save the changes.

Always make sure you have a backup in place!

EDIT: I found out that gHex isn't suitable for your situation, since it tries to load the whole file in memory. hexedit will serve you fine. However, if you want a graphical editor like gHex, but with partial file loading capabilities, you may try wxHexEditor. Check also the Comparison of Hex editors page in Wikipedia.