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