Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

At work we do a nightly dump of our mysql databases. From day to day, I would guestimate that close to 90-95% of the data is duplicate, increasing as time goes on. ( Heck at this point some are probably 99% )

These dumps are where one line is a single mysql INSERT statement, so the only differences are whole lines, and the order in which they're in in the file. If I got them sorted, the actual difference from file to file would be very small.

I've been looking, and I haven't found any way to sort the output on dump. I could pipe it through the sort command, though. Then there would be long, long blocks of identical lines.

So I'm trying to figure a way to store only the diffs. I could start with a master dump, and diff against that each night. But the diffs would be larger each night. Or, I could make rolling diffs, which individually would be very small, but seems like it would take longer and longer to compute, if I have to put together a master diff of the whole series each night.

Is this feasible? With what tools?


Edit I'm not asking how to do mysql backups. Forget mysql for the moment. It's a red herring. What I'm wanting to know is how to make a series of rolling diffs from a series of files. Each night we get a file ( which happens to be a mysqldump file ) that is 99% similar to the one before it. Yes, we gzip them all. But it's redundant to have all that redundancy in the first place. All I really need is the differences from the night before... which is only 1% different from the night before... and so on. So what I'm after is how to make a series of diffs so I need only store that 1% each night.

share|improve this question
add comment

4 Answers 4

up vote 10 down vote accepted

Lately I've been trying out storing database dumps in git. This may get impractical if your database dumps are really large, but it's worked for me for smallish databases (Wordpress sites and the like).

My backup script is roughly:

cd /where/I/keep/backups && \
mysqldump > backup.sql && \
git commit -q -m "db dump `date '+%F-%T'`" backup.sql
share|improve this answer
    
This only stores diffs? –  user394 Aug 20 '10 at 12:40
2  
Yes. It's very convenient! You can "check out" the file from any point in time, and git will automatically combine the diffs to give you the whole file as it existed at that time. –  sep332 Aug 20 '10 at 13:39
1  
This blog post (not mine) goes into more detail: viget.com/extend/backup-your-database-in-git The comments get more into the pros & cons and caveats. I'll also add that if you use git, you get more than just being able to roll back versions. You can also tag dumps, or have separate branches (dev/prod). The way I look at it is git (or insert your favorite modern version control system) does a better job than I could by rolling my own diff/gzip 'solution'. One warning about this article: don't push your dumps to github unless you want them public (or are paying for a private repo). –  drench Aug 20 '10 at 17:10
    
Git does not only store diffs. In fact, primarily it stores the full snapshot of each revision, but with various optimizations. See this excellent answer and its question –  tremby Mar 31 at 7:16
add comment

Two backup tools that can store binary diffs are rdiff-backup and duplicity. Both are based on librsync, but above that they behave quite differently. Rdiff-backup stores the latest copy and reverse diffs, while duplicity stores traditional incremental diffs. The two tools also offer a different set of peripheral features.

share|improve this answer
1  
IIUC, rdiff-backup is more attractive, for it allows one to browse the backup normally, while duplicity only has an old copy. –  Tshepang May 17 '11 at 13:41
add comment

You could do something like this (with a.sql as your weekly backup).

mysqldump > b.sql
diff a.sql b.sql > a1.diff
scp a1.diff backupserver:~/backup/

Your diff files will become larger by the end of the week.

My suggestion though is just gzip it (use gzip -9 for maximum compression). We do this at the moment and that gives use a 59MB gz-file while the original is 639MB.

share|improve this answer
    
We're already gzipping them :) –  user394 Aug 18 '10 at 13:59
add comment

(I have not done this in production.)

Do a full backup once per day or week. Backup relay logs once per hour or day.

share|improve this answer
    
What's a relay log? –  user394 Aug 18 '10 at 3:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.