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. ((Yes, you Perl people, it’s in PHP. But it’s not written by me. So on balance turns out more elegant.))
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:
avi@jup-linux2:~$ cd www
avi@jup-linux2:~/www$ mkdir bigdump
avi@jup-linux2:~/www$ chmod 777 bigdump
avi@jup-linux2:~/www$ cd bigdump/
avi@jup-linux2:~/www$ wget -q http://www.ozerov.de/bigdump.zip
avi@jup-linux2:~/www$ unzip bigdump.zip
avi@jup-linux2:~/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! ((Those permissions aside – anyone can execute whatever SQL they like with your credentials through this page. Seriously, not on the internet!))
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 2Mb ((Or whatever’s smaller out of upload_max_filesize and post_max_size in your php.ini)), we can upload through the web browser, else we need to upload or link our sql dump to the same directory as bigdump:
avi@jup-linux2:~/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.