Importing Large SQL File in MySQL Database
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
We will go through one by one.
Method 1: Importing by Command line tools
This one is the most straightforward and easy way. There is actually two option.
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
