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
mysql -u root -p mydatabase < archive.sql
for import. And there are no errors in the /var/log/mysqld.log