Foreign keys / Transactions in MySQL.. WTF?
Dagfinn Ilmari Mannsåker
ilmari at ilmari.org
Wed Aug 1 09:20:18 BST 2007
"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. 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
That's what global table locking is for.
1. FLUSH TABLES WITH READ LOCK
2. Create snapshot
3. UNLOCK TABLES
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 london.pm