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.


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.

  • 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

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
  • 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
  • 1
    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 '14 at 7:16

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.

  • We're already gzipping them :) – user394 Aug 18 '10 at 13:59

(I have not done this in production.)

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

  • What's a relay log? – user394 Aug 18 '10 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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