How to make big MySQL database more diffable/rsyncable? (aka rsyncing big files)

[Krzysztof Nosek]

Hello group,


I'm having a very hard time rsyncing efficiently a MySQL database which 
contains very large binary blobs.
(Actually, it's the database of Mantis bug tracker 
[http://www.mantisbt.org/], with file attachments stored directly in the 
table rows. I know it's a bad idea from many other reasons, but let's 
say it was given to me as such.)

First, I was dumping the database with mysqldump to an uncompressed dump 
and rsyncing it. Such a dump would use some 34G of disk space. My rsync 
took no less than two hours then, regardless of number of actual changes 
in the database. (Later I realized that my dump was perhaps not enough 
line-oriented, so I tried some --skip-extended-insert options of 
mysqldump, with no luck.)

Currently I'm using mysqlhotcopy to rsync raw database data. It still 
takes no less than one hour (which is 50% of the previous results, 
mostly because the raw data of the database is just 17G. The single 
biggest file is 16909M).

How to do it better? I thought that rsync would be magically able to 
extract changes from my data. However, it looks like it diffs the 17G 
file everyday again and again, even it stayed intact. The 
times/attributes of files do not change across the copies either, as 
mysqlhotcopy preserves them.

Please help!
Sorry if my problem is lame, I'm relatively new to the field.

Regards,
Krzysztof Nosek