Is there a faster way to load mysqldumps? [duplicate]

Question

Possible Duplicate:
Speeding up mysql dumps and imports

mysqldump is reasonably fast, but dumps of a medium-sized database (20-30 megs) take several minutes to load using mysql my_database < my_dump_file.sql

Are there some mysql settings I can tune to speed up the load? Is there a better way to load saved data?

I've experimented using the mysqlimport utility with CSV-based dumps. These load slightly--but not appreciably--faster. I'm tempted to just copy raw database files around, but that seems like a bad idea.

Answer 1

Assuming that you're using InnoDB...

I was in the situation of having a pile of existing mysqldump output files that I wanted to import in a reasonable time. The tables (one per file) were about 500MB and contained about 5,000,000 rows of data each. Using the following parameters I was able to reduce the insert time from 32 minutes to under 3 minutes.

innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT

You'll also need to have a reasonably large innodb_buffer_pool_size setting.

Because my inserts were a one-off I reverted the settings afterwards. If you're going to keep using them long-term, make sure you know what they're doing.

I found the suggestion to use these settings on Cedric Nilly's blog and the detailed explanation for each of the settings can be found in the MySQL documentation.

Answer 2

maatkit - parallel dump

and

maatkit - parallel restore

Very fast.