2015-11-05 9:49:46
作者：容 易
数据库版本:mysql5.6.20
昨天在群里和大家一起热烈讨论了mysqldump --single-transaction在备份过程中执行某些DDL操作造成数据不一致而导致备份失败的情况。感觉颇有收获，也更加深入的理解了mysql事物隔离级别以及不同模式下的一致性读，经过实际的测试和对讨论的总结以及个人的理解总结如下，如有错误和遗漏请大家及时留言指出谢谢。
mysqldump --single-transaction 基于innodb引擎测试,只有在RR模式下才能够实现单事物备份。因为RR模式下的一致性读是以事物开始为准,并且可重复读和避免了幻读,而mysqldump的实际操作是针对每张表单独进行逻辑备份，例如在RC模式下，第一张表的备份是一个查询可以保证该表的一致性读，但是进行第二张表备份的时候，可以查询到备份第一张表备份过程中对第二张表的已提交操作，所以只有RR模式才能完成单事物的备份。
mysqldump --single-transaction 首先会确认本次单事物备份需要备份那些表，备份过程是通过SELECT /*!40001 SQL_NO_CACHE */ * FROM `goods`类似方式获取备份表的数据，一张表接一张表进行备份，而且并未在备份开始就对所有需要备份表的metadata lock进行锁定并且全程持有，所以在备份过程中可以对非正在进行备份的表执行ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE等操作，
这些操作会改变表结构造成备份结果不一致导致备份失败。例如备份开始时A表有id name 2个字段，在备份其他表的过程中A表加了一个字段变成id name mail并且在其他表备份过程中已经完成添加字段的操作，当备份到A表时，进行SELECT /*!40001 SQL_NO_CACHE */ * FROM `A`获取数据，无法发现多了一个字段这样就会出现数据不一致的情况，具体表现有以下三种情况，所有操作都是在备份已经开始并且还未结束的情况下进行。
情况一、进行DDL操作的表是已经被Mysqldump备份过的表，对本次备份没有影响，正常完成备份。(备注:对存在外键或者触发器的情况未测试)
情况二、进行DDL操作的表是发生在备份其他表的时候完成，并且该表还未完成备份，当备份到该表时，发现该表是在本次事物备份过程中被DDL操作过，mysqldump将会提升相关错误并且直接退出，提醒用户重新备份。
情况三、备份过程中，假如DDL操作的是当前正在备份的表，将会处于Waiting for table metadata lock状态等待该表备份结束才能够进行，对本次备份无影响。(备注:对存在外键或者触发器的情况未测试)
其他例如DROP TABLE, RENAME TABLE等操作也与以上三种情况基本类似，例如DROP TABLE, RENAME TABLE当备份过程中删除或重命名了某张表，mysqldump备份过程中发现表不存在了，mysqldump将会提升相关错误并且直接退出，提醒用户重新备份。
另外文档中提到了 CREATE TABLE也可能造成备份失败，我自己在测试过程中新建了一张独立的表，备份一切照旧顺利备份完成并且未对新表进行备份，或许是没有测试到相关的触发条件，还请大家指点。
One Response