SQL Dump File SplitterWednesday, 24 January 2007
Scenario: You are a conscious user and you back up your site’s database every once in a while. And you sit smiling and content knowing that your precious data is safe. Just to discover after disaster strikes that you can’t restore the contents of the SQL dump file because of upload size limitations or because phpMyAdmin would run out of memory while decompressing the gzip / zip file.
In these cases there are two solutions at hand: if you have console access you can login and import the plain SQL file (which you previously uploaded via FTP) by typing a command like “mysql -uusername -ppassword < dumpfile.sql” or if you only have access to phpMyAdmin you can open up a text editor and split the SQL file into smaller parts that can be uploaded sequentially. The problem si that if you have a larger dump file this can be a reeeaaaly time consuming job.
Today I encountered a problem like this. And since I had no console access and I was in no mood to lose a few precious hours splitting up a 40 meg file by hand I googled for “sql splitter” and I found a program called SQLDumpSplitter 2. Unfortunately the author’s site was not working (and since I don’t know any German I couldn’t really figure out why) but I found an alternative download on a dutch web developer forum. And since that forum required to be a member to be able to download I thought I should offer yet another alternative download location without all those registration related mini headaches. Here on this site…
Update (almost 8 years later on November 7, 2014)
Since this is by far the most popular post on this blog, I believe it deserves an update.
If you’re having problems with SQLDumpSplitter 2 please have a look at the following resources (picked from your super helpful comments):
- BigDump: Staggered MySQL Dump Importer - Imports large and very large MySQL dumps (like phpMyAdmin dumps) even through web servers with hard runtime limits and those in safe mode. The script imports only a small part of the huge dump and restarts itself. The next session starts where the last one left off.
- SQL splitter - A PHP script to split your SQL file into an array. Note that you’ll probably need PHP installed locally and configured pretty loosely (with high memory limit) to be able to process large SQL dumps with this script.
Second Update (December 28, 2018)
So I’ve been hosting this file for almost 12 years now and this morning I received a comment:
There’s an update for SQLDumpSplitter! Which is now multiplatform!
Also while checking Philip’s site I noticed that he mentions that I’m still hosting the program which he wrote in 2002(!). I consider this a nice nod as this blog of mine might not be the most active for some years now but it sure is persistent. :-)