Foreign keys / Transactions in MySQL.. WTF?

Dagfinn Ilmari Mannsåker ilmari at
Wed Aug 1 09:20:18 BST 2007

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

> On Tue, Jul 31, 2007 at 07:53:28PM +0100, Dagfinn Ilmari Mannsåker wrote:
>> Alex Sayle <alexs at> 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">

2. Create snapshot
4. Copy from snapshot
5. Destroy snapshot


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

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

More information about the mailing list