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.

Such as:

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]

use databasename;
source backupfile.sql

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
post_max_size
upload_max_filesize

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