Importing a 16 GB MySQL file silently fails

Question

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=8
innodb_io_capacity=300
innodb_doublewrite = 0
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1
join_buffer_size=128M
sort_buffer_size=2M
read_rnd_buffer_size=2M
key_buffer_size = 20M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Edit 7/28/21

 mysql> SHOW GLOBAL VARIABLES LIKE '%timeout%';
connect_timeout                      10

 delayed_insert_timeout               300

 have_statement_timeout               YES

 innodb_flush_log_at_timeout          1

 innodb_lock_wait_timeout             50

 innodb_rollback_on_timeout           OFF

 interactive_timeout                  28800

 lock_wait_timeout                    31536000 
 mysqlx_connect_timeout               30

 mysqlx_idle_worker_thread_timeout    60

 mysqlx_interactive_timeout           28800

 mysqlx_port_open_timeout             0

 mysqlx_read_timeout                  30

 mysqlx_wait_timeout                  28800

 mysqlx_write_timeout                 60

 net_read_timeout                     30

 net_write_timeout                    60

 replica_net_timeout                  60

 rpl_stop_replica_timeout             31536000 
 rpl_stop_slave_timeout               31536000 
 slave_net_timeout                    60

 wait_timeout                         6000
mysql>  SHOW SESSION VARIABLES LIKE '%timeout%';
connect_timeout                    10

 delayed_insert_timeout             300

 have_statement_timeout             YES

 innodb_flush_log_at_timeout        1

 innodb_lock_wait_timeout           50

 innodb_rollback_on_timeout         OFF

 interactive_timeout                28800

 lock_wait_timeout                  31536000 
 mysqlx_connect_timeout             30

 mysqlx_idle_worker_thread_timeout  60

 mysqlx_interactive_timeout         28800

 mysqlx_port_open_timeout           0

 mysqlx_read_timeout                30

 mysqlx_wait_timeout                28800

 mysqlx_write_timeout               60

 net_read_timeout                   30

 net_write_timeout                  60

 replica_net_timeout                60

 rpl_stop_replica_timeout           31536000 
 rpl_stop_slave_timeout             31536000 
 slave_net_timeout                  60

 wait_timeout                       28800

Answer 1

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%';

Answer 2

I suggest trying a staggered import approach, like this:

https://www.ozerov.de/bigdump/

Staggered imports allow you to tune the process based on the limitations of your server, which sometimes you have no control over.