Splitting large MySQL dump files

One of the frustrating things with working with MySQL is of importing large sql dump files. Either you get a ‘max execution time exceeded’ error from PHP or a ‘Max_allowed_packet_size’ from MySQL. In a recent task I needed to import a table of around a million records on a remote host, which quickly became an exercise in frustration due to various limitations on the server. SSH was of no help as changing the configuration files was restricted to the root user.

My last resort was to split the huge ‘INSERT’ statements into smaller size files. Manually doing the same is obviously time consuming and error prone; the only other solution is to write a small script to split the insert statements. This was to be a quick hack so the parsing code was to be of minimum complexity. Splitting a sql dump containing extended insert statements is somewhat complex so you need to have the dump file in a simple format – each insert statement should be on its own line as shown below.

INSERT INTO `dt_codes` VALUES ...
INSERT INTO `dt_codes` VALUES ...
INSERT INTO `dt_codes` VALUES ...
.
.

You can create the initial dump file in a simple format using the following command or you can do it using phpMyAdmin.

mysqldump -uUSER -pPASS --databases DATABASE_NAME --tables TABLE_NAME \
--extended-insert = FALSE > dump.sql

Once you have the big dump file you can use the following PHP script for splitting the file into smaller chunks. Change the variable ‘$max_lines_per_split’ to whatever value you think will work on your system.

<?php
 
set_time_limit(600);
ini_set("auto_detect_line_endings", true);
 
/* Number of 'insert' statements per file */
$max_lines_per_split = 50000;
 
$dump_file = "dump.sql";
$split_file = "dump-split-%d.sql";
$dump_directory = "./sql-dump/";
 
$line_count = 0;
$file_count = 1;
$total_lines = 0;
 
$handle = @fopen($dump_file, "r");
$buffer = "";
 
if ($handle) {
    while(($line = fgets($handle)) !== false) {
        /* Only read 'insert' statements */
        if(!preg_match("/insert/i", $line)) continue;
        $buffer .= $line;
        $line_count++;
 
        /* Copy buffer to the split file */
        if($line_count >= $max_lines_per_split) {
            $file_name = $dump_directory . sprintf($split_file, $file_count);
            $out_write = @fopen($file_name, "w+");
            fputs($out_write, $buffer);
            fclose($out_write);
            $buffer = '';
            $line_count = 0;
            $file_count++;
        }
    }
 
    if($buffer) {
        /* Write out the remaining buffer */
        $file_name = $dump_directory . sprintf($split_file, $file_count);
        $out_write = @fopen($file_name, "w+");
        fputs($out_write, $buffer);
        fclose($out_write);
    }
 
    fclose($handle);
    echo "done.";
}

Once the dump file has been split into smaller ones, you can gzip them to reduce the size further.

gzip dump-split-*


5 thoughts on “Splitting large MySQL dump files

  1. And why you did not use mysql command line to import the file into database? mysql -h… -u…. -p…. database_name < filename.sql You said that you have ssh access, right?

  2. As I have said in the post, the sql dump being large, the command from SSH raised the ‘Got packet bigger than ‘max_allowed_packet’ bytes’ error and I did not have the permission to change the settings due to admin restrictions.

  3. This is also useful for backing up SQL dumps to google docs which has a char+line limit on uploads for non-enterprise users. Good work.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>