Restoring MySQL Database from mysqldump file produces seemingly random syntax errors

Question

Sorry for the long title I wasn't sure how else to make it distinct from the auto-search results.

I have a large-ish dump file (~44GB) and I'm trying to import this into MySQL on a different server.

Both servers are running 5.7, though one is 5.7.20 (source) and the other is 5.7.42 (target), on Ubuntu 22.04 LTS. I have, just as a hail mary, installed the latest MySQL 8 server for the OS as the target database server, which produced the same results.

Every attempt to restore the database results in a syntax error, but not always in the same place, and sometimes before or after where the previous run got to.

For example; on run 1 it may error on line 26880, then run 2 it will error on line 14230 (earlier than run 1), then run 3 maybe on line 390290 (later than both of them). Line numbers here are arbitarary but do go up and down, and generally never in the same place twice.

• max_allowed_packet is maxed at 1GB
• innodb_buffer_pool_size is set to 128GB (which is approximately 65% of the available RAM)

Everything else is default. There are no errors listed in the error log.

I have tried other backups, which produce the same result.

The backup is not compressed.

What have I done wrong?

Answer 1

Please note your statement

For example; on run 1 it may error on line 26880, then run 2 it will error on line 14230 (earlier than run 1), then run 3 maybe on line 390290 (later than both of them). Line numbers here are arbitarary but do go up and down, and generally never in the same place twice.

To me, this points to something about the network throwing its hands up in the air and just giving up. You should make the network wait while sending mysqldump rows to the MySQL Instance

I have addressed this before

• 2018-01-04 : MySQL - MySQLDump problems with large table
• 2019-08-06 : Incomplete dumps using mysqldump

On a bare mental machine, I would do this:

mysql> SET GLOBAL net_read_timeout = 31536000;
mysql> SET GLOBAL net_write_timeout = 31536000; 

I have seen mysqldumps being created and just fail with similar messages. Your situation (the reverse : loading a mysqldump into a MySQL Instance) should be just as applicable.