Larger SQL files can be imported into MySQL using different ways. I am listing 3 common ways here.
Using PHPMyAdmin by increasing
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
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
<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.
2 thoughts on “Import larger SQL files into MySQL”
You can also use SQL for a speedy import using LOAD DATA INFILE
Although you may have to change your MySQL config before this works.
I tend to import the data to an ‘import table’ then use SQL to do what ever I want with the data as I SELECT INTO other tables.
Paul, thanks for sharing another way