Error importing large sql dump file

[bryan2019]

Hi,

It's been 4 days now but I cannot import my sql dump file (351MB)
I tried many times to import this to PHPMyAdmin but still receiving errors.
I think the file is to big.

IS changing the value of max_allowed_packet help importing file?? Where to find this??

Please help Admin!

[zakrpa]

did you try to upload file and import with mysql command?

create database and user with CWP then upload file with ftp or whatever you use.
go to folder where is sql file uploaded

example:

cd /home/USERNAME/public_html/

import command:

mysql -u USERNAME -p DB_NAME < FILE_NAME.SQL

[Administrator]

for bigger files its always the best to use the command line as zakrpa specified

[tomidjx]

for bigger files its always the best to use the command line as zakrpa specified

I'm trying to import a database (due to transferring the site to another server).
My database is actually very small, but I still get the error #1153 - Got a packet bigger than 'max_allowed_packet' bytes

mysql default config seems to be Max: 2,048KiB

How can i increase the max_allowed_packet? I cannot find this in /etc/my.cnf, and in php.ini neither.

I tried to import the database from terminal still it doesn't work, i get the same error #Got a packet bigger than 'max_allowed_packet' bytes

any help will be greatly appreciated.

[brijendrasial]

did u try this? change max allowed packet according to ur need.

mysql --max_allowed_packet=100M -u root -p database < dump.sql

[tomidjx]

did u try this? change max allowed packet according to ur need.

mysql --max_allowed_packet=100M -u root -p database < dump.sql

I did succeed and could finally import database.

Unfortunately i didn't document properly what I did, but, the solution was in php.ini. I did change a couple of things, I remember: post_max_size = 50M, upload_max_filesize = 50M, and I did it from command line, firstly I double checked in info.php  where was the relevant php.ini file.
I tried to edit mysql conf file to no avail.
Editing  php.ini  from command line did the trick.

Thanks i really appreciate any help, and ready to share any useful output. (unfortunately, i don't find easy to get it right with CWP)

[Administrator]

in cwp you have php.ini editor for this most commonly required changes.

[tomidjx]

in cwp you have php.ini editor for this most commonly required changes.

Yes i did look in it, unfortunately my trouble was that I couldn't find anything like max_allowed_packet. I ended up by trial and error, changing some params as specified aboce, and at the end it did work.

Somehow I wish this stuff was a little bit more documented, but I uderstand CWP is free and one must be prepared to spend time to find the way around and resolve issues. I love this panel, and I truly hope I can succeed to get it running properly. I'm not there yet though.

[brijendrasial]

You need to define max_allowed_packet in /etc/my.cnf file not in php.ini file.

[tomidjx]

You need to define max_allowed_packet in /etc/my.cnf file not in php.ini file.

that's the first thing I did, but made no changes. (restarted the services, rebooted etc.)
I did risolve only when I edited php.ini. I don't know why, but that's what happened.

ps.
in the /etc/my.cnf file there wasn't max_allowed_packet entry, I tried to add it to it, but made no change)

Thanks for support.

[Sandeep]

check this tutorial
https://www.mysterydata.com/how-to-import-large-mysql-database-via-command-line-on-cwp-vestacp-and-cpanel-server/