Foreign keys / Transactions in MySQL.. WTF?

[Dagfinn Ilmari Mannsåker]

"Jonathan T. Rockway" <jon at jrock.us> writes:

> On Tue, Jul 31, 2007 at 07:53:28PM +0100, Dagfinn Ilmari Mannsåker wrote:
>> Alex Sayle <alexs at alphacomplex.info> writes:
[ Context restored ]
>> > To take a snapshot of a MySQL database you need to lock the whole
>> > database. Once your Database reaches a cretin size and your backups
>> > take more than your average write interval your clients write will
>> > be held until the lock is released.
[…]
>> > Now $work's DB currently takes 90 mins to dump out, and our average
>> > mean time between write is 6 seconds[1]. you do the maths.
>> 
>> That's what filesystem/block-device level snapshots are for.
>
> Does MySQL guarantee that the filesystem is in a legal state at all
> times?  If not, a filesystem-level backup is just going to give you
> garbage.

That's what global table locking is for.

<ol class="spoonfeed">

1. FLUSH TABLES WITH READ LOCK
2. Create snapshot
3. UNLOCK TABLES
4. Copy from snapshot
5. Destroy snapshot

</ol>

You need enough free space in the volume group/pool/whatever to
accomodate the blocks that get written to while the snapshot exists.

-- 
ilmari
"A disappointingly low fraction of the human race is,
 at any given time, on fire." - Stig Sandbeck Mathisen