1

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?

1
  • How much RAM on target machine (192GB?) What is the fulltext of the error message? What is the query on the naughty line? What was max_allowed_packet on the source machine?
    – Rick James
    Oct 4, 2023 at 20:56

1 Answer 1

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

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.

2
  • 1
    One could also transfer the dump file to the database server host, and verify it is not damaged by using a checksum. Then import the dump file to the database on that host. Oct 4, 2023 at 18:00
  • Hi there, thanks for this! It turns out it was in fact the net read/write timeouts. I wasn't expecting this as I was working with the file on a local machine using mysql -uusr -p targetDB < dumpfile.sql
    – DSZ
    Oct 5, 2023 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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