Home / server / Questions / 130903
In Process
wurlog
Asked: 2010-04-10 04:33:15 +0800 CST

How to backup database dumps which only minor changes between the backups?

  • 772

I have a posgresql dumpfile every night and i like to keep every one of them. Each is 14 GB in size, so my backupdrive will be full soon.

The difference from one day to another are only some 100MB. How do i make the daily backups without wasting a lot of space.

PS: i used tar to compress 1 file and the size went down to 5GB. I hoped that when i compress two files the ratio would be better, but no. 2 dumps compressed are 10Gb

backup mysql files

5 Answers

  1. I would like to continue on the Tometzyk's mention of rdiff and suggest the backup software rdiff-backup. It keeps a mirror copy of the most recent backup, and saves history as compressed reverse diffs. Those times I've used it on database dumps I've remember have goten away with a quite nice (small) delta. That is, you have your mysqldump create a new file with the same filename each dump, and then you run rdiff-backup on that dump.

    rdiff-backup can either be run locally (including towards a network mount) as well as across ssh. Feel free to look at the rdiff-backup example page for a few common usage scenarios.

    • 1

  2. Here's an alternative approach; since your incremental backup size is small, it's likely that only a few MySQL tables are changing regularly, and you're probably backing up a number of large tables that don't change from day to day. If you can figure out whether this is the case, you could back up the entire database on a reduced schedule - maybe once a week - and take more frequent backups of the more volatile tables, using:

    mysqldump [database name] [table1 table2 ....]

    Here's a link to the mysqldump documentation.

    • 1

  3. rdiff from librsync is an opensource program that will compute difference (so called deltas) between any (binary or text) files.

    There's also a backup system — duplicity — which uses librsync for efficient storage of backups.

    • 0

  4. There are binary differential prgrams around. Tometzky mentioned rdiff. I've used bsdiff before, but it doesn't work on large files.

    Consider running something like LZMA to compress the dump/diff file after you extract it. From my experience most average 3:1 compression (though images and video do worse, text much better, normal stuff). LZMA is considerably better than GZIP and BZIP found in Tar.

    • 0

  5. You can archive your files with 7zip utility and pack it as solid archive

    • 0