l i n u x - u s e r s - g r o u p - o f - d a v i s
Next Meeting:
July 7: Social gathering
Next Installfest:
Latest News:
Jun. 14: June LUGOD meeting cancelled
Page last updated:
2003 Dec 05 18:25

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

On Fri, Dec 05, 2003 at 04:43:25PM -0800, Mitch Patenaude wrote:
> 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.

If you're worried about inconsistencies, use the -l switch to mysqldump.
This is a per database lock, so if you're dumping multiple databases
with related tables, you might run into problems.

Marc didn't say what version of MySQL server he is running, or what
table types he is using.  You need InnoDB tables for transactions, and
server 4.0.x for foreign key constraints.

But none of this matters if you use mysqldump -l.

So the revised command might be:

  mysqldump -l --opt mydb | gzip > mydb_`date +%y%m%d%H%M%S`.sql.gz

vox-tech mailing list

LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
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!