Hi & thanks to Colin for making tarsnap!
Perhaps this mail belongs on a MySQL list, but as this should be a fairly common use case for tarsnap users, I hope a tarsnap+mysql best practice has evolved that I could not find ;)
Does anyone successfully use tarsnap directly on MySQL data files instead of the common intermediary `mysqldump` step? I can't use the latter, because
a) Creating a dump takes a long time (database read lock)
b) Requires a lot of free disk space (worst case without indexes: as much as the db itself)
c) Restoring takes a long time (index recreation)
IMHO, it would be more efficient to let tarsnap operate directly on the binary db files. However, there are some contradictory views about whether this is a safe operation. One guy says it's safe for InnoDB, if you just freeze your filesystem [1]. Amazon suggests "FLUSH TABLES WITH READ LOCK" with an FS freeze should work [2] but others say this is not safe for InnoDB [3]. Another disadvantage of the "FLUSH .. LOCK" mechanism is that it might hang indefinitely while waiting for long running queries [4].
Yet, the "FLUSH TABLES t1, tn FOR EXPORT" feature of MySQL 5.6.6+ [5] looks promising, as it seems to be made for this very use case. However, it only operates on InnoDB. So if your InnoDB and MyISAM tables are not or loosely coupled (like, when you only use the MyISAM tables as a full-text search cache), this might be a sensible solution. Just feed all your .idb tables to this command, (make FS snapshot,) run tarsnap and unlock. Minimal lock time.
I haven't tested it, and there are still some loose ends. Would this approach require a backup of the ib_logfile* files? Binary logs? What are the chances of data corruption?
I would love to hear how other tarsnap users solve this situation.
Cheers
Willem
[1]
http://dba.stackexchange.com/questions/52147/can-i-time-out-my-scripted-flush-tables-with-read-lock