Mysql database import issue on centos 7 server

CentOS
#1

Hi All,

I have a mysql DB file of 34Mb size, when i try to import using mysql command, i am getting this error:

mysql -u  -p -h   < DB.sql
"ERROR 2006 (HY000) at line 491: MySQL server has gone away".

I found in google to change the max_allowed_packet" size in /etc/my.cnf file, even i give the size 1G, same issue repeating. When i try to import same DB on centos 6.6 server, it accepts fine.
Please suggest on this.

#2

Hi ,

If your are doing any dump or import operation in db , then add in /etc/my.cnf

[mysqldump]
max_allowed_packet=1024M

I think this will solve your problem.

#3

HI,

I have given max_allowed_packet=1024M, still same issue.

#4

To fix such error, you need to add these three variables to your my.cnf file.

max_allowed_packet = 64M wait_timeout = 6000 max_allowed_packet = 64M
#5

Hi Ravi,

in my DB wait_timeout is set to 28800 which is default value, when i update my.cnf file with the variables wait_timeout=6000, i am getting this error while going to mysql prompt.

I am not able to enter mysql prompt.

#6

I have update these variables under both [mysqld] and [mysqld_safe] in /etc/my.cnf, i am able to run the command mysql, but unable to import the DB file which is of 34Mb.

#7

have you added

[mysqldump]
max_allowed_packet=1024M

mysqldump??

#8

Yes, i have added mysqldump. still same issue.

#9

Try with add in my.cnf

interactive_timeout=1000
connect_timeout=1000

#10

Could you please check the MySQL error log and see what happens when you perform the import. You can copy/paste the error messages here.

#11

Hi,

we are using centos 7 and Database: MariaDB , there are no logs are generating in the time of importing .sql file.

#12

Hi sashi ,

can you please check the dump file with tail -f fle.sql , is it completely exported or not ??

that might be issue with incomplete export

#13

Hi Ashish,

I am able to import the same .sql in other centos 6 machine, i checked tail -f .sql file. it is the output:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

– Dump completed on 2015-09-25 9:51:24

#14

Hi all,

Try to use mysqldump -u (user) -p (database) < (backup file).sql

Thank you

1 Like