Speeding up mysql dumps and imports

mysql backup restore
deadprogrammer picture deadprogrammer · Sep 15, 2008 · Viewed 53.8k times · Source

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.

Answer

JBB picture JBB · Sep 16, 2008
  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.

Related questions

How to take backup of a single table in a MySQL database?

By default, mysqldump takes the backup of an entire database. I need to backup a single table in MySQL. Is it possible? How do I restore it?

Read More
Restoring MySQL database from physical files

Is it possible to restore a MySQL database from the physical database files. I have a directory that has the following file types: client.frm client.MYD client.MYI but for about 20 more tables. I usually use mysqldump or a …

Read More
Can I restore a single table from a full mysql mysqldump file?

I have a mysqldump backup of my mysql database consisting of all of our tables which is about 440 megs. I want to restore the contents of just one of the tables from the mysqldump. Is this possible? Theoretically, I could …

Read More