Can I restore a mysql database faster?

I'm quite new to MySQL and I was wondering: when dumping a mysql datab开发者_Python百科ase it takes only a few seconds, but when loading it sometimes it takes a few minutes! Is there a reverse of mysqldump to load the database in a few seconds?

Some easy tuning here might help.

Also, there are techniques that can help in specific situations, such as using --disable-keys.

In addition, there is an older post. Be careful of the chosen answer though, the comment said it is dangerous, which is correct, and this tool is now officially deprecated.

In mysql, for storage engines that use file-based storage, you can backup and restore using the files. See this relevant page:

http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

It's slower when you load it because it has to recreate the indexes. So the short answer is "no". However you can improve it by using --opt option when you dump. This adds some SQL to the dump file that does various things such as disabling the keys until all the data is loaded so it rebuilds indexes all at once.

This offers a nice improvement.

No - writes always take longer than reads - and with a relational database it has to rebuild the indexes too. There are some things you can do to make it go faster though (e.g. use extended inserts, defer index rebuilds)

there is an interesting to restore from mysql dump files (created using mysqldump) ... the technique is explained in detail at

http://www.geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/fast_parallel_restore_from_sql_dumps_mysqldump_for_mysql.php

it uses different user accounts to run backups in parallel