[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: CVS to back up MySQL databases?

From: Mark D. Baushke
Subject: Re: CVS to back up MySQL databases?
Date: Thu, 18 Jan 2007 08:38:10 -0800

Hash: SHA1

Doug Lee <address@hidden> writes:

> Do the CVS experts out there see any reason why I should not use CVS
> as a means of making incremental backups of a MySQL database dump?  

The biggest problem is that mysqldump tries to dump as much of a table
on each line of the file as possible. So, if you insert a new row, then
you are very likely going to see the entire table show up on a single
line for CVS to rewrite. This will make for some very big ,v files in
your repository over time.

If you can convince mysqldump to dump your tables with one row per line,
then only if you are constantly modifying columns will you get huge
diffs between one dump and the next.

> My theory is that if I do the following, I will have the ability to
> restore the entire database to exact states going back N days, where N
> is selected easily and can easily be changed. I also believe that, at
> least for slow-changing data and assuming a wise selection of dump
> formats, this technique will use minimal disk space for what it
> yields. I'm writing to see if anyone with more experience can find a
> hole in this system:
> 1.  Create a CVS repository containing one file, which will be the
> MySQL dump output file.
> 2.  If necessary, create a small filter program that splits dump file
> lines such that one field appears on each line but processing the file
> for database restoration still works as expected.

This might work.

> 3.  Create a permanent CVS sandbox, make the first MySQL dump, and
> commit it.

Be sure to update before you modify just in case someone else has done a
snapshot of your database from a different directory...

> 4.  On a fixed schedule, dump the database into the same file,
> replacing the file, and commit the update.
> 5.  On another fixed schedule, use `cvs admin' to delete old
> revisions.

Possible, but if you can get the data format problems down far enough,
why remove the oldest revisions?

> 6.  In case of emergency, restore the database to any saved state by
> checking out the appropriate dump and rebuilding the database with it.

Yup, provided your 'split' routines in #2 did not corrupt your tables at

> This will obviously produce huge deltas if an ALTER TABLE command is
> used, but it seems sound in all other respects to me.


        -- Mark
Version: GnuPG v1.4.6 (FreeBSD)


reply via email to

[Prev in Thread] Current Thread [Next in Thread]