Today´s piece of fine literature will be dealing with the import of large MySQL Databases.
As a practical example let´s assume you are about to move your Forum or Blog to a new hosting company. You would like to take all the existing posts, articles, attachments and whatever is stored in the database with you and import it to your new hosting account.

How would you go on about that ?
Yes, first thing you would do is back up the existing MySQL Database and download it to your PC. Usually this is done with some sort of MySQL management tool, such as phpMyAdmin. You got your backup downloaded alright, now it´s time to import it to the new database.

And this is where things might get dirty. I´ll give you an example of how big a MySQL Database might get, just take a look at the size of the backup of our Forum i did a few days ago:


Whoa, a hefty 1GB backup file. This is a backup of our vBulletin Database, in which all posts, topics and attachments are stored. Now, taking a backup of such a Database is a breeze, while importing it can be a pain up the *****, you know what i mean
Ok, no worries though. Most of todays hosting companies have some sort of Management Tool such as phpMyAdmin, which will let you import your database with the click of a button. You can just go to phpMyAdmin which can be found in cPanel or whatever management interface your host offers.


There it is, and when you click on the Link it will take you straight to phpMyAdmin. The inital screen of it will look something like this.


To the left you see all databases that belong to your account, so click on the one you would like your backup file to import to. That will open the Database and show you a list of tables and sort of an “options bar” at the top. I highlighted the options in the screenshot below.


And that is where the Import option is located. So do a left-click on it and it will take you to the Import-Screen which allows for uploading your backup file. And that is where all the possible trouble starts…


I highlighted a few things and wrote some explanation about them on that screenshot. The most important thing to pay attention to on the Import screen is the maximum filesize of the file to import. If your Database Backup is bigger than the maximum allowed filesize the import WILL fail, and the bigger your backup the more like phpMyAdmin is to time out. You can say that the bigger your backup is, the more difficult it gets to import it using phpMyAdmin. Worst case scenario being that your backup file exceeds the maximum filesize phpMyAdmin will handle. That maximun filesize i am talking about differs from hoster to hoster, some allow 2MB files, others go up to roughly 50MB maximum filesize that you would be able to import (if the script doesn´t time out because of the max_execution time being reached…that is).

What to do when the backup is bigger than the allowed maximum filesize ?

There are a few scripts available that allow to import even huge databases on servers with a hard runtime limit. The trick they use is that they import the database in smaller chunks, meaning they important only a certain amount of lines at a time. The first script i would like to introduce is Bigdump.

Bigdump does exactly what i said above, it imports your backup by executing a configurable amount of lines at a time. So no matter how big your backup is, it will import nicely with Bigdump. First thing we obviously need to to is download it from the author website which i already linked to a few lines above. So go download it and unpack the ZIP archive when finished downloading.

It contains only one file, called bigdump.php. Let´s open it with a text-editor, as we need to change a few details so it will work on your server. As i said, open it in a text-editor and you will see quite a few lines of settings that can be, have to and shouldn´t be changed. I´ll be focussing on the most important ones that will make Bigdump work on your server.

Let´s start with the Database configuration:


Ok, this isn´t really hard to fill out, just replace the default values with your MySQL access details such as Database Name, User, Password and Server. Not that hard, is it ?
Now to a more interesting section:

This is the part of the configuration where you will need to make some changes if the import with these default settings still fails, that means the import process times out due to the time limit for script execution being reached.

If that happens, then decrease the $linespersession value to something lower, i suggest 1000-1500 for testing. You might have to test a few times to find a working combination, though for most of the times i used Bigdump the default settings worked just fine.

Anything else in the config file should stay the way it is for a standard import, except you have some very strange collation such as koi8r for example. But as i will be only showing how an import with default settings work, let´s save the file for now and continue.

Good, we changed the Bigdump configuration, now it is time to upload Bigdump. And not only upload Bigdump, but also upload the MySQL backup file to the same directory you uploaded Bigdump to.
You also may upload the MySQL backup file through Bigdump later, but let´s do it this way for now.
So upload both to a directory and then open your Webbrowser and point it to your bigdump.php, e.g.
and you will see Bidgdumps Interface.


The highlighted file is the MySQL backup. Bigdump will identify  both, compressed and uncompressed MySQL backups. See the blue Start Import Link in the above screenshot ? Let´s left-click it and see what happens.


Yay, it seems to work. It will now import small chunks, and you are given a lot of details on what´s going on there, how many lines have been imported, how many are left, aso. Once Bigdump is finished it will tell you at the bottom of the screen and the Database has been successfully imported then.


According to that the import has been finished successfully and you now should check your Database with phpMyAdmin and see if everything is there, e.g. all tables have been imported, etc. Not a bad idea to optimize the Database at the same time.

That´s it, Database imported and found a way around the possible conflict with the maximum filesize limit. If you need help on getting Bigdump to work for just drop me a message and i´ll try to help you out as much as possible. If you found any typos in this tutorial…..keep them. They are yours. And as english isn´t my native language i am sorry if i screw up grammar and stuff sometimes, but i hope it was easy enough to understand. If you have any questions or comments, suggestions or just wanna mock at me then feel free to reply here.