bigdump

BigDump.php – Importing LARGE MySQL databases

This 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. 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 repeat the process.

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, but it’s a dodgy fix.

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

Shortt URL for this post:

2 comments

  1. Pingback: Working and parsing CSV text files & PHP

Leave a Reply

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