SQLDumpSplitter : Splits Huge MySQL Database File into Smaller Chunks

Earlier this week, this website ran into a problem of a database mess and we had to restore the database from backup. Since backend was inaccessible, the restoring was done directly through phpMyAdmin interface. When we tried to upload the 60 MB SQL dump, phpMyAdmin simply failed because there were too many SQL queries to be carried out. So we had to split the huge database file into smaller chunks.

For splitting large SQL dump files into smaller ones we used SQLDumpSplitter. This tool is a life saving tool for bloggers and web site owners. It is portable and is available for Windows, Linux and macOS. When you want to split the SQL file, you can launch SQLDumpSplitter and select the SQL file. Then you have to choose the size of the smaller files. Default value is 2 MB but you can choose anything down to 100 kilobytes. I have found through experiment that 2 MB is perfect for quickly uploading to phpMyAdmin.

SQLDumpSplitter

When you click on Split button, it creates a sub-folder with the same name as the SQL file and places all the smaller SQL files inside it. These files are named with a number starting from 0. You have to begin importing these small SQL files in the increasing order of this number. For example, if the files are dump_0.sql, dump_1.sql, and dump_2.sql. Then you have to first upload/import dump_0.sql then dump_1.sql and finally dump_2.sql. Using a different order will cause problems with SQL queries.

SQLDumpSplitter

SQLDumpSplitter is not just another file splitter tool. It actually parses SQL statements and ensures that they are not corrupted when splitting the files. In case of a big INSERT statement, it can split it across two files without changing the overall effect of the original statement from the large source SQL dump file.

You can download SQLDumpSplitter from https://philiplb.de/sqldumpsplitter3/.