专栏首页「3306 Pai」社区mysqldump备份时的数据一致性问题

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

作者:魏新平,知数堂优秀校友。

在日常运维当中,经常会用到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,会产生如下的锁阻塞。

    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 |

    +---------------+-------------+---------------------+---------------+-------------+-----------------+

总结

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


知数堂精品课程,MySQL实战/优化、SQL优化、Python运维自动化现已发车。课程已全面升级到MySQL 8.0版本,现在上车刚刚好,一起开启MySQL 8.0的修行之旅吧。

全文完。

本文分享自微信公众号 - 3306pai(pai3306),作者:魏新平

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 用mysqldump备份数据库时,要注意路径的问题。

    用户1272546
  • 用mysqldump备份数据库时,要注意路径的问题。

    一直用VPS自动备份脚本来备份数据(见以前的文章),以前一直没出过问题,最近从KLOXO换成WDCP后,就出现这个问题了。

    用户1191760
  • 调用外部api时的数据一致性问题

    春节又要来了,远行的小伙伴们将开始一场刺激的抢票之旅,关于购票,从程序角度上而言,大致分为这么几步: 1、 检查是否有剩余的票 2、 购票后票数减一 3、...

    java达人
  • 备份策略 - 单表备份的隐患与应对方案

    作者介绍: 刘鹏松,任职云和恩墨北区交付部,负责山东部分客户业务交付。 在通过mysqldump进行MySQL数据库(InnoDB引擎)逻辑备份的时候经常使用的...

    数据和云
  • mysqldump与innobackupex备份过程你知多少(三)

    相关阅读: mysqldump与innobackupex备份过程你知多少(二) mysqldump与innobackupex备份过程你知多少(一) mysqld...

    沃趣科技
  • Mysql备份工具mysqldump--简介

    优点: mysqldump的优点就是逻辑备份,把数据生成SQL形式保存,在单库,单表数据迁移,备份恢复等场景方便,SQL形式的备份文件通用,也方便在不同数据库之...

    陈不成i
  • mysqldump命令详解 Part 7- -single-transaction 参数的使用

    [MySQL学习笔记] 3.mysqldump命令详解 Part 2 -备份全库

    bsbforever
  • mysqldump与innobackupex备份过程你知多少(一)

    导语 1、先看mysqldump 1.1. mysqldump备份过程解读 1.2. mysqldump备份过程中的关键步骤 1.2.1. FLUS...

    沃趣科技
  • (7) MySQL数据库备份详解

    比如我们由于误操作,在主数据库上删除了一些数据,由于主从复制的时间很短,在发现时,从数据库上的数据可能也已经被删除了, 我们不能使用从数据库上的数据来恢复主数...

    用户1214487

扫码关注云+社区

领取腾讯云代金券