Importing Large SQL File
Sometimes we need to import large SQL files in MySQL.
Importing Large SQL File in MySQL Database can be a big headache time to time. There are several ways for this purpose.
$mysql -u username -p -h hostname databasename < backupfile.sql
Example: $mysql -u root -p -h localhost my_large_db < my_large_db.sql
If a password is set, it will prompt for password. Or you can directly provide password after -p, which is not a recommended way.
Make sure, your environment path variable is set of your MySQL (in my case: C:\laragon\bin\mysql\mysql-5.7.24-winx64\bin)
$mysql -u username -p -h hostname
[enter your password]
In this method, you have to increase “post_max_size” and also “upload_max_filesize”. First you have to locate your php.ini file. After locating, open your php.ini file in your favorite text editor.
Open your php.ini file.
Find following lines
and increase values to required size in MB. E.g.
post_max_size = 500M
upload_max_filesize = 500M
Then save the file and restart your apache or nginx server.
After import is completed you may want to restore original values for additional safety.
Compressing is an easy solution for Importing Large SQL File
This one is straightforward. Just compress your SQL file and import the file using phpmyadmin importer. File may be compressed (gzip, zip) etc. One thing is compressed file’s name must end in .[format].[compression]. Example: .sql.zip
Originally posted at LaravelPlug