Speed up a MySQL restore from a dump file

A slow MySQL restore usually spends time parsing inserts, maintaining indexes, committing transactions, or waiting for storage. Measure which resource is limiting the load before changing durability settings. There is no fixed speedup that applies to every dump.

Start with the dump and transaction boundaries

mysqldump --opt, enabled by default, includes extended inserts and several import-oriented options. Inspect the file before adding settings: dumps may already save and change session variables, issue commits, or contain DDL that commits implicitly.

For a controlled data-only load into an isolated destination, session settings can reduce overhead:

SET @saved_autocommit = @@SESSION.autocommit;
SET @saved_unique_checks = @@SESSION.unique_checks;
SET @saved_foreign_key_checks = @@SESSION.foreign_key_checks;

SET SESSION autocommit = 0;
SET SESSION unique_checks = 0;
SET SESSION foreign_key_checks = 0;

-- Execute a bounded batch of INSERT statements here.
COMMIT;

SET SESSION unique_checks = @saved_unique_checks;
SET SESSION foreign_key_checks = @saved_foreign_key_checks;
SET SESSION autocommit = @saved_autocommit;

Use these options only with data whose uniqueness and relationships you can validate. Disabling unique_checks permits InnoDB optimizations; it is not permission to import duplicate keys safely. Re-enabling foreign-key checks does not scan previously loaded rows for violations.

Prefer manageable batches over one enormous transaction. A failed load rolls back its uncommitted transactional work, not necessarily the entire dump: earlier commits, DDL, and nontransactional tables have different behavior.

Give InnoDB enough memory and redo capacity

Size innodb_buffer_pool_size for the memory actually available to MySQL, leaving room for connections, the operating system, and other processes. A fixed percentage of host RAM is inappropriate for a tightly limited container.

On MySQL 8.0.30 and later, use innodb_redo_log_capacity to size redo capacity; older releases use innodb_log_file_size and related settings. Larger capacity can reduce checkpoint pressure but consumes disk space. Verify the installed version before changing configuration.

Settings such as innodb_flush_log_at_trx_commit=2 or sync_binlog=0 trade durability for less synchronous I/O. The amount lost in a crash is not guaranteed to be exactly one second. Disabling binary logging also affects replication and point-in-time recovery. Consider these changes only for a rebuildable, isolated restore target, with an explicit plan to restore normal settings before use.

Choose a format that supports your recovery plan

A single SQL stream fed to the mysql client does not become parallel merely by increasing the server's thread count.

Reduce avoidable I/O and verify the result

Stream a compressed dump rather than first writing an expanded copy:

# Bash: make a decompression failure fail the pipeline too.
set -o pipefail
gzip -dc dump.sql.gz | mysql mydb

Fast storage and adequate memory can help when those are the bottleneck. A local socket avoids some connection overhead, but rarely dominates a large restore.

Afterward, validate row counts, constraints, indexes, and restore errors. Run ANALYZE TABLE where statistics need refreshing. Split large restores into restartable units so a failed table can be retried without replaying the entire backup.

See MySQL's InnoDB bulk-loading guidance.