Importing a 16 GB MySQL file silently fails

I've looked at a lot answers here MySQL any way to import a huge (32 GB) sql dump faster? and on Server Fault but haven't found a solution to not being able to import a 16 GB MySQL file completely; it silently fails at different parts of the import. Sometimes 2 GB will import before the import stops, sometimes 10 GB. There are no errors in the logs. Sometimes the console returns to #, sometimes not.

top shows MySQL running at 100% when importing, but then drops down to normal loads when the import stops.

The database is 16 GBs, and has 90% InnoDB tables with a few MyISAM tables. To export the database on the first machine, I'm using

mysqldump --single-transaction --lock-tables  -u mydatabaseuser -p mydatabase > archive.sql

The importing machine has 8 dedicated cores and 150 GB dedicated RAM (server is at Linode) and is running Alma Linux and MySQL 8.0.25.

To import, I'm using

mysql -u root -p mydatabase < archive.sql

Running the import from within mysql, i.e. using mysql> doesn't help.

One table is 12 GB, and I tried importing that table by itself with no luck.

Using these in my.cnf throws an error on mysql restart:

autocommit=0
unique_checks=0
foreign_key_checks=0

Should I be exporting differently?

Is MySQL on the importing machine timing out?

What do I need to edit in my.cnf?

my.cnf on the importing machine:

```
[mysqld]
disable-log-bin=1
default-authentication-plugin=mysql_native_password
performance-schema=0

port = 3306
socket = /tmp/mysql.sock
skip-external-locking
skip-name-resolve

# uncomment for import
bulk_insert_buffer_size = 40G
read_buffer_size = 40G

# If I uncomment these, MySQL throws the error on restart
# Job for mysqld.service failed because the control process exited with error code"
# autocommit=0
# unique_checks=0
# foreign_key_checks=0

innodb_buffer_pool_size=50G
innodb_buffer_pool_instances=56
innodb_log_file_size = 8G
innodb_log_buffer_size=64M
innodb_read_io_threads=8
innodb_write_io_threads=

Those 3 settings are probably wrong. When autocommit=0, nothing gets stored until a COMMIT is issued.

Look in the dump file; it will probably have a lot of settings, plus some huge (multi-row) INSERTs. Big INSERTs like those work best with autocommit=ON.

150GB is an odd RAM size; are you sure?

These sound dangerous, even with 150G of RAM:

bulk_insert_buffer_size = 40G
read_buffer_size = 40G

They are "buffers", they don't need to be huge; 1G is probably more than ample. Set them to 100M each.

16 is the recommended max for this:

innodb_buffer_pool_instances=56

Timeout??

Time it. How long does it run? Perhaps exactly 5 minutes? Look for a timeout setting that is 600 (seconds).

SHOW GLOBAL VARIABLES LIKE '%timeout%';
SHOW SESSION VARIABLES LIKE '%timeout%';

StackExchange Database Administrators Q#295874, answer score: 2