Re: Daily rebuild of a development database.

[Rick James]

LVM

If the OS provides a way to take "snapshots" of the entire disk (or at least the volume with all of mysql on it), then you can have a dump and reload in seconds or minutes, even for 100GB.

Roughly speaking it goes like this:

1. Stop production MySQL
2. Perform Logical Volume snapshot

Now you have two copies of the same 100GB of data. But only slightly more than 100GB of disk is comsumed! From there, you can...

3. copy the snapshot to somewhere else

or

3. Use the snapshot for another instance of mysql.

Note: This replaces "backup". It is a full disk copy. How, you ask? "Copy On Write", plus some magic in the file system.

The copy points to the _same_ disk blocks until either the original or the copy modifies the block. At that point, a copy of the block is made (and suitable pointers are changed). So the two versions gradually diverge (and gradually take more space).