How to make big MySQL database more diffable/rsyncable? (aka
rsyncing big files)
krzysztof.nosek at techland.pl
Mon Jul 6 09:41:49 GMT 2009
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.
Sorry if my problem is lame, I'm relatively new to the field.
More information about the rsync