[linux-lvm] LVM snapshots and filesystem quiescence

[Bryan Whitehead]

lt;dr You can safely use LVM for most modern DB's.

To start, a SQL dump is cool and all, but will not scale. Once your DB
is ~300GB (or 30GB depending on the data), the dump takes a day and a
restore takes even longer (much longer).

Databases should write to a journel, log, whatever, so a replay of
transactions can happen if a filesystem snapshot was taken. Basically
the same methods that keep a filesystem functional when the power is
pulled also applies to a modern database (note: filesystems often only
journel metadata - a DB will journel the entire transaction). The DB
will startup, see the database files are dirty and replay its journel.
The journel should have metadata, data, etc... and the databases
store/files (sitting on the filesystem) should have internal
versioning so rows can be consistant before a replay.

Using MySQL as an example along with InnoDB, the transaction
journel/buffer is written to and can be replayed - making this buffer
huge will result in long recovery times but has its benefits. Can some
transactions not make it to the buffer/log? Sure. But your DB will be
consistant and clean (and not just consistant with missing data). If
that snapshot needs to be promoted because someone ran DROP DATABASE
myapp; and your master/slaves all deleted everything - you are good.
Use a snapshot from LVM as new master, remove the bad SQL from the log
(this is different from the internal innodb buffer/journel), and
replay logs. You are now caught up. Take another snapshot for safety,
make new slaves, restart business.

I've only had to do this twice.

To make this longer, if you DB is garbage (think MySQL with MyISAM)
you are basically completely hosed and can just run repair_table on
each table. That is like running fsck which will probably just remove
rows half-written and leave a mess behind. That sucks. I've don't that
also. To use a crappy DB like that (using MySQL/MyISAM as an example),
you'd run "flush tables with read lock", this might take 0-25mins to
return, take snapshot, run "unlock tables". This works. But your
should do this on a dedicated slave.

PS. if you are using MySQL just use hotbackup on a slave.

On Thu, Apr 25, 2013 at 8:05 AM, Clay Gerrard <clay.gerrard at gmail.com> wrote:
> I love this question.  I always thought the only way to guarantee a
> consistent snap of a block was to unmount it before taking the snap, mostly
> just cause of un-fsynced data in the page cache.  Any other answer would be
> very welcome.
>
> -Clay
>
>
> On Thu, Apr 25, 2013 at 5:14 AM, Tanstaafl <tanstaafl at libertytrek.org>
> wrote:
>>
>> Hello,
>>
>> I'm totally new to LVM snapshots. I've have read the FAQ and docs and
>> searched the list archives and can't find an answer to my question...
>>
>> I know that you have to quiesce SQL databases using their own native tools
>> if you want to have a consistent snapshot of the raw db files, but what
>> about other programs like postfix or dovecot that are constantly (on a busy
>> server) writing to disk? Do you need to stop these, take the snapshot, then
>> restart them? Or will the filesystem itself handle this (similar to how VSS
>> works on windows)?
>>
>> I'm fairly certain the answer is no, I don't have to worry about these
>> kinds of programs, only SQL databases.
>>
>> I engage a script that simply performs a native dump of my databases
>> immediately preceding the snapshot (my db's are very small and the dumps
>> only take a few seconds, and this just seems much simpler to me) and places
>> these files into a sql_dumps directory on the filesystem that will be
>> snapshotted so these will be included in my backups, so am not worried about
>> them being in a consistent state.
>>
>> Thanks in advance...
>>
>> Charles
>>
>> _______________________________________________
>> linux-lvm mailing list
>> linux-lvm at redhat.com
>> https://www.redhat.com/mailman/listinfo/linux-lvm
>> read the LVM HOW-TO at http://tldp.org/HOWTO/LVM-HOWTO/
>
>
>
> _______________________________________________
> linux-lvm mailing list
> linux-lvm at redhat.com
> https://www.redhat.com/mailman/listinfo/linux-lvm
> read the LVM HOW-TO at http://tldp.org/HOWTO/LVM-HOWTO/