专栏首页「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

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

作者:魏新平
原始发表时间:2020-05-12
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

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

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

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

    用户1272546
  • MySQLDump的备份方法

    mysqldump 是 MySQL 系统自带的逻辑备份工具,主要用于转储数据库。它主要产生一系列的 SQL 语句,可以封装到文件,该文件包含重建数据库所需要的 ...

    用户8989785
  • [安全] mysqldump 备份的后门

    1. 简介 mysqldump 是备份 MySQL 数据库的常用工具,其中会包含 创建表、删除表、插入数据 这些数据库操作的语句 而黑客可能会利用 mysqld...

    dys
  • MySQLDump的备份方法

    mysqldump 是 MySQL 系统自带的逻辑备份工具,主要用于转储数据库。它主要产生一系列的 SQL 语句,可以封装到文件,该文件包含重建数据库所需要的 ...

    用户9236362
  • 备份Mysql数据库时提示"mysqldump: command not found"解决方法

    一般我们在备份数据库的时候,比较小的数据库可以用phpmyadmin或Navicat Premium工具进行备份还原,如果数据库比较大或者可能考虑到完整性,还是...

    4xx.me
  • MySQL数据备份mysqldump的简单使用

    MySQLdump是一个数据库逻辑备份程序,可以使用对一个或者多个mysql数据库进行备份或者将数据传输到其他mysql服务器。执行mysqldump时需要账户...

    星哥玩云
  • 调用外部api时的数据一致性问题

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

    java达人
  • 基于mysqldump聊一聊MySQL的备份和恢复

    Hi,大家好,我是麦洛,今天我们聊聊MySQL的备份和恢复,在下面文章中,你会了解到MySQL常见的备份类型,以及基于mysqldump命令在日常开发中如何做M...

    麦洛
  • mysqldump备份表中有大字段失败的排错过程

    几天前收到某个业务项目,MySQL数据库逻辑备份mysqldump备份失败的邮件,本是在休假,但本着工作认真负责,7*24小时不间断运维的高尚职业情操,开始了D...

    星哥玩云
  • 小白学习MySQL - mysqldump保证数据一致性的参数差异

    MySQL数据库选择mysqldump做逻辑备份,以前可能就只写一条简单的指令,但实际上,为了保证数据的一致性,这里面是很有些讲究的,尤其是数据量大且同时存在并...

    bisal
  • mysqldump 把数据库备份到异地的服务器

    这个方法可以把通过mysqldump 把本地数据库备份到远端主机, 中间数据的传输通过 ssh 加密。 远端主机上通过cat 读取标准输入然后把mysqldum...

    好派笔记
  • 备份的原因 备份会出现哪些问题

    在工作和学习中,经常会制做一些文档,但是可能会出现一些问题,例如电脑突然没电,导致没来得及保存。如果重做就会非常浪费时间,所以这个时候就需要在做的时候,即使没做...

    用户8739990
  • mysqldump与innobackupex备份过程你知多少(三)

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

    沃趣科技
  • MySQLdump里的秘密,终于被我发现了

    1 Part1 引言 在日常数据库运维中,经常要对数据库进行热备。热备的一个关键点是保证数据的一致性,即在备份进行时发生的数据更改,不会在备份结果中出现。my...

    腾讯云数据库 TencentDB
  • XtraBackup备份时出现的socket报错解决

    今天为公司新建的uat数据库使用XtraBackup备份时,出现了报错,将解决方法整理、做一下备忘:

    星哥玩云
  • 确保混合云备份与灾难恢复的数据一致性

    为了使备份和灾难恢复成功工作,数据必须同步。这些技巧有助于IT团队确保数据一致性。 ? 理想的世界中,如果混合云平台的一部分出现问题,处理只会减慢,然后自动恢复...

    静一
  • (7) MySQL数据库备份详解

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

    用户1214487

扫码关注腾讯云开发者

领取腾讯云代金券