Editing Large MySQL Exports with Sed

Every once in a while you have a 12GB MySQL export and you need to edit one line. Or at least I do. Let's imagine I forgot to skip a table on export and don't want to repeat the 12GB export. That would never happen, but let's imagine.

If you've ever tried to open a 12GB file in your favorite text editor, you've probably found it's no longer your favorite text editor. Suddenly your new favorite text editor is whatever can open that file without crashing, and ideally taking less than 20 minutes. Your new favorite text editor is Sed.

Sed is an old Unix tool, which means the top search results for it return a long list of arcane syntax without telling you how to do anything practical. "Sed" is short for something you don't care about because you just need to quickly edit a huge text file. So let's focus on the one thing we want to do, which is remove the INSERT line for the table we should have skipped on export:

cat original_export.sql | sed '/^INSERT INTO `problemtablename/d' > filtered_export.sql

That will give you a filtered_export.sql file that's only 11.5GB, with the problematic INSERT statement removed. With that problem solved, you can now find dozens of tutorials on exactly how that worked and why Sed is a super powerful tool you should spend days learning in more depth. Or you can go back to your regularly scheduled text editor. I recommend the latter.

Code Process

Read This Next