When restoring large MySQL databases from dump files (especially in the 30GB+ range), performance degradation typically occurs due to several factors:

• Index rebuilding during MyISAM table imports
• InnoDB's transaction overhead (even for single small tables)
• MySQL's default configuration being optimized for OLTP rather than bulk operations

These server variables should be adjusted before starting the restore:

SET GLOBAL foreign_key_checks=0;
SET GLOBAL unique_checks=0;
SET GLOBAL autocommit=0;
SET GLOBAL innodb_flush_log_at_trx_commit=2;
SET GLOBAL sync_binlog=0;

For MyISAM tables (which comprise most of your database), these techniques help:

-- Disable keys before import
ALTER TABLE large_myisam_table DISABLE KEYS;

-- After import completes
ALTER TABLE large_myisam_table ENABLE KEYS;

Alternatively, modify your dump file to include these statements around each table's data.

Though you only have one small InnoDB table, these settings matter:

SET GLOBAL innodb_buffer_pool_size=12G; -- 70% of available RAM
SET GLOBAL innodb_log_file_size=2G;
SET GLOBAL innodb_log_buffer_size=256M;

If you need to restart the process, first check which statements completed:

-- For MyISAM tables
CHECK TABLE problematic_table FAST QUICK;

-- For InnoDB
SELECT COUNT(*) FROM imported_table;

Then edit your dump file to skip already-imported data segments.

For truly large databases, consider these approaches:

1. Parallel Import: Split dump file and use multiple connections
2. Physical Backup: Use mysqlhotcopy for MyISAM
3. Loader Utility: Convert to CSV and use LOAD DATA INFILE

Use these commands to monitor progress during lengthy imports:

SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Handler%';
SHOW PROCESSLIST;

When restoring large MySQL databases from dump files, performance degradation is a common issue. The initial fast import followed by progressively slower operations typically indicates one of these underlying problems:

• Index rebuilding overhead (especially for MyISAM tables)
• Transaction handling in InnoDB
• Memory allocation issues
• Disk I/O bottlenecks

Here are the most effective approaches to speed up your MySQL restore:

# Recommended my.cnf settings during restore
[mysqld]
bulk_insert_buffer_size = 256M
key_buffer_size = 1G
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

Use this optimized command for your restore:

mysql --max_allowed_packet=1G --net_buffer_length=1000000 \
--init-command="SET SESSION autocommit=0; SET FOREIGN_KEY_CHECKS=0;" \
-u username -p database_name < dump_file.sql

For extremely large databases (30GB+), consider these additional optimizations:

1. Parallel restoration by splitting the dump file:

# Split the dump file by table
csplit -z dump_file.sql '/^-- Table structure/' '{*}'

2. Disable indexes during import (MyISAM specific):

ALTER TABLE large_table DISABLE KEYS;
# Run your import
ALTER TABLE large_table ENABLE KEYS;

Use this query to monitor the restore progress:

SELECT 
  table_name,
  data_length/1024/1024 AS size_mb,
  index_length/1024/1024 AS index_mb
FROM information_schema.tables 
WHERE table_schema = 'your_database'
ORDER BY data_length DESC;