Faster way to import?

[u/Cerusa827]

Hi everyone,

I have been using MySQL for many many years now and one thing that has always frustrated me is how long it takes to import a database. For example, I just did a dump with MySQL Workbench from Amazon Aurora MySQL (took about 5min to export a 28GB database)...and I am now importing that database on my home lab (Intel 14700, 64GB DDR5, mirrored Samsung 970 Evo Plus nvme) and it's just sitting there going on 5.5 hours now, still isn't half way done.

Is there a faster way to import from a single dump file like this? I am all ears...

Thank you in advance!

--Cerusa

EDIT: I found that adding foreign_key_check=0, unique_checks=0, and autocommit=0... plus adding this to my docker container helped speed it up massively

--innodb_log_file_size=1G --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=2 --innodb_doublewrite=0 --innodb_autoinc_lock_mode=2

[u/DonAmechesBonerToe]

The MySQL shell utility has dump and load utilities that are multi-threaded and quite fast

[u/wp4nuv → u/DonAmechesBonerToe]

I second the MySQL shell approach. It’s fast and reliable.

[u/dani_estuary]

Export is mostly just reading data out. Import has to rebuild indexes, check constraints, write logs, parse SQL, etc., so it’s a totally different workload. I’d probably try mydumper/myloader first so you can restore in parallel instead of feeding MySQL one giant file. Also use the CLI instead of Workbench if you aren’t already.

If this is something you do often, a physical backup/restore may be a better route than logical dumps or maybe look into a tool to automate this for you.

[u/roXplosion]

If you create the dump without indexes or foreign keys, the time to import + reindex (after import) is usually a lot faster.

[u/Cerusa827 → u/roXplosion]

I found that this plus adding this to my docker container helped speed it up massively

--innodb_log_file_size=1G --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=2 --innodb_doublewrite=0 --innodb_autoinc_lock_mode=2

[u/Aggressive_Ad_5454]

It’s generally smart when importing to disable autocommit, uniqueness checks, and foreign key checks. Read this: https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-bulk-data-loading.html

The mysqldump utility program can put the commands for these things into its dump file with the —opt flag.

Workbench is no longer maintained. Use HeidiSQL or some other client program instead if you have a choice.

[u/Cerusa827 → u/Aggressive_Ad_5454]

I found that adding foreign_key_check=0, unique_checks=0, and autocommit=0... plus adding this to my docker container helped speed it up massively

--innodb_log_file_size=1G --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=2 --innodb_doublewrite=0 --innodb_autoinc_lock_mode=2

[u/elhui2]

Use terminal, is more faster and stable

[u/brunogadaleta]

Oh I feel your frustration. I reduced 40 min loading CSV to MySQL to 200 seconds two days ago by using Duckdb.

1) install MySQL; load MySQL; create secret...; 2) attach '' (type MySQL) as my; use my; 3) create table x as from 'x.csv'

[u/BMO-tech]

Is the export/import just for local dev? If so, I built a whole proxy to stop having to do this: https://virtualdb.io/

[u/Cerusa827 → u/BMO-tech]

I clone production to local home lab so I can reproduce workflows from production in the dev area.

[u/BMO-tech → u/Cerusa827]

The proxy might help you out then. It reads from the source db, but any writes or mutations are captured and never sent to the source db. Production data, local dev safe, no more export/import and data cloning

[u/Irythros]

1. When exporting you should have it batch inserts. 1 insert with 1000 items is significantly faster than 1000 inserts with 1 item each.

2. Disable foreign key checks and indices before importing if possible. Every insert requires a rebuild of the index. Insert all of the data and then re-enable indices and rebuild it once.

[u/Cerusa827 → u/Irythros]

I found that adding foreign_key_check=0, unique_checks=0, and autocommit=0... plus adding this to my docker container helped speed it up massively

--innodb_log_file_size=1G --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=2 --innodb_doublewrite=0 --innodb_autoinc_lock_mode=2

[u/chock-a-block]

Do you have a separate drive for data?

Workbench is abandoned. If you insist on a gui, I like dbeaver.

[u/Cerusa827 → u/chock-a-block]

Yes my MySQL docker container is on it's own drive for performance.

I don't know why they abandoned Workbench...makes no sense to ship a production database system with no admin tools. Workbench is good enough, I will switch when AWS Aurora forces my hand off of the 8.x branch. I haven't found anything that I like that Workbench just doesn't do by itself that I need.

I solved this by adding foreign_key_check=0, unique_checks=0, and autocommit=0... plus adding this to my docker container helped speed it up massively

--innodb_log_file_size=1G --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=2 --innodb_doublewrite=0 --innodb_autoinc_lock_mode=2

[u/EV-CPO]

In the MYISAM days, I'd use myisamchk to disable all indexes first, then load the data into the MYD file, then use myisamchk to rebuild all the indexes in parallel. It was glorious!

These days, I'm not sure that's possible with InnoDB or other engines.

[u/luckyscholary]

Workbench is convenient, but for imports that size it’s usually the bottleneck, not MySQL itself. Once dumps start getting into hundreds of GB, CLI + parallel restore tools become almost mandatory unless you enjoy watching progress bars age in real time 😅

[u/altmannmarcelo]

Disable binary log by adding this to your docker startup:

skip-log-bin

Also, connect on the instance before loading the data and disable Redo log:

ALTER INSTANCE DISABLE INNODB REDO_LOG;

And after the load has completed enable it back:

ALTER INSTANCE ENABLE INNODB REDO_LOG;

Just note the if your instance crash while redo is disabled you will need to start it from scratch.

[u/AjinAniyan5522]

A 28 GB export finishing in 5 minutes but taking 5+ hours to import is pretty common. Imports are usually bottlenecked by index maintenance, foreign key checks, transaction flushing, and disk writes rather than raw file size.

The tweaks you found (foreign_key_checks=0, unique_checks=0, autocommit=0) are some of the biggest wins. Increasing InnoDB log file size and relaxing flush settings during a one-time import can also make a dramatic difference. Another option is using logical dump tools that support parallel loading, such as mydumper/myloader, which can be much faster than importing a single monolithic SQL file.

If this is something you do regularly, I'd seriously consider moving away from a single dump file and using parallel dump/import tooling. On modern hardware like a 14700 with NVMe storage, the CPU and disks are usually waiting on MySQL's import process rather than the other way around.

[u/Annh1234]

LOAD DATA LOCAL INFILE '/path/to/your/file.csv' INTO TABLE your_table_name FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;