DEV Community

kpax-dev
kpax-dev

Posted on

1 1

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

Tiugo image

Modular, Fast, and Built for Developers

CKEditor 5 gives you full control over your editing experience. A modular architecture means you get high performance, fewer re-renders and a setup that scales with your needs.

Start now

Top comments (0)

Image of Stellar post

How a Hackathon Win Led to My Startup Getting Funded

In this episode, you'll see:

  • The hackathon wins that sparked the journey.
  • The moment José and Joseph decided to go all-in.
  • Building a working prototype on Stellar.
  • Using the PassKeys feature of Soroban.
  • Getting funded via the Stellar Community Fund.

Watch the video

👋 Kindness is contagious

Explore a trove of insights in this engaging article, celebrated within our welcoming DEV Community. Developers from every background are invited to join and enhance our shared wisdom.

A genuine "thank you" can truly uplift someone’s day. Feel free to express your gratitude in the comments below!

On DEV, our collective exchange of knowledge lightens the road ahead and strengthens our community bonds. Found something valuable here? A small thank you to the author can make a big difference.

Okay