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

[Krzysztof Nosek]

Hi,

thanks for your tips... even if I'm still stuck with my problem :)
> * Does the output of mysqldump compress well with gzip?
>   
No it doesn't. The biggest table we talk about here consists mainly of 
large binary blobs which are media files in compressed formats such as 
jpg, avi, png, etc.
But I was actually trying to rsync uncompressed dumps, being aware that 
compression brings a huge load of entropy.
> * If you're confident that you have a good line-oriented dump, how
>   does diff cope when comparing 2 dumps?
>   
I was never patient enough to measure the effectiveness of such diffs. 
But comparing only parts of the dumps I can see that they are _very_ 
stable - just single lines added/changed across the dumps. Diffing dumps 
(or fragments) is not quite feasible because even if it's properly 
line-oriented, the lines are very very VERY long. It therefore takes 
lots of memory and time to get anything.
> * If you're not telling rsync to transfer just a single file, do the
>   filenames match at both ends of the transfer?
Anyway, I'm slowly becoming convinced that one has to remove the file 
blobs from the database entirely, moving them to the flat structure in a 
directory which would be rsynced separately. There is such feature in 
Mantis, as far as I know. I think it would give huge boost from the file 
list being basically constant, which is nearly impossible to achieve 
when all the files are glued together in a single large binary something.

Regards,
 nosek