Mysql – Speeding up thesql dumps and imports

backupmysqlrestore

Are there any documented techniques for speeding up mySQL dumps and imports?

This would include my.cnf settings, using ramdisks, etc.

Looking only for documented techniques, preferably with benchmarks showing potential speed-up.

Best Solution

  1. Get a copy of High Performance MySQL. Great book.
  2. Extended inserts in dumps
  3. Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile
  4. Import with multiple threads, one for each table.
  5. Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
  6. Look at the table compare script in the Maakit toolkit and see if you can update your tables rather than dumping them and importing them. But you're probably talking about backups/restores.