Fast InnoDB Restore?

Question

Hi All,

I am working on a development copy of a MySQL / InnoDB database that is about 5GB.

I need to restore it pretty frequently for testing alter scripts, and using the mysqldump file is taking quite a while. The file itself is about 900MB and takes around an hour to load in. I've removed data inserts for unimportant tables, and done extended inserts, etc., but it's still pretty slow.

Is there a faster way to do this? I am thinking to just make a copy of the database files from .../mysql/database-name, id_logfile#, and ibdata1, and copy them back in when I need to 'reset' the db, but is this viable with InnoDB? Is the ibdata file for one database? I only see one, even though I have multiple InnoBD db on this box.

Thanks!

Answer 1

I have no idea if it would be faster -- it might be slower, in fact, depending on how extensively your tests alter the data --, but what if you put all your tests in a transaction, and then rolled it back?

Answer 2

I believe you can just copy the file named for the database (with the server daemon down) and all should be well. It seems like something that a little testing on a sample db should answer quickly, no?

Answer 3

You might be able to do something with http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html

See the ALTER TABLE xxx IMPORT TABLESPACE option especially.