Can I restore a mysql database faster?

Question

I'm quite new to MySQL and I was wondering: when dumping a mysql database 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?

score 4 · Answer 1 · edited May 23 '17 at 12:13

4

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.

edited May 23 '17 at 12:13

Community

1
1

answered Jul 05 '11 at 16:17

zw324

26,764
16
85
118

i always can not understand why --disable-keys can help in this kind of scenario ---- you always have to create the index anyway, i have tested disable keys, import data and then enable keys, it is even slower than import the data directly. – James.Xu Jul 05 '11 at 16:25
@James Here's some info, dunno if it is helpful: http://forums.mysql.com/read.php?21,68820,68820 – zw324 Jul 05 '11 at 16:27
thanks for you link. In my test, there is no old data in the table. In my opinion, --diable-keys can not help, unless the indexes you are disabling are not so import that you can use the data right after importing data. – James.Xu Jul 05 '11 at 16:33

score 3 · Answer 2 · answered Jul 05 '11 at 16:18

3

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

answered Jul 05 '11 at 16:18

Paul Sonier

38,903
3
77
117

...but only if there are no DML statements processed during the backup / restore (i.e. the database is shut down) – symcbean Jul 05 '11 at 16:20

score 2 · Answer 3 · answered Jul 05 '11 at 16:20

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.

score 0 · Answer 4 · answered May 19 '13 at 04:42

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

score 0 · Answer 5 · answered Jul 05 '11 at 16:19

0

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)

answered Jul 05 '11 at 16:19

symcbean

47,736
6
59
94

Can I restore a mysql database faster?

5 Answers5