Skip to end of metadata
Go to start of metadata

I happened to back up a MySQL database into one huge dump SQL file, which was 123G Bytes. When I tried to restore it to another computer, I found the processing was very slow (especially the enable key step for a table). I decide to abandon some tables, but I found for such a large file, there is no good way to edit it. Even for tasks like just deleting the first line or last line are very difficult. So I decide to split the file. After goggling a while and testing some of them, I could not find a good tool to achieve this. I also tried csplit (which have a line limit of 2048), and some script written in sed, ssed and awk, but no luck. Finally I decided to write a small piece of python code to split the huge dump file into small pieces, and it rocks.

The idea was simple. I just try to split the input file by the patterns like

--
-- blah blah blah
--
  File Modified
File
May 26, 2011 by Shu, Anhei

Usage:

dumpSplitter -i inputFileName -p prefix -n initSerial

Example:

dumpSplitter -i mydb.dump -p abc_ -n 1000

will read in mydb.dump and generate files abc_1000, abc_1001, ...

  • No labels

3 Comments

  1. Anonymous

    Ok, this is the most helpful script I've found on the interwebs to date.  Thanks so much! Full of awesome.

  2. Anonymous

    Here's another piece of open source software that will split MySQL dumps into smaller files.

    It is smarter than just splitting it into several files; it splits within extended insert queries but keeps overall MySQL syntax correct. Very powerful in case this python script doesn't cut it.

    http://rodo.nl/index.php?page=mysql-splitter

    Hope this helps!

  3. Anonymous

    Beautiful, worked like a charm on a 25Gb database with large blob tables