Rsync efficiency on SQL dump files

Callum Macdonald at
Wed Oct 1 12:30:42 GMT 2008


I'm backing up production MySQL database servers. The tables are almost
all MyISAM.

My plan is to use MySQL binary logging and then rsync the binary logs
offsite hourly. The binary log files are only appended to, with new
queries logged at the end of the file. So I'm assuming the rsync
algorithm will be highly effective at reducing the bandwith required to
transfer these files.

My question is regarding MySQL dump files. From one backup to the next,
I'd estimate that the MySQL data will be 90% consistent. So the dump
file will have 10% new data. That new data will be scattered at random
points through the file.

Typically I'd pipe the output from mysqldump into gzip. Then I'd copy
the gzipped file offsite. I wonder if saving the file uncompressed, and
then using rsync to copy the file offsite will be more efficient.

Any advice? Will rsync be effective at finding the 90% consistency
between the new dump file and the old dump file? Will it be able to
transfer only the 10% of the file that has changed?

I'd welcome any other advice or feedback on my proposed setup.

Cheers - Callum.

PS> Apologies if this double posted. I tried it a few days ago but
haven't seen it on the list yet.

More information about the rsync mailing list