CODEWITHSUNDEEP
phpmysqlsqlcommand
Jason Kuah
Jason Kuah

Reputation: 57

cannot import too large sql file to mysql

I have a 28 MB sql file need to import to mysql. Firstly, i'm using xampp to import, and it fails, and so i change my max_file_uploads, post_size(something like that)in php.ini-development and php.ini-product to 40 MB, but it still show "max:2048kb" and import fail again.

From research, i've learned to import by using mysql.exe, so i open mysql.exe and type the command line(msdos) below:

-u root -p dbname < C:\xxx\xxx\sqlfile.sql

but still failed again and again.....

what the problem is? xampp? or my sql setting?

Upvotes: 0

Views: 9110

Answers (6)

Sudam Ranasinghe
Sudam Ranasinghe

Reputation: 11

Go to the php.ini file in the xamp click the config in the apach. Then in the text file type these commands.you may vary these values according to your file size of the database

post_max_size = 900M
memory_limit = 1024M
max_execution_time = 6000
max_input_time = 6000

Upvotes: 0

Rod
Rod

Reputation: 101

I found the only solution was to log in to MySQL from the command line and use the 'source' command:-

1) cd to the directory containing your SQL file for import, then log into MySQL:

#> mysql -u YOURUSERNAME -p -h localhost

2) use MySQL commands to import the data:

#> use NAMEOFYOURDB;

#> source NAMEOFFILETOIMPORT.sql

This also feeds back info about progress to your terminal, which is reassuring.

Upvotes: 0

The Marlboro Man
The Marlboro Man

Reputation: 971

How large is your file?. You might as well do it from a console:

mysql -u##USER## -p ##YOUR_DATABASE## < ##PATH_TO_YOUR_FILE##

Do that without executing your mysql.ext file: just "cd" right into the directory and try the command.

It will ask for your password and start importing right away. Don't forget to create the database or delete all tables if it's already there.

I always found this approach quick, painless and easier that rolling around with php directives, phpmyadmin configuration or external applications. It's right there, built into the mysql core.

Upvotes: 1

Sal00m
Sal00m

Reputation: 2916

Try this:

mysql -uroot -p --max_allowed_packet=24M dbname

Once you log into the database:

source C:\xxx\xxx\sqlfile.sql

I think that you should be able to load your file

Upvotes: 3

Ripa Saha
Ripa Saha

Reputation: 2540

I also fetched the similar problem. So after that I also conclude , large sql file will never be imported to mysql. It will always give timeout error.

Then I found a solution.

There is an software Heidisql.

follow below steps:-

1) download the software.
2) then install the software
3) create new session in Heidisql and open the session
4) then go to Tools -> Load SQL File -> Browse.

That's it. This solution works best for me.

check the link here

Upvotes: 0

user12933
user12933

Reputation: 9

You should increase max_allowed_packet in MySQL.

Just execute this command before importing your file:

set global max_allowed_packet=1000000000;

Upvotes: 0

Related Questions