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 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