On 06. 04. 14 14:32, Willem de Groot wrote:
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
[2] http://aws.amazon.com/articles/1663?_encoding=UTF8&jiveRedirect=1
[3] http://forums.mysql.com/read.php?22,184590,184610
[4]
http://www.mysqlperformanceblog.com/2012/03/23/how-flush-tables-with-read-lock-works-with-innodb-tables/
[5] http://dev.mysql.com/doc/refman/5.6/en/flush.html
Hi Willem,
We are not using MySQL but Sybase. Out of that, the problem is basically
the same....
Our solution is:
1. Freeze (or lock) the db.
2. Snapshot the file system.
3. Unfreeze (or unlock) the db.
4. Do a backup on the snapshot
5. Remove the snapshot
BR, Jacques-D.
