mysqldump备份时的数据一致性问题

心已赠人 2023-03-13 09:26 14阅读 0赞

#  #

> 作者：魏新平，知数堂优秀校友。

在日常运维当中，经常会用到mysqldump。使用mysqldump导出数据的时候，我们最关心的问题之一就是表的一致性。简单的说就是所有表是不是同一时间的数据和结构。随着备份参数的不同，表的一致性和对数据库的影响也会不一样。

### 测试的mysqldump版本 ###

mysqldump Ver 10.13 Distrib 5.7.22-22, for Linux (x86\_64)

由于mysiam引擎的退休，这里默认谈论的都是innodb引擎的表。

#### 情况一，在不使用任何其他参数的情况下 ####

mysqldump -h127.0.0.1 -uwxp -p'wxp'  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日志相关语句： #####

...
    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 ####

mysqldump -h127.0.0.1 -uwxp -p'wxp' --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日志相关语句： #####

...
    FLUSH TABLES
    FLUSH TABLES WITH READ LOCK。
    ...

##### 备份表可以执行的语句： #####

整个实例变成了只读，所有表的DDL和DML都会被阻塞，只能执行查询语句（SELECT）。

#### 情况三，使用single-transaction ####

mysqldump -h127.0.0.1 -uwxp -p'wxp' --single-transaction  test  > dump.sql

##### 一致性状态： #####

所有库的所有表都能保持一致性。

##### general日志相关语句： #####

...
    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.

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:    
    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，会产生如下的锁阻塞。

### 总结 ###

在使用mysqldump备份的时候，可以使用lock-tables，lock-all-tables，single-transaction三个参数来控制表的一致性问题。lock-tables和lock-all-tables都是通过显示的加上只读锁来确保表的一致性。只有single-transaction通过MVCC来确保表的一致性，并且可以并发的执行DML和DDL。大家在备份的时候一定要先了解自己备份的具体需求和备份实例可以接受什么样的影响，小心的选择这三个参数。

--------------------

*  由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。
    
    ![format_png][]
    
    --------------------
    
    另外，叶老师在腾讯课堂《**MySQL性能优化**》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。
    
    下面是自动拼团的二维码，组团价仅需78元
    
    ![format_png 1][]

[format_png]: /images/20230312/64753b252e3848e7ac91f441c220c0dc.png
[format_png 1]: /images/20230312/514b8ab60f6a44539278e9ca9505bbc8.png