Incremental/Differential MySQL Backup using mysqldump

Question

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

Answer 1

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."

Answer 2

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

Answer 3

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.

Answer 4

Check https://sourceforge.net/projects/mysqlincrementalbackup/ script. An incremental backup solution for MyISAM and Innodb.