[Linux] MySQL Incremental rsync

[Danny Rathjens]

Kwan Lowe wrote:
>> I have an off site server with a dedicated ADSL link which I want to
>> rsync a nightly MySQL backup too.  The result of the mysqldump operation
>> is aprox 500mb, which takes way too long over this kind of link.  If I
>> follow this process, will rsync only submit the changes (i.e. new
>> additions to the db?)
>>
>> on MySQL Server perform a nightly:
>> rm -rf /var/backup
>> mysqldump to /var/backup
>> rsync /var/backup to remote box
>>
>> Or will rsync pickup the entire dump file again?  Obviously I wouldn't
>> be able to use gzip or bz2...
>>
> 
> This sounds like something that the binary log may handle. That way you don't have
> to dump the entire database, just send what has changed. There's also a few links
> online about replication and DR recovery that use the binary log so those may be a
> good start.

That's a good idea.  Continuing in the original vein, though, rsync does not have
the ability to detect diffs in files, it copies the whole thing if it has been modified.
That's why I use rdiff-backup.  It only transfers patches for differences in changed files.

Also, that's a pretty small database. :)  I'd just compress the dump and copy the whole thing.
SQL compreses very well.  500 MB of SQL will compress to around 20 MB.

And using mysqlhotcopy (so you are just copying the db files instead of generating the verbose sql
necessary to recreate them a la mysqldump) would decrease that 500MB by quite a bit as well.