Antoine, Thursday, November 28, 2002, 4:17:48 PM, you wrote: A> Sorry to insist, but nobody has any clues about this ? A> I can supply further info if needed.
A> Is the "FLUSH TABLES WITH READ LOCK" functionality well tested ? A> Having corrupted backups is annoying, and I can't really A> take the system down for a 4-hour tape backup... ;( A> -------- Message original -------- A> Sujet: possible problems with FLUSH TABLES WITH READ LOCK A> De: "Antoine" <[EMAIL PROTECTED]> A> Date: Mar, 26 Novembre 2002, 17:27 A> A: <[EMAIL PROTECTED]> A> Hi, A> I am using FLUSH TABLES WITH READ LOCK to get consistent A> snapshots of my database without shutting it down. A> The setup is : A> - bi-P4 Xeon with Redhat 7.3 A> - 2.4.19 kernel with properly patched LVM (compiled from source) A> - MySQL server 4.0.4 (compiled from source) A> - ext3 filesystem on a 44 GB LVM logical volume named /dev/vgdata/data - A> the whole database is 20 GB in size A> - all tables are MYISAM ; some with dynamic records, some fixed, A> some compressed A> - some tables - not all - are created with DELAYED_KEY_WRITE=1 to get A> more speed (30% faster thanks to this) A> The backup sequence is : A> - FLUSH TABLES WITH READ LOCK A> - lvcreate -L 5G -c 256k -s -n backup /dev/vgdata/data A> (this creates a 5GB snapshot volume named "backup" from the logical A> volume containing the database) A> - UNLOCK TABLES A> - mount /dev/vgdata/backup /backup -oro,noatime A> - cd /backup/ ; tar cvf /dev/st0 * You need to do it with 2 sessions. According MySQL manual: If you are using a Veritas filesystem, you can do: 1. From a client (or Perl), execute: FLUSH TABLES WITH READ LOCK. 2. From another shell, execute: mount vxfs snapshot. 3. From the first client, execute: UNLOCK TABLES. 4. Copy files from snapshot. 5. Unmount snapshot. A> Today I've tried restoring a backup on a test partition just to see A> (you're never too careful). Restoring is OK (of course) but when I run A> "myisamchk -c *.MYI", just to be sure, I get various kinds of errors, on A> some tables but not all. Common errors include : A> "1 clients is using or hasn't closed the table properly" A> "error: Size of indexfile is: 17404928 Should be: 17507328" A> "warning: Size of datafile is: 24922872 Should be: 24896378" A> "error: Found 185731 keys of 186676" A> "error: Found key at page 1024 that points to record outside datafile" A> In fact, all kind of errors that you'd expect to find if you copy your A> files without doing a "FLUSH TABLES WITH READ LOCK" first. Thus I A> was wondering if the latter command does work properly. Is it likeky to A> be due to : A> - SMP problems ? (it has hyperthreading enabled, BTW, but this shouldn't A> make any further difference : it just sees 4 logical CPUs instead of 2) - A> DELAYED_KEY_WRITE ? (but some tables that aren't created as such have A> problems too, so this shouldn't be the _only_ problem) A> - specific Linux locking behaviour wrt flushing & locking tables ? - A> Linux LVM bug ? (unlikely in my opinion, it seems heavily used) A> - other... ? A> Please note : tables are written to in a continuous way, so it's no A> surprise many tables get corrupted if the lock is not absolutely A> consistent and fail-proof ;)) A> Also, I know the backup volume is large enough (I print the occupied size A> at the end of the backup procedure). A> Well, of course, it may just be the backup tape itself that was screwed A> up, but it doesn't seem very likely, at least in my opinion, otherwise A> un-tar-ing it should have failed somewhere. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
