Importing large MySQL databases using BigDump
I’ve been using phpMyAdmin for a long time to manage my MySQL databases, it’s been a great tool to easily filter through data for things that I am building or to find a piece of data that I cannot find through the applications admin dashboard.
There are many reasons why you will need to export/import databases from one location to another, usually when you’ve been building something on your local machine that needs to be pushed to live or going from a development environment to a live environment for example.
If you’re trying to import a large database into phpMyAdmin you’ll probably notice the maximum file size limit. Depending on the server configuration these can vary massively.
If you have a good level of access to the server configuration, there are a number of ways you can increase the file size limit by increasing the upload_max_filesize and post_max_size parameters in php.ini.
If you are unable to increase these parameters you will need to look at a different solution, such as BigDump for importing database files that are larger than the phpMyAdmin limit.
BigDump is a script that staggers the import of large and very large MySQL files. BigDump is a script I’ve relied on heavily in my career and has got me out of those situations where I urgently need to import a large database file.
The first thing you’ll need to do is download the bigdump.php file and upload it to the server.
You’ll need to configure this file using your database credentials on lines 41-44.
// Database configuration $db_server = 'localhost'; $db_name = ''; $db_username = ''; $db_password = '';
And you’ll also need to put the MySQL file import in the same directory of the bigdump.php so that it can locate the file. Unfortunately, it cannot read .zip files but it will be able to locate SQL, GZ, or CSV files in the working directory.
If everything has been set up correctly you’ll be able to see the file without any error messages.
If you get an error that reads ‘At this place the current query includes more than 300 dump lines’. You need to change the way you’re exporting the MySQL file from phpMyAdmin.
To do this make sure you click on ‘custom’ from the export tab in phpMyAdmin.
and change the ‘Maximal length of created query to 300’
Now when you import the file you will no longer get the error message and the file will be imported correctly.
Remember to remove the MySQL database and bigdump.php file off the server once finished for security reasons.