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.
Importing by Command line tools
Increasing PHP file size limit
Compressing the SQL file and importing it
Method 1: Importing by Command line tools
Option 1: from the directory where your backup file is:
$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)
Option 2: from the directory where your backup file is:
$mysql -u username -p -h hostname
[enter your password]
Method 2: Increasing PHP file size limit
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.
Method 3: Compressing the SQL file and importing it
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
Top comments (0)