BigDump.php – Importing LARGE MySQL databases

bigdumpThis post applies to people that are having problems importing a large database sql dump into their hosting provider’s MySQL server. Most hosting providers use the very popular PHPMyAdmin software for all their user’s online database related administration purposes. It’s visual, intuitive, and it negates the need to use cryptic MySQL commands. Best of all – it’s free!

A problem with most hosting providers is that they limit the maximum size of the imported .sql dump file. I’ve got one hosting account where the database import limit is 5MB and another where it’s 50MB. A problem arises, however, when one must import a large dump – which is something I encounter fairly regularly. On one occassion I had to move a 27GB forum from my own server to another, and on another occassion I had to move a large web site with a 6GB back end. Clearly this sort of import cannot be done via the ‘import’ command in PHPMyAdmin.

One option available to users that are restricted in such a manner is by using bigdump.php. What Big Dump does is stagger the import process in little chunks with the clever use of javascript meaning that the page literally reloads over and over with clever use of AJAX scripting; so it continues to import your dump file in little chunks (of a defined number of rows)… over and over and over until completion – even on servers with hard runtime limits or running in safe mode. In essence, it will import a number of rows, say 1 to a 1000, then pause… and then it will resume at row 1001 and continue to repeat the process.

Start of the big dump

You can upload your dump from the browser or run it direcly from your server.

A word of caution. A large dump may take considerable time and consume lots of server resources, so it’s possible that your import might be identified as ‘abuse’ by your hosting provider. In the first instance, it’s probably best if you simply upload your dump file to your server and contact appropriate support and have them import the file directly on the database server via the MySQL command line without tying up a http thread. Bigdump.php does give you the option of giving the script a rest between importing rows of data so the perceived CPU usage will be staggered over time.

dump finished

The screen upon completion of the dump.

 

You can download bigdump.php below (ZIP DOWNLOAD – 10KB) or visit the authors web site here.

First Name:
Your Email Address:
 




Download: Bigdump
Description: Staggered import of large MySQL Dumps.
Author:Alexey Ozerov
Category: PHP code
Date: January 2, 2010



If you liked this article, you may also like:

  1. Randomly select rows from a LARGE database with MySQL
  2. Working with CSV text files & PHP
  3. Disabling and Deleting WordPress Post Revisions
  4. Copy the contents of a directory from one location to another with PHP
  5. Populate a HTML Dropdown Box from an Array (or Database) using PHP
About Marty

is a passionate web developer from Sydney, Australia. He owns about 600 websites and makes a healthy living from working the web. As a day job, he works as a pilot for an international airline. Follow Marty on Twitter or Google+.

Comments

  1. that’s very useful and powerfull script ,thank’s :)

Trackbacks

  1. [...] enormous amount of server processing power so you may better using something like the browser-based BigDump.php. First [...]

Please leave a comment or question!

*