Call us
Dec 05 2007

How to import a large dump file into MySQL

Tutorials 4 comments

If you work with MySQL and PHPMyAdmin regularlly, you will have no doubt seen the import feature in PHPMyAdmin. Basically this feature allows you to import a MySQL dump file straight into your database, similar to restoring a backup etc.

There is a limit set by PHPMyAdmin of 2Mb though, so you can’t import files over that size. You can however "Zip" the dump file up because it can handle zip and gzip files. Here was my problem though. I have a database that I wanted to import nearly 100k rows of data into. As you can imagine, the uncompressed dump file was way over 2Mb, so I zipped it up and tried the import.

For some reason I could not get PHPMyAdmin to import this zipped file. It kept on showing a blank screen after starting the import, and no records ever got imported.

After a bit if head scratching and searching I came across a text file splitter program. This program takes a text file and splits it into however many files you want. So all I needed to do was install this program and split my dump file into files below the 2Mb limit!

I split my file into 4 files and imported them straight into MySQL one after the other. You can download this freeware program from here.

Tagged :

Related Posts

4 Responses to “How to import a large dump file into MySQL”

  1. Henry Says:
    May 23rd, 2008 at 2:56 pm

    Hmmm… Why does everyone seem to love PHPMyAdmin when it can’t even import more than 2MB data? It’s useless and frustrating!

  2. Ben Says:
    May 23rd, 2008 at 3:15 pm

    Hi Henry, I agree. I have since found a script called bigdump that will let you import massive files into MySQL.

  3. Henry Says:
    May 23rd, 2008 at 3:54 pm

    Hi Ben

    Yes, I saw the BigDump script. I didn’t really want to resort to that - it seemed slightly insecure.

    I also had the blank screen issue - The upload would complete (I could see it counting up in Opera), then after several minutes thinking it would present the blank screen. On investigation it would seem to have given up after just getting started with a nice LOCK TABLES without UNLOCK TABLES to make things even worse!

    Solution:
    1. Get your host to increase the max upload size - I can now do up to 16MB.
    2. Check the net_buffer_size setting on the server. Mine is set to 16,384. When you dump, include the setting “–net_buffer_size=16834″ or whatever your settings are. This cleared things up nicely.

  4. Ben Says:
    May 23rd, 2008 at 5:02 pm

    Yes it is a bit unsecure but I just deleted it after I’d used it. I don’t really need to upload large files now, it was a one off really.

Leave a Comment