How to Import a Very Large SQL Dump with phpMyAdmin

Having trouble importing a large SQL dump through phpMyAdmin? This is a problem that often plagues users of shared web hosting, when they’re moving to a new host or restoring a backup. Since most shared hosting providers don’t offer shell access, you have to use a tool like phpMyAdmin to upload and load your SQL file.

Unfortunately, phpMyAdmin often has trouble processing large files. Sometimes it takes so long for the process to run that it times out, or other times the upload quota is simply too low.

Fortunately, there’s a simple solution: split your SQL file into smaller chunks. Here’s a command you can use under Mac OS X or Linux to do it automatically:

split -l 5000 ./path/to/mysqldump.sql ./mysqldump/dbpart-

The split command takes a file and breaks it into multiple files. The -l 5000 part tells it to split the file every five thousand lines. (You can tweak this value to find a good medium between fewer files and larger sizes.) The next bit is the path to your file, and the next part is the path you want to save the output to. Files will be saved as whatever filename you specify (e.g. “dbpart-“) with an alphabetical letter combination appended.

Now you should be able to import your files one at a time through phpMyAdmin without issue.

  • http://www.clickhelp.ca Dustin

    I noticed it is for Mac and Linux, any recommendations for the Windows users?

    • http://www.webmaster-source.com Matt

      I haven’t really used Windows much in recent years, but a quick Google search came up with this: http://www.hjsplit.org/

      It splits a file into multiple files by kilobyte rather than every Nth line, but it should still work. Just check the size of the original file and divide by how many files you think should work.

  • http://securewp4me.com app103

    HJ Split wasn’t really intended for splitting text files. It was made for splitting binary files and requires running a joiner executable to rejoin all the parts into something usable. Most common usage for it was sending a large attachment in multiple parts, attached to multiple emails, back in the days before all the free file hosting companies came onto the scene.

    For Windows users, you can try SQL Dump Splitter 2, which is a nice GUI app made specifically for the purpose of splitting SQL files: http://www.rusiczki.net/2007/0.....-splitter/

  • Themis Theotokatos

    Doesn’t really work, because if you split for every 5000 lines and at the 5000nd line there is a one half of an insert command then the other file can’t be imported since it’s missing the insert. You need to split between commands not between lines.

  • mony