Slow running MySql restore - 10 times slower than backup speed and still going

Very large imports are notoriously hard to make fast. It sounds like your import is slowing down--processing fewer rows per second--as it progresses. That probably means MySQL is checking each new row to see whether it has key-conflicts with the rows already inserted.

A few things you can do:

Before starting, disable key checking.

   SET FOREIGN_KEY_CHECKS = 0;   SET UNIQUE_CHECKS = 0;

After ending restore your key checking.

  SET UNIQUE_CHECKS = 1;  SET FOREIGN_KEY_CHECKS = 1;

And, if you can wrap every few thousand lines of INSERT operations in

   START TRANSACTION;   INSERT ...   INSERT ...   ...   COMMIT;

you'll save a lot of disk churning.

Notice that this only matters for tables with many thousands of rows or more.

mysqldump can be made to create a dump with that disables keys. https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_disable-keys

mysqldump --disable-keys

Similarly,

mysqldump --extended-insert --no-autocommit

will make the dumped sql file contain a variant of my suggestion about using transactions.

In your case if you had used --opts --no-autocommit you probably would have gotten an optimal dump file. You already used --opts.

I changed my.ini and got some improvements while also using mysqldump --extended-insert --no-autocommit

my.ini for 16GB RAM on Windows 10 mysql 7.4

# Comment the following if you are using InnoDB tables#skip-innodbinnodb_data_home_dir="C:/xampp74/mysql/data"innodb_data_file_path=ibdata1:10M:autoextendinnodb_log_group_home_dir="C:/xampp74/mysql/data"#innodb_log_arch_dir = "C:/xampp74/mysql/data"## You can set .._buffer_pool_size up to 50 - 80 %## of RAM but beware of setting memory usage too high#innodb_buffer_pool_size=16Minnodb_buffer_pool_size=8G## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size=5Minnodb_log_file_size=2Ginnodb_log_buffer_size=8M#innodb_flush_log_at_trx_commit=1#Use for restore onlyinnodb_flush_log_at_trx_commit=2innodb_lock_wait_timeout=50