Splitting massive MySQL dumps

As I posted yesterday, I have a massive MySQL dump to import. I tried BigDump, but one of the tables kept producing errors and so BigDump would exit. I don’t need the whole db imported, so I wrote this to split it by table. It produces a new sql file for every table it finds, numbered sequentially so if you process them in alphabetical order it’s the equivalent of the whole dump. USE statements get their own files in the same sequence.

#! /usr/bin/perl

use strict;
use warnings;
use 5.010;

my $dump_file = $ARGV[0];
&usage() if !$dump_file;

say "using ".$dump_file;

my ($line, $table,@query, $file_number,$file_name);
my $line_number = 1;
my $find_count = 0;

open(DUMP_IN, "< $dump_file");
                my $line = $_;
                if (/^USE\s.(\w+)./){
                        say "changing db: ".$1;
                        $file_name = &make_file_name("USE_$1", "$find_count");
                        &write_USE($file_name, $line);
                }elsif (/^-- Table structure for table .(.+)./){
			## If the current line is the beginning of a table definition
			## and @query is defined, then @query must be full of the previous
			## table, so we want to process it now:
                        if (@query){
                        $file_name = &make_file_name("$table", "$find_count");
                                open(OUTPUT, ">$file_name");
                                                print OUTPUT $_;
                                close OUTPUT;
                                undef @query;
                        $table = $1;
                next unless $table;
                push @query, $line;

close DUMP_IN;
say $line_number;

## Subroutines!
sub write_USE() {
        my($filename, $line) = @_[0,1];
        open (OUTPUT, ">$filename");
        print OUTPUT $line;
        close OUTPUT;

sub make_file_name() {
        my ($type, $number) = @_[0,1];
        $number = sprintf("%05d", $number);
        return $file_name;

sub usage() {
        say "Error: missing arguments.";
	say "Usage:";
	say "$0 [MYSQL_DUMP]";
        exit 1;

A small downside is that this replaces my 2.5Gb file with about 1800 smaller ones. A scripted importer is to follow.

Massive dumps with MySQL

hurr. *insert FLUSH TABLES joke here*

I have a 2.5GB sql dump to import to my MySQL server. MySQL doesn’t like me giving it work to do, and the box it’s running on only has 3GB of memory. So, I stumbled across bigdump, which is brilliant. It’s a PHP script that splits massive SQL dumps into smaller statements, and runs them one at a time against the server. Always the way: 10 lines into duct-taping together something to do the job for you, you find that someone else has done it rather elegantly.1

In short, we extract the directory to a publicly http-accessible location, stick the sql dump there and tell it to go.

In long, installation is approximately as follows:

[email protected]:~$ cd www
[email protected]:~/www$ mkdir bigdump
[email protected]:~/www$ chmod 777 bigdump
[email protected]:~/www$ cd bigdump/
[email protected]:~/www$ wget -q http://www.ozerov.de/bigdump.zip
[email protected]:~/www$ unzip bigdump.zip
[email protected]:~/www/bigdump$ ls
bigdump.php  bigdump.zip

Where ~/www is my apache UserDir (i.e. when I visit http://localhost/~avi, i see the contents of ~/www). We need permissions to execute PHP scripts in this dir, too (which I have already). We also need to give everyone permissions to do everything – don’t do this on the internet!2

Configuration involves editing bigdump.php with the hostname of our MySQL server, the name of the DB we want to manipulate and our credentials. The following is lines 40-45 of mine:

// Database configuration
$db_server   = 'localhost';
$db_name     = 'KBDB';
$db_username = 'kbox';
$db_password = 'imnottellingyou';

Finally, we need to give it a dump to process. For dumps of less than 2Mb3, we can upload through the web browser, else we need to upload or link our sql dump to the same directory as bigdump:

[email protected]:~/www/bigdump$ ln -s /home/avi/kbox/kbox_dbdata ./dump.sql

Now, we visit the php page through a web browser, and get a pretty interface:

BigDump lists all the files in its working directory, and for any that are SQL dumps provides a ‘Start Import’ link. To import one of them, click the link and wait.

  1. Yes, you Perl people, it’s in PHP. But it’s not written by me. So on balance turns out more elegant. []
  2. Those permissions aside – anyone can execute whatever SQL they like with your credentials through this page. Seriously, not on the internet! []
  3. Or whatever’s smaller out of upload_max_filesize and post_max_size in your php.ini []