Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Are there any techniques to use mysqldump (or similar command) to make incremental or differential backups for MySQL?

share|improve this question

3 Answers 3

You can simulate incremental backup by enabling and then backing up the binary logs. See http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html under "Making Incremental Backups by Enabling the Binary Log."

share|improve this answer

Checkout Xtrabackup (by Percona) if you are using InnoDB. It can do incrementals.

http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup:incremental?rev=1289183209

Cheers

share|improve this answer

I use binlogs but they are not an ultimate solution and I tend to find myself relying on snapshots as well.

There are two primary reasons for this:

  • Binlogs store all changes. A weeks worth of binlogs will likely be significantly larger than the difference between two snapshots a week apart. The only relief is that binlogs tend to compress well (usually well below 10%).
  • If you ever need to do auditing or go back then replaying the binlog against an ancient snapshot will take a very long time and the large the binlog the greater the chance of error so you need regular snapshots.

I had a large number of snapshots recently there were neither compressed or differential. I experimented with diff and found that even with options such as no context the resulting diffs were larger.

Without trying all the alternatives, the best thing that I found was rdiff. This reduced them to around 5% of the size for me and when then compressed with xz into the 1% zone.

Although the rdiff signature files don't compress well as they are hash collections (same as random data), compressing them against each other should yield good results.

Many people wont need such a solution as in most cases if there is a failure they will need to store the database with the most recent image and bring it up to date as quickly as possible.

However, if you have a complex system that needs a lot of accounting, auditing, debugging, etc (more critical things than a blog) then the efficient storage of snapshots becomes important.

share|improve this answer

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.