Is there a faster way to load mysqldumps?
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
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.