2 ways to Import large SQL files to MySQL database

when we are working with MySQL database it’s very obvious that you need to import and export the database at several times. when the .sql file that you need to import is of small size, the process of import and export is very fast. But when the database file (.sql) file that you want to import is of large size of 10Mb, 100Mb or even large size than that 800Mb it becomes very time taking the process and sometimes even the browser crash.

On localhost, when we use a MySQL database and use XAMPP server, we will be using Phpmyadmin. it provides a very simple interface to interact with the database and perform different operations. we use phpmyadmin to import or export the database.

on the Server side, when we use cPanel we get the same Phpmyadmin to interact with the database and import and export database. By default, the maximum upload size is (Max: 2,048KiB).

If the database file (.sql ) is large size you will get error :

 “the file size exceeded the maximum size permitted by your PHP configuration”

So here are the 2 ways to upload the large SQL files to MySQL database

1. increasing the PHP upload limit in XAMPP / WAMP

the first method is to increase the limit of file upload for PHP in XAMPP / WAMP. This method of increasing the PHP file upload limit is good for middle size files eg. files of size 10Mb to 100Mb or sometimes even files up to 200MB because very large files can hang up the browser.

so follow the below steps to increase the php file upload size in XAMPP :

1. find a file named as php.ini, it can be found on your computer c drive: “C:\xampp\php\php.ini ” or sometimes here: “C:\xampp\apache\bin\php.ini  “

2. hence open the php.ini file and search for “upload_max_filesize“. We can CTRL+F to search for the keyword upload_max_filesize, below is the attached screenshot. Hence we can increase the upload size to whatever we want. So In our case, I increased it to 56MB
upload_max_filesize =56M .
certainly, It is advised not to increase the upload_max_filesize to very high which can ultimately make sometimes system/browser to crash.

3. next search for “post_max_size”, make sure to check your up and down radio button if you do not find the variable post_max_size.
increase the value more than or equal to upload_max_filesize.
In my case, I increased to 128 MB. post_max_size =128M.

5. restart XAMPP Apache and MySQL and reload phpmyadmin

That’s all now you can import the files of the large size up-to limit set above.

Likewise to increase the php file upload size in WAMP

similarly, follow the above steps and in php.ini file & search for: “memory_limit” and increase it to 1024M.
probably the memory_limit default value is 128M, increase it to as per required by you. In my case, I increase it to 128MB

memory_limit=1024M

2. Another Method is to import the very large database SQL files through command prompt (CMD).

certainly, this method is very helpful when you have a very heavy SQL file to be imported to any database, even if the SQL file is as large as 1GB the import is done more than 100 times faster than importing it through phpmyadmin.

Follow the below steps to import very large sql file through cmd :

1. first of all, we need to open the command prompt in administrator privilege.
2. furthermore, change your location to “C:\xampp\mysql\bin>mysql“. run command: ” cd C:\xampp\mysql\bin>mysql “
3. after that you need to run this command: “mysql -u {DB_USER} -p {DB_NAME} < path/to/file/ab.sql ” where you have to enter your database user_name and database name. In my case my
   DB_USER : root && my
   DB_NAME: vintagehome && we need vh_backup.sql file to import with the complete path.

finally merging both 2nd and 3rd point the whole syntax is :

“C:\xampp\mysql\bin>mysql -u {DB_USER} -p {DB_NAME} < path/to/file/ab.sql “

here is the code That I execute for importing SQL file:
my database name DB_NAME: vintageh_vintagehome
user name DB_USER: root
DB password: root
SQL file name: vh_backup

“mysql -u root -p vintageh_vintagehome < D:\downloads\sql\vh_backup.sql“

furthermore, the password of DB user is required. In my case, the password: root.

above all below are the images of the steps:

the 2nd method is the fastest way to import the long sql files.

I hope you understand the above example if there is an issue comment here and one of us will help you. thank you for reading.

This blog is developed by Team Skydevelopers