How can I speed up a MySQL restore from a dump file?

Solution 1:

One thing that may be slowing the process is the key_buffer_size, which is the size of the buffer used for index blocks. Tune this to at least 30% of your RAM or the re-indexing process will probably be too slow.

For reference, if you were using InnoDB and foreign keys, you could also disable foreign key checks and re-enable it at the end (using SET FOREIGN_KEY_CHECKS=0 and SET FOREIGN_KEY_CHECKS=1).

Solution 2:

This link shows what one can do to speed up restoring process.

http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

One can put put the commands at the top of the dump file

SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

And put these statements at the end of the dump file

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;

This worked for me. Happy restoring :-)

Solution 3:

If you have the physical copy of the dump file (the DB directory), you can just copy it to the new server if the new server have the same MySQL version and it will work fine. This work fine with MyISAM and for me I think it is better than restoring the data based on the logical SQL dump file.

Solution 4:

This will do:

mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" -u root -p < Backup_Database.mysql