I have a 47 GB MySQL dump of a single table:


I ultimately want it into PostgreSQL, but since I didn't figure out an easy way to transform the MySQL SQL to PostgreSQL SQL I figured, I will get it into MySQL and then write a small ETL script to do this.

I initially tried to just load it using MySQL WorkBench Data Import/Restore, but it was failing.

Now I've run split -l 2000 commonswiki-latest-image.sql and have 20 files of roughly 2 GB each, but it still fails with:

23:12:28 Restoring C:\temp\commonswiki\xaa.sql

Running: mysql.exe --defaults-extra-file="c:\users\jmurdoch\appdata\local\temp\tmpi_ltz8.cnf"  --host= --user=root --port=3306 --default-character-set=utf8 --comments --database=mediawikiimages < "C:\\temp\\commonswiki\\xaa.sql"

ERROR 2013 (HY000) at line 331: Lost connection to MySQL server during query

Operation failed with exitcode 1

00:54:17 Import of C:\temp\commonswiki\xaa.sql has finished with 1 errors

It's also terribly slow as it only imported 209236 rows during the nearly 2 hours, but I think there are about 20M items to import, so at that rate it would take 200 hours to import.

I'm using Windows 7, MySQL 5.6.14 and MySQL Workbench 6.

My main questions:

  • Is there a script which would feed MySQL with the splitted files and perform automatic error recovery in case it times out?

  • Is there a conversion tool which would convert MySQL SQL to PostgreSQL SQL without first loading it into a database?

  • Is this the schema you are using? – cerd Feb 11 '14 at 4:50
  • It should not take this long to load. Are you using InnoDB or myisam for these tables? How much RAM do you have on this machine? From show variables give us values for innodb_buffer_pool_size, innodb_write_io_threads etc. – cerd Feb 11 '14 at 4:57
  • Also see: stackoverflow.com/questions/1112069/… – cerd Feb 11 '14 at 4:57
  • I was using InnoDB with 16 GB RAM although I'm not sure how much MySQL was ready to use. I actually appear to have solved the issue through increasing the 'max_allowed_packet'. – John M Feb 11 '14 at 8:51


max_allowed_packet = 256M

in MySQL config allowed to import the files without errors.

| improve this answer | |

An answer by kresimir-plese to How can I import a large (14 GB) MySQL dump file into a new MySQL database? on Stack Overflow helped me:

mysql -u root -p

set global net_buffer_length=1000000; --Set network buffer length to a large byte number
set global max_allowed_packet=1000000000; --Set maximum allowed packet size to a large byte number
SET foreign_key_checks = 0; --Disable foreign key checking to avoid delays,errors and unwanted behaviour

source file.sql --Import your sql dump file

SET foreign_key_checks = 1; --Remember to enable foreign key checks when procedure is complete!

and from a comment by nicolas-thery:

Over mariadb, max values are checked:

set global net_buffer_length=1048576;
set global max_allowed_packet=1073741824;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.