19

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.

14

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.

3
  • 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
  • I know the question+question is pretty old, but could you add an example of commands showing how to use it? For example for backup201901.tar.gz, backup201902.tar.gz, ..., backup201912.tar.gz, backup202001.tar.gz. This would be useful for future reference.
    – Basj
    Feb 9 '20 at 12:36
  • Last time I followed rdiff-backup, the main devs had moved on, and the project had sort of stagnated, Don't know if that changed. It was also incredibly slow over networks, if that matters.
    – Lizardx
    Feb 11 '20 at 3:24
13

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
4
  • 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
3

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.

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

There are several possible approaches one could follow, depending on the size and actual textual similarity of the database dumps:

  1. apply a deduplicating backup program that uses a rolling checksum as the OP requests, e.g. restic (https://restic.net/) or borgbackup (https://borgbackup.readthedocs.io/) on the unmodified dumps. Both of the systems allow even mounting a certain backup version via FUSE and work in a socalled forever incremental way.
  2. Decouple database structure from content, similar to how the NCBI guys do it for their quite big genetics data bases. That is: you would create SQL scripts for creating the database schema (e.g. like ftp://ftp.ncbi.nlm.nih.gov/snp/organisms/human_9606_b151_GRCh38p7/database/organism_schema/) and separately store the tables' content in either cleartext or compressed binary format without the insert statements (like done in ftp://ftp.ncbi.nlm.nih.gov/snp/organisms/human_9606_b151_GRCh38p7/database/organism_data/) e.g. as tab or comma separated values. Of course that requires a separate import routine that would create the insert statements just in time for importing the data back into the data base, i.e. restoring from the backup. In case your DBMS offers a csv file importer the requirement of the additional script above can be omitted. The so shrunken text files could then again be fed into the abovementioned or other regular backup programs like rdiff-backup.
  3. Choose a solution where structure and content are loosely coupled using a format like arff files as WEKA is using (https://www.cs.waikato.ac.nz/ml/weka/arff.html): The structure and data types of the columns would be declarated in the file header, and the actual content would then follow separated by a @DATA statement once again in csv-like form. Lots of ETL tools nowadays offer an arff reader in addition to a data base connector. The files themselves could again be fed into regular backup programs
3
  • This answer answers the question "how to do rolling backups of database dumps", but not the more general question "How to rolling backups of highly similar backups", which is what I asked
    – user394
    Feb 11 '20 at 19:49
  • Honestly I suspect that what you actually want to achieve is deduplication, which is mentioned in the 1st approach. Maybe you would like to have a look at restic.net/blog/2015-09-12/restic-foundation1-cdc where it is described, and maybe then you would like to give them a try?
    – jf1
    Feb 13 '20 at 12:19
  • This comment, fleshed out in detail, would make a much more pertinent answer than your current one.
    – user394
    Feb 13 '20 at 14:33
-3

(I have not done this in production.)

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

1
  • 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.