Import a large SQL file in PHPMyAdmin

Quick links

When wondering how to be able to import a larger SQL file in PHPMyAdmin, the solution is pretty easy, to allow it through the standard phpMyAdmin sql import - which can also be used to import a csv in phpMyAdmin, or even to import an OpenDocument Spreadsheet in phpMyAdmin, simply by changing the input format in import menu.

PHPMyAdmin : A tool written in PHP intended to handle the administration of MySQL over the WWW

The limitation (for example, it can be 2Mb with XAMPP(Fig 1)) is most likely due to the php.ini configuration.

Import a large SQL file in PHPMyAdmin : Fig 1 : PHPMyAdmin with 2Mb limit MySQL import
Fig 1 : PHPMyAdmin with 2Mb limit MySQL import

Simply open it - for example, with XAMPP, by selecting the Apache Config menu entry "php.ini" (Fig 2). Search for the following values in the php.ini file : post_max_size, upload_max_filesize, and memory_limit.

XAMPP is an easy to install Apache distribution containing MySQL, PHP and Perl
Import a large SQL file in PHPMyAdmin : Fig 2 : XAMPP open the php.ini configuration file
Fig 2 : XAMPP open the php.ini configuration file

In case they are not in sync, the maximum allowed upload file size will be limited by the smaller value, keeping in mind the memory limit should be higher than the two other values, as memory will be needed to store and process the data, not counting other server activities.

These were the default values on my local server :


post_max_size=8M
upload_max_filesize=2M
memory_limit=128M

A possibility - to customize to specific needs - can be to update them to following values (Fig 3) :


post_max_size=128M
upload_max_filesize=128M
memory_limit=1024M
Import a large SQL file in PHPMyAdmin : Fig 3 : PHPMyAdmin with 128Mb limit MySQL import
Fig 3 : PHPMyAdmin with 128Mb limit MySQL import

Don't forget, after applying these changes, to restart the Apache server - on XAMPP, stop it (Fig 4) and start it again (Fig 5) (Fig 6) (Fig 7).

Import a large SQL file in PHPMyAdmin : Fig 4 : Stop XAMPP Apache server
Fig 4 : Stop XAMPP Apache server
Import a large SQL file in PHPMyAdmin : Fig 5 : Start XAMPP Apache server
Fig 5 : Start XAMPP Apache server
Import a large SQL file in PHPMyAdmin : Fig 6 : XAMPP Apache server starting
Fig 6 : XAMPP Apache server starting
Import a large SQL file in PHPMyAdmin : Fig 7 : XAMPP Apache server started
Fig 7 : XAMPP Apache server started

Just to show an example, in case only one value is updated, for example upload_max_filesize, it might end up with the following values (Fig 8) :


post_max_size=8M
upload_max_filesize=128M
memory_limit=128M
Import a large SQL file in PHPMyAdmin : Fig 8 : PHPMyAdmin with 8Mb limit MySQL import
Fig 8 : PHPMyAdmin with 8Mb limit MySQL import

Even though the maximum filesize has been setup to a higher value, it won't work as the maximum posting size is lower.

PHPMyAdmin will only allow 8Mb in the later case.

Supports the development of a number of open-source software projects, including the Apache web server

Similar articles

Comments (5)

Shane Ross, 2018-08-19
Uwielbiam czytać twoje treści, publikuj dalej
Samantha Murray, 2018-08-19
So machen wir das, großartig zu wissen
Raul Lamb, 2018-08-19
Adoro ler seu conteúdo, continue postando
Samantha Gonzales, 2018-08-19
Đã có thời gian tuyệt vời khi đọc thông tin này, hãy tiếp tục công việc tốt
pravitates, 2018-08-19
Velké množství informací, díky za sdílení

Leave a comment