[lug] mysqldump versus snapshot versus..

[George Sexton]

dio2002 at indra.com wrote:
>> steve at badcheese.com wrote:
>>> The only thing tricky about doing DB backups is getting a good, solid
>>> snapshot of the DB.  This generally means 'locking' the tables as you
>>> dump them.
>> Or... get a real commercial DB with a "quiescent" mode specifically
>> designed for the purpose where the DB tables are stopped and changes are
>> written to logical logs while the snapshot is being taken.
>>
>> Just a thought.
> 
> all good thoughts.  my app happens to be using mysql so the choice is out
> of my control for right now unless i want to rewrite it and i don't :-). 
> thanks for the insight.

Most real databases write updates to the transaction log, and once 
they're written to the transaction log, they then write them to the 
underlying tables.

A backup process usually just suspends the writer process from doing 
this until the backup completes.

Of course, where this gets icky is when you have someone reading. The 
engine has to check the transaction log to see if the rows it's 
returning are affected by an update, and if so, return those.

-- 
George Sexton
MH Software, Inc.
Voice: +1 303 438 9585
URL:   http://www.mhsoftware.com/