how to import the .sql file having size 150MB from terminal or phpmyadmin

Question

I am trying the upload the .sql file having size 150MB using terminal or phpmyadmin but giving the errors

this is what I did before the importing the file

in php.ini file

1)post_max_size = 20000M
2)upload_max_filesize = 20000M
3)max_execution_time = 50000
4)max_input_time = 50000
5)memory_limit = 20000M

& in /etc/mysql/my.cn

6)max_allowed_packet = 2G

& in the /usr/share/phpmyadmin/libraries/config.defalut.php

 $cfg['ExecTimeLimit'] = '0'; //to make it unlimited, this was 300 default

even after these many setting I am getting errors: when tried from the terminal as

 mysql -u root -p dbname < mydbfile.sql & then entered the password & got the

 error: ERROR 2006 (HY000) at line 23: MySql server has gone away

when I tried to import the database file using phpmyadmin after taking 3-4hrs it also results in the errors

like: No data received

is there any other way like reading the .sql file using the php & inserting into the database one by one. is it good way?

any idea what could be the problem?

Thanks in advance!

I know that phpmyadmin has it own upload file limit, but the mysql command should work. Maybe check the mysql max connection time? — We0, Jul 24 '13 at 08:00
@We0 I run this query : SHOW VARIABLES LIKE 'wait_timeout' & `interactive_timeout` & got 28800 — sandip, Jul 24 '13 at 08:07
For the error `No data received` try checking the path to the .sql file. For importing large files, check this [link](http://stackoverflow.com/questions/12425287/mysql-server-has-gone-away-when-importing-large-sql-file). — Dinesh, Jul 24 '13 at 08:09

score 2 · Answer 1 · answered Jul 24 '13 at 08:00

2

I think you should split the .sql insert commands and import them in multiple phases. Do check the "max connection time" setting.

answered Jul 24 '13 at 08:00

Vivek Sadh

the .slq file contains 7Lacks + records it will take lot of time, any other option? – sandip Jul 24 '13 at 08:09

score 0 · Answer 2 · answered Jul 24 '13 at 11:00

0

This may be because of max_allowed_packet

Change in the my.ini/my.cnf file. Include the single line under [mysqld] in your file

max_allowed_packet=500M

now restart the MySQL service once you are done. You can see it's curent value in mysql like this:

SHOW VARIABLES LIKE 'max_allowed_packet'

You can try to change it like this, but it's unlikely this will work on shared hosting:

SET GLOBAL max_allowed_packet=16777216;

answered Jul 24 '13 at 11:00

Sathish D

I run this query: SHOW VARIABLES LIKE 'max_allowed_packet' & got the result `Variable_name Value` `max_allowed_packet 16777216` already, I mean I set that already but the problem persists – sandip Jul 24 '13 at 11:47
Try to set it larger size say 500M. And try again. The size of the row to be inserted show be less than the max_allowed_packet – Sathish D Jul 24 '13 at 11:48
ok but I already set max_allowed_packet = 2G in my my.cnf file – sandip Jul 24 '13 at 12:19
Can you execute this SHOW VARIABLES LIKE 'max_allowed_packet' and paste the result? – Sathish D Jul 24 '13 at 12:22
I am sorry satishD but I think you didn't noticed my first comment on your answer, anyways I got result as: 16777216 – sandip Jul 24 '13 at 12:40
@sandip that is what my confusion was. 16777216=16M.... but you mentioned that it was changed to 2G where the result should be – Sathish D Jul 25 '13 at 06:18

2 Answers2