How to import large sql files into mysql table

I have a php script that parses XML files and creates a large SQL file that looks something like this:

INSERT IGNORE INTO table(field1,field2,field3...) VALUES ("value1","value2",int1...), ("value1","value2",int1)...etc 

This file is more than 20 GB (I tested the 2.5 GB file, but it does not work either).

I tried commands like:

mysql -u root -p table_name </var/www/bigfile.sql

this works on small files, say about 50 MB. but it does not work with a large file.

I tried:

 mysql> source /var/www/bigfile.sql 

I also tried mysqlimport, but this does not even process my file.

I get an error

 ERROR 2006 (HY000): MySQL server has gone away 

Occurs approx. 30 seconds after starting.

I set allow_max_packet to 4 GB, but when checking with SHOW VARIABLES it only displays 1 GB.

Is there a way to do this without spending an extra 10 hours?

+6
source share
1 answer

Try splitting the file into multiple INSERT queries.

+2
source

Source: https://habr.com/ru/post/908754/


All Articles