--single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. Option automatically turns off --lock-tables.
发布评论
评论(6)
这个不知道怎么说,我们有段时间需要备份线上财务数据进行线下核对就使用了该选项,然而核对结果却有误差,重新备份一次核对又没问题,所有表都是innodb,并且财务操作都是事务操作。
如果要确保事务的完整性(ACID),在使用 mysqldump 进行备份时可以添加 --single-transaction 选项,
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction
使用--single-transaction选项进行备份时,且事务分离级别设定为“REPEATABLE READ”时,
MySQL支持InnoDB表同时写入,但由此会消耗一部分系统资源,因此备份时间会比不使用该选项的时间长。
如果要使用该选项,建议备份时间安排在相对空闲的时间段。
另外需要注意的是只有InnoDB和NDB存储引擎支持事务。
能细说吗,我对这方面不是很熟悉
你说的双机热备,该是线上数据的热备份?
回复
线上数据热备。
看看双主配置