mysqldump备份时的数据一致性问题
原标题:mysqldump备份时的数据一致性问题
作者:魏新平,知数堂优秀校友。
在日常运维当中,经常会用到mysqldump。使用mysqldump导出数据的时候,我们最关心的问题之一就是表的一致性。简单的说就是所有表是不是同一时间的数据和结构。随着备份参数的不同,表的一致性和对数据库的影响也会不一样。
测试的mysqldump版本
mysqldumpVer10.13Distrib5.7.22-22,forLinux(x86_64)
由于mysiam引擎的退休,这里默认谈论的都是innodb引擎的表。
情况一,在不使用任何其他参数的情况下
- mysqldump-h127.0.0.1-uwxp-p'wxp'test>dump.sql
很简单,只是指定了连接地址,账号密码,和需要导出的数据库。在没有指定参数的情况下,默认会使用lock-tables参数。官方文档参数解释如下:
一致性状态:Foreachdumpeddatabase,lockalltablestobedumpedbeforedumpingthem。Because--lock-tableslockstablesforeachdatabaseseparately,thisoptiondoesnotguaranteethatthetablesinthedumpfilearelogicallyconsistentbetweendatabases.Tablesindifferentdatabasesmaybedumpedincompletelydifferentstates.
单个库里的所有表都保持一致性,库之间的表不一定能保证一致性。
general日志相关语句:
- ...
- LOCKTABLES`backup`READ/*!32311LOCAL*/,`t`READ/*!32311LOCAL*/,`t1`READ/*!32311LOCAL*/
- ...
- UNLOCKTABLES
在备份一开始就显示的一次性给所有的表加上读锁,让库在备份期间变成只读来确保表的一致性。由于是一个库一个库的备份,多个库之间的表是不一定存在一致性的。举个夸张的例子,同时导a,b两个库,a库里面的表可能是下午三点的状态,而b库里面的表却是下午4点钟的状态。
备份表可以执行的语句:
正在备份的库当中所有表的并发DML,DDL都会被阻塞,只能执行查询语句(SELECT)。
情况二,使用lock-all-tables
- mysqldump-h127.0.0.1-uwxp-p'wxp'--lock-all-tablestest>dump.sql
官方参数解释
一致性状态:Lockalltablesacrossalldatabases.Thisisachievedbyacquiringaglobalreadlockforthedurationofthewholedump.Thisoptionautomaticallyturnsoff--single-transactionand--lock-tables.
所有库的所有表都能保持一致性。
general日志相关语句:
- ...
- FLUSHTABLES
- FLUSHTABLESWITHREADLOCK。
- ...
整个实例变成了只读,所有表的DDL和DML都会被阻塞,只能执行查询语句(SELECT)。
情况三,使用single-transaction
- mysqldump-h127.0.0.1-uwxp-p'wxp'--single-transactiontest>dump.sql
所有库的所有表都能保持一致性。
general日志相关语句:
- ...
- SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;
- STARTTRANSACTION/*!40100WITHCONSISTENTSNAPSHOT*/
- SAVEPOINTsp
- showtables
- showtablestatuslike'backup'
- SETSQL_QUOTE_SHOW_CREATE=1
- SETSESSIONcharacter_set_results='binary'
- showcreatetable`backup`
- SETSESSIONcharacter_set_results='utf8'
- showfieldsfrom`backup`
- showfieldsfrom`backup`
- SELECT/*!40001SQL_NO_CACHE*/*FROM`backup`
- SETSESSIONcharacter_set_results='binary'
- use`test`
- select@@collation_database
- SHOWTRIGGERSLIKE'backup'
- SETSESSIONcharacter_set_results='utf8'
- ROLLBACKTOSAVEPOINTsp
- ...
设置会话级别为RR,然后开启一个会话。这里开启会话的时候多了一个WITHCONSISTENTSNAPSHOT,这个很关键。官方文档关于这两个的区别
STARTTRANSACTION
IfthetransactionisolationlevelisREPEATABLEREAD(thedefaultlevel),allconsistentreadswithinthesametransactionreadthesnapshotestablishedbythefirstsuchreadinthattransaction.
STARTTRANSACTION/*!40100WITHCONSISTENTSNAPSHOT*/
TheeffectisthesameasissuingaSTARTTRANSACTIONfollowedbyaSELECTfromanyInnoDBtable.
下面用例子来展示上面的意思,会话隔离级别都是REPEATABLEREAD。
- 会话1:会话2:
- select*frombackup;STARTTRANSACTION;
- Emptyset(0.00sec)
- insertintobackupvalues(now);
- QueryOK,1rowaffected,1warning(0.00sec)
- select*frombackup;
- +------------+
- |shijian|
- +------------+
- |2020-05-09|
- +------------+
- -------------------------------------------------------------------------------------------------------
- select*frombackup;
- +------------+
- |shijian|
- +------------+
- |2020-05-09|
- +------------+
- -------------------------------------------------------------------------------------------------------
- insertintobackupvalues(now);
- QueryOK,1rowaffected,1warning(0.00sec)
- select*frombackup;
- +------------+
- |shijian|
- +------------+
- |2020-05-09|
- |2020-05-09|
- +------------+
- 2rowsinset(0.00sec)
- -------------------------------------------------------------------------------------------------------
- select*frombackup;
- +------------+
- |shijian|
- +------------+
- |2020-05-09|
- +------------+
- 1rowinset(0.00sec)
会话1一次插入一条记录,总共插入两次。会话2只能看到第一条记录,也就是说只要执行了select语句,回话2能查询到的数据就会保持一致。如果我们添加了/*!40100WITHCONSISTENTSNAPSHOT*/,那么会话2这两条记录是都看不到的,效果就像是从会话一开始就自动执行了select*frombackup。这样就保证了整个备份期间数据都是一致的。
备份表可以执行的语句:
备份库当中所有表都可以并发的执行DML和查询语句(SELECT)。但是DDL有一些特殊。
从上面的通用日志中可以看出,在刚开始备份一张表的时候,都会创建一个SAVEPOINT,备份完毕以后就会回滚到这个SAVEPOINT。在回滚以前是无法执行DDL语句的。如果执行DDL,会产生如下的锁阻塞。
- admin@localhost[performance_schema]10:15:42>select*frommetadata_lockswhereobject_schema='test';
- +---------------+-------------+---------------------+---------------+-------------+-----------------+
- |OBJECT_SCHEMA|OBJECT_NAME|LOCK_TYPE|LOCK_DURATION|LOCK_STATUS|OWNER_THREAD_ID|
- +---------------+-------------+---------------------+---------------+-------------+-----------------+
- +---------------+-------------+---------------------+---------------+-------------+-----------------+
在使用mysqldump备份的时候,可以使用lock-tables,lock-all-tables,single-transaction三个参数来控制表的一致性问题。lock-tables和lock-all-tables都是通过显示的加上只读锁来确保表的一致性。只有single-transaction通过MVCC来确保表的一致性,并且可以并发的执行DML和DDL。大家在备份的时候一定要先了解自己备份的具体需求和备份实例可以接受什么样的影响,小心的选择这三个参数。
文章评论
共有 51 位网对文章表示很赞! 查看完整内容