l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
November 4: Social gathering
Next Installfest:
TBD
Latest News:
Oct. 24: LUGOD election season has begun!
Page last updated:
2003 Dec 05 17:23

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
Re: [vox-tech] Tar, MySQL, and cron-ed backups
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [vox-tech] Tar, MySQL, and cron-ed backups



So, if tar is saying the "file changed as we read it", does that mean that tar:
* skipped the file,
No

* made a copy of the version that existed when tar *started* the operation,
No

* made a copy of the version that existed when tar *finished* the operation, or
No

* some combination of these?
yes. if it was half-way through reading the file.. then the first half is the old file, and the second half is the new files. If more than one change was made, then more than one file is there.

Even if tar doesn't complain, the database is probably still be corrupted, since several independent files all need to be "in sync" for the database to be consistent. Almost certainly the files in the archive will be badly corrupted (indices inconsistent with tables, records that were changed half-way through a read), and therefore the backup of those files will be useless. Even if they "work" (mysqld doesn't barf on startup) they are almost certainly not transactionally consistent, and that will lead to unpredictable behavior and incorrect results. You'll need to either shut down the database or exclude those files whatever the final solution.

This is the kind a dilemma that a good DBA/consultant gets paid so much to resolve. Basically, you can use a tool like mysqldump to dump a version of the tables, which can be used to reconstruct the database later. If you don't have enough disk space, then get another (or bigger) disk. Even then, transactional rules that aren't strictly enforced by the database may lead to inconsistent data if you're dumping a "live" database.

For example... Say the mysqldump script is working on an order processing database. If it dumps the customer table first, followed by the product table and then the order table you can end up with a customer record which was added to the database after the dump for the customer table completed, (and therefore isn't in the backup) but a corresponding order record was added before the backup of the order table, which means that the restored database would have an order corresponding to a non-existent customer record, violating the foreign key constraint.

If you "fix" this by backing up the order table first, then you end up with the same problem with customer record deletions. The solution here is to have a way to "suspend" certain types of operations during the backup. (e.g. don't delete customers/orders, just set a flag in the table that says they're invalid, and delete them later.. this is still no guaranty though... but it may lessen the amount of work needed to manually "fix" the inconsistencies when you have to restore.

Good luck,
-- Mitch

_______________________________________________
vox-tech mailing list
vox-tech@lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech



LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Sunset Systems
Who graciously hosts our website & mailing lists!