mysqldump备份数据库时的数据一致性问题--single-transaction
Tags： MySQLmysqldump备份恢复数据一致性逻辑导出导入
在日常运维当中，经常会用到mysqldump。使用mysqldump导出数据的时候，我们最关心的问题之一就是表的一致性。 简单的说就是所有表是不是同一时间的数据和结构。 随着备份参数的不同，表的一致性和对数据库的影响也会不一样。
测试
mysqldump Ver 10.13 Distrib 5.7.22-22, for Linux (x86_64)
由于mysiam引擎的退休，这里默认谈论的都是innodb引擎的表。
情况一，在不使用任何其他参数的情况下
1 | mysqldump -h127.0.0.1 -uroot -plhr test > dump.sql |
很简单，只是指定了连接地址，账号密码，和需要导出的数据库。在没有指定参数的情况下，默认会使用lock-tables参数。官方文档参数解释如下：
For each dumped database, lock all tables to be dumped before dumping them。Because --lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.
一致性状态：
单个库里的所有表都保持一致性，库之间的表不一定能保证一致性。
general日志相关语句：
1 | ...LOCK TABLES `backup` READ /*!32311 LOCAL */,`t` READ /*!32311 LOCAL */,`t1` READ /*!32311 LOCAL */...UNLOCK TABLES |
在备份一开始就显示的一次性给所有的表加上读锁，让库在备份期间变成只读来确保表的一致性。由于是一个库一个库的备份，多个库之间的表是不一定存在一致性的。举个夸张的例子，同时导a,b两个库，a库里面的表可能是下午三点的状态，而b库里面的表却是下午4点钟的状态。
备份表可以执行的语句:
正在备份的库当中所有表的并发DML，DDL都会被阻塞，只能执行查询语句（SELECT）。
情况二，使用lock-all-tables
1 | mysqldump -h127.0.0.1 -uroot -plhr --lock-all-tables test > dump.sql |
官方参数解释
Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.
一致性状态：
所有库的所有表都能保持一致性。
general日志相关语句：
1 | ...FLUSH TABLESFLUSH TABLES WITH READ LOCK。... |
备份表可以执行的语句：
整个实例变成了只读，所有表的DDL和DML都会被阻塞，只能执行查询语句（SELECT）。
情况三，使用single-transaction
1 | mysqldump -h127.0.0.1 -uroot -plhr --single-transaction test > dump.sql |
一致性状态：
所有库的所有表都能保持一致性。
general日志相关语句：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | ... SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ SAVEPOINT sp show tables show table status like 'backup' SET SQL_QUOTE_SHOW_CREATE=1 SET SESSION character_set_results = 'binary' show create table `backup` SET SESSION character_set_results = 'utf8' show fields from `backup` show fields from `backup` SELECT /*!40001 SQL_NO_CACHE */ * FROM `backup` SET SESSION character_set_results = 'binary' use `test` select @@collation_database SHOW TRIGGERS LIKE 'backup' SET SESSION character_set_results = 'utf8' ROLLBACK TO SAVEPOINT sp ... |
设置会话级别为RR，然后开启一个会话。这里开启会话的时候多了一个WITH CONSISTENT SNAPSHOT，这个很关键。官方文档关于这两个的区别
START TRANSACTION
If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table.
下面用例子来展示上面的意思，会话隔离级别都是REPEATABLE READ。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | 会话1: 会话2: select * from backup; START TRANSACTION; Empty set (0.00 sec) insert into backup() values(now()); Query OK, 1 row affected, 1 warning (0.00 sec) select * from backup; +------------+ | shijian | +------------+ | 2020-05-09 | +------------+ ------------------------------------------------------------------------------------------------------- select * from backup; +------------+ | shijian | +------------+ | 2020-05-09 | +------------+ ------------------------------------------------------------------------------------------------------- insert into backup() values(now()); Query OK, 1 row affected, 1 warning (0.00 sec) select * from backup; +------------+ | shijian | +------------+ | 2020-05-09 | | 2020-05-09 | +------------+ 2 rows in set (0.00 sec) ------------------------------------------------------------------------------------------------------- select * from backup; +------------+ | shijian | +------------+ | 2020-05-09 | +------------+ 1 row in set (0.00 sec) |
会话1一次插入一条记录，总共插入两次。会话2只能看到第一条记录，也就是说只要执行了select语句，回话2能查询到的数据就会保持一致。如果我们添加了/*!40100 WITH CONSISTENT SNAPSHOT */，那么会话2这两条记录是都看不到的，效果就像是从会话一开始就自动执行了select * from backup。这样就保证了整个备份期间数据都是一致的。
备份表可以执行的语句：
备份库当中所有表都可以并发的执行DML和查询语句（SELECT）。但是DDL有一些特殊。
从上面的通用日志中可以看出，在刚开始备份一张表的时候，都会创建一个SAVEPOINT，备份完毕以后就会回滚到这个SAVEPOINT。在回滚以前是无法执行DDL语句的。如果执行DDL，会产生如下的锁阻塞。
1 2 3 4 5 6 7 | admin@localhost [performance_schema] 10:15:42>select * from metadata_locks where object_schema='test'; +---------------+-------------+---------------------+---------------+-------------+-----------------+ | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID | +---------------+-------------+---------------------+---------------+-------------+-----------------+ | test | backup | SHARED_READ | TRANSACTION | GRANTED | 818988 | | test | backup | EXCLUSIVE | TRANSACTION | PENDING | 818989 | +---------------+-------------+---------------------+---------------+-------------+-----------------+ |
总结
- 不加参数，默认为--lock-tables：单个库里的所有表都保持一致性，库之间的表不一定能保证一致性。
- 加上--lock-all-tables：所有库的所有表都能保持一致性。整个实例变成了只读，所有表的DDL和DML都会被阻塞，只能执行查询语句（SELECT）。
- 加上--single-transaction：所有库的所有表都能保持一致性。备份过程中，可以执行DML和DDL语句。
在使用mysqldump备份的时候，可以使用lock-tables，lock-all-tables，single-transaction三个参数来控制表的一致性问题。lock-tables和lock-all-tables都是通过显示的加上只读锁来确保表的一致性。只有single-transaction通过MVCC来确保表的一致性，并且可以并发的执行DML和DDL。大家在备份的时候一定要先了解自己备份的具体需求和备份实例可以接受什么样的影响，小心的选择这三个参数。
参考
http://blog.itpub.net/26736162/viewspace-2691903/
使用mysqldump迁移类MySQL数据库：https://www.xmmup.com/shiyongmysqldumpqianyileimysqlshujuku.html
