Import large MySQL database (phpMyAdmin limitation)

Submitted by eangkasa on Tue, 10/06/2009 - 21:15

I have encountered this issue about a week ago, when i'm exporting my database through phpMyAdmin tool, the export works very well and the output file is up to 10 MB in size. The main issue comes when i would like to import it back using phpMyAdmin, but guess what happened, a timeout occured.

 

I will kindly break it down for you, there are 2 limitations surrounding phpMyAdmin from importing a very large MySQL database:

1. A time out is divided into a server timeout and browser timeout. This basically means when a script is being executed for too long that it has eventually passed the time limit either on the server and/or browser, the script will cease to run. Simply put, the larger your file is, the longer it takes to be executed, the bigger chance it fails.

You could actually bypass the browser timeout by modifying the about:config setting if you are on Firefox, unfortunately for server timeout, if you are on shared hosting like me, you are unlikely to get access to modify the server config.

2. File size limit on certain web hosting companies. Normally, i would say a 10 MB filesize limit is being implemented by most web hosting companies, if your MySQL filesize is more than that, then you're out of luck. Fortunately, my hosting company (BlueHost) is generous by allowing me to upload up to 50 MB filesize.

Simply put, my export process failed due to the first constraint of timeout mechanism, presumably a server  timeout in my case. After hours of researches, i came across this amazing script called BigDump. I would strongly recommend it as it's practically easy to use, straightforward and works absolutely perfect for me.

 

8 steps on how to use BigDump to import your MySQL database file:

1. Download the bigdump.php file from the link i gave you above
2. Open the file and adjust the database configuration according to your database setting
3. Create a working directory called dump and modify the permission to 777
4. Transfer the bigdump.php and yourdumpsqlfile.sql to the working directory using FTP
5. Execute the bigdump.php via URL such as http://www.yourwebsitename.com/dump/bigdump.php
6. A screen shall come up and you will be able to select the .sql file which you uploaded just now via FTP
7. Please wait until the script finished, and do not close your browser till it's done.
8. Remove the whole dump directory from your server.