Import larger SQL files into MySQL

Larger SQL files can be imported into MySQL using different ways. I am listing 3 common ways here.

Using PHPMyAdmin by increasing upload_max_filesize:

Most  commonly used tool for MySQL is PHPMyAdmin but problem is that PHP have upload file size limit so files larger than that can’t be uploaded but in PHP Configuration file (php.ini), there is  “upload_max_filesize” limit that can be changed. So if you want to import larger file using PHPMyAdmin then you need to modify php.ini file.

You can find php.ini file path through phpinfo(). So go to php.ini, and find upload_max_filesize you will find something like:

   ; Maximum allowed size for uploaded files.
   ; http://php.net/upload-max-filesize
   upload_max_filesize=2M

Replace upload_max_filesize value with file size larger than your SQL file that you want to import. So if you want to allow file of 100 MB to be uploaded , then you should replace above line of code with:

   ; Maximum allowed size for uploaded files.
   ; http://php.net/upload-max-filesize
   upload_max_filesize=100M

Restart your server, most probably you will be using apache2. So if you are on Linux then use this command:
sudo service apache2 restart

If you are not on Linux and using some other package for PHP then restart your server accordingly. And now you should be able to upload bigger files hence you will be able to import bigger SQL files.

 

Using Command line:

Most simplest and my favorite to import  larger SQL files is command line usage because if you have access to command line then it can work almost everywhere. From your terminal or Command prompt based on Operating System (OS) you are using, execute following command:

mysql -u <username> -p<password> <DBName> < filename.sql

Here replace <username> with your DB username, <password> with your DB password, <DBName> with Database name and filename.sql with your SQL file’s path you want to import.  Please note that there shouldn’t be space between -p and password.

Using Desktop Tool:

Simply use a desktop based tool instead of PHPMyAdmin. If you are importing SQL file on a server where you can only use SSH to do anything or you are using it on a shared hosting then desktop tool is not always an option for you unless your DB is accessible from remote connection.

However if you can use Desktop based tool then you should desktop based tool as they are much more flexible and importing large SQL file is not a problem. So in this regard, there are plenty of free and paid tools. Navicat for MySQL is a nice tool but it is paid and costly. So probably you will be interested in free GUI tools like MySQL workbench.

If you guys found something unclear or some mistake in the content then feel free to let me know in comments section.