概述：mysqldump是MySQL官方自带的备份工具，被广泛使用着。本文详细介绍了如何使用mysqldump获得一个一致的备份，以及可能遇到的一些问题。

1. 是什么备份的一致性？

一致性对于备份来说是非常重要的，如果一个备份不具备一致性，那么再恢复之后，可能会让软件出现各种奇怪的问题。我们来详细看看什么是备份的一致性，例如，一个备份流程中，首先耗时一分钟（12:00:00-12:01:00）完成了用户表U表备份，然后继续其他业务表A、B等的备份，于此同时，业务线程开始向用户表U写入新注册用户X的信息，接着，业务线程又向事件表E写入X用户的事件Y信息。接着，备份线程在完成其他业务表之后，开始并完成备份事件表E。

如果不考虑一致性，在备份事件表E的时候，会记录X用户的事件信息，但是在用户表U中却没有该用户的注册信息，也就出现了不一致的数据。如果事件U表记录的是，企业核心数据，例如账务、计费等信息，恢复这样数据，则难以达到备份与恢复的目的。

一致性的备份是指，所有备份中的数据，可以对应到数据库在某一时刻的状态。在上面的案例中，就要求，所有备份的数据与用户表U开始备份时刻的数据处于同一个状态。

2. mysqldump备份的一致性保障方式

2.1 使用 –lock-tables, -l

默认情况下，参数–opt是打开的，所以，不加任何相关参数的话，会默认带上参数–lock-tables，该参数，会在备份某个数据库之前，将该数据库的所有的表都加上锁，阻止所有的写入操作，所以，总是可以让单个数据库保持一致的状态。

2.2 使用–lock-all-tables, -x

另一个加锁的参数是–lock-all-tables, -x，与–lock-tables差别在于，该参数是在备份任务开始时，将需要备份的所有数据库的所有表，都加上锁，阻止所有的写入操作，所以，使用该参数，就可以获得整个实例级别的一致性，而–lock-tables参数是可以获得单个数据库级别的一致性。当然，如果你的实例中，数据库数量非常多，而且关联性并不大，则还是应该尽量使用-l参数，避免加锁时间过长。

2.3 使用最常用 –single-transaction

–single-transaction参数应该是mysqldump备份中最有用的参数了。对于InnoDB表，使用该参数一方面可以获得一致性的数据，另一方面，也不需要在备份期间持续的对数据库进行加锁操作。

一般来说，使用了该参数，就可以获得一个一致的备份，并且在备份过程中，无需阻塞读取或者写入操作。

3. 使用–single-transaction的一些例外情况

因为MySQL两层架构设计，导致了Server层和引擎层在很多功能上并不能很好的契合。在使用–single-transaction参数备份时，如果数据库层正在执行某些DDL，那么还是可能会出现不一致。

在mysqldump的文档中也明确提到，如果在mysqldump执行过程中，数据库上执行了ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE等DDL，还是会有不一致出现。因为文档写得比较简单，而一致性又是一个比较大的问题，所以，这里详细探讨一下这种情况。

3.1 –single-transaction备份时，如果有ALTER TABLE，数据是否还一致？

在备份的过程中，如果有部分表执行了ALTER TABLE操作，这部分表是否还可以正常备份呢？这样的备份是否有一致性？

简单的回答，在使用–single-transaction参数备份时，如果执行了ALTER TABLE，并且该操作属于COPY（或INPLACE）类型，那么可能会导致备份数据出现错误，事实上，可能会导致，该表中的数据无法被备份出来。

这是一个mysqldump的限制。因为与MySQL事务、DDL实现机制、InnoDB事务机制都有较深的关系，所以，并不容易绕过，这个问题的底层原因在很早就已经在Bug系统中汇报了，但是一直都没有好的、彻底的修复策略，参考：MySQL Bug#28432。当然，也可能是修复的代价太高，而收益比较小。

从原理上，简单的来说，当对InnoDB表做DDL操作时，而且该DDL是一个COPY类型的（ALGORITHM为COPY的时候），MySQL会先对该表加上一个全局的锁，不允许任何的写操作，然后新建好一个临时表（新的结构），然后将原表中的数据拷贝到新的临时表中，完成拷贝之后，然后再将原表删除，并将新的临时表重命名为原表。在这个过程中，所有新拷贝的数据都使用新的事务ID，而原表的数据又被删除了。所以，在这个DDL之前开始的事务，都不再能够读取DDL之后的新表的数据，即便这个新表的数据本身并没有被其他任何事务修改。从事务一致性的角度来看，这应该是不可以被接受的，使用了Repeatable Read隔离级别的事务，在某个时刻开始之后，能够读取的数据，应该总是一致的。所以，也比较明确，这就是MySQL的一个已知的限制。

3.2 ALTER语句的ALGORITHM到底是COPY、INPLACE，还是INSTANT呢？

那么一个ALTER TABLE语句的DDL到底是COPY、INPLACE，还是INSTANT呢？这个问题没有一个简单答案，也不再本文的讨论范围之内，详细内容可以参考：

• ALTER TABLE Statement@dev.mysql.com
• Online DDL Operations@dev.mysql.com

另外，因为INSTANT是8.0版本才引入的，所以，5.7的版本要么是COPY、要么是INPLACE。

3.3 延伸说明，ALTER语句对于事务隔离性的破坏

某些ALTER语句执行时，是会破坏事务的隔离性的。这里也做了一个简单的测试验证：

在上面的例子中可以看到，如果在实际的备份中，先备份t_b，而另一个线程对t_a进行了ALTER操作的DDL（注：并且是ALGORITHM为COPY）时，备份线程再读取t_a的数据，就会失败，在实际的mysqludmp备份中，就只能备份t_a的表结构（show create table可以执行，且显示的是新的表结构），但无法备份该表的数据。

3.4 –single-transaction与其他DDL语句

除了ALTER语句之外，其他还有CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE等语法，都会破坏InnoDB RR隔离级别下的一致性，所以也都会有类似的问题。这些DDL相比ALTER要更简单一些（没有ALGORITHM选项），如果在mysqldump执行过程中执行这些DDL（如果是上面示例中的顺序），也会有类似的不一致问题，这里就不再详述。

3.5 –single-transaction与FLUSH TABLES WITH READ LOCK

mysqldump在–single-transaction和–master-data（–source-data）结合使用的时候，会通过FTWRL命令将数据库锁住，并获取全局的、一致的日志位点。但是，因为MySQL的设计原因，FTWRL比较容易导致数据库阻塞，尤其是数据库的负载比较大的时候，参考：

• Xtrabackup: Handling FLUSH TABLES WITH READ LOCK
• Introducing backup locks in Percona Server@Percona 2014年
• FLUSH Statement@dev.mysql.com
• MySQL Bugs#71017 ：mysqldump creates useless metadata locks
• FLUSH TABLE WITH READ LOCK详解@天士梦

所以，如果备份发生在主库，且负载比较大，则会有一定概率阻塞数据库，导致服务不可用。

4. 小结

虽然，说了这么多问题。但是，总体上mysqldump和–single-transaction组合起来用，通常都能够帮助你获得一份有效的、一致的备份。

但是，如果你是负责一个大型系统（数据库非常多）的备份，数据库实例的数量非常多，开发人员也非常多，那么虽然概率小，但依旧一定会遇上这些情况。希望本文能够帮助你理解这种现象，以及尽量避免这种情况的发生。例如，可以考虑在备库/副本上备份，或者使用Xtrabackup的物理备份作为补充等。

本文重点介绍了MySQL领域最新的书籍：《MySQL实战》，以及2020左右推出的书籍《MySQL是怎样运行的》。

《MySQL实战》，作者陈臣

上个月，MySQL领域又发布了一本新书：《MySQL实战》，作者是陈臣，来自Oracle的工程师，一直活跃在MySQL数据库领域。本文概要的介绍一下，这是一本怎样的书，以及合适哪些人阅读。

相比于官方手册，该书籍在实践方面是更接地气的，这一点上，这本书也是MySQL官方手册的很好补充。

例如，如果想系统的了解MySQL的备份，本书在第五章系统的介绍各种MySQL的备份方法。涵盖了官方提供的mysqldump，也包括在社区使用非常广泛的mydumper和XtraBackup。如果只是阅读官方手册的话，你可能只会看到关于mysqldump的相关介绍，但在实际的生产使用中，尤其是海量数据备份时，XtraBackup和mydumper都被广泛使用。

再比如，大表DDL一直是MySQL的一个”硬伤“，在系统运行较长时间后，都会遇到这个令人头疼的问题。本书的第七章就叫”DDL“，单独的介绍了这个online DDL的现状以及常用的解决方案。不仅包含了官方MySQL中的online DDL支持，也涵盖了在社区最为广泛使用的两个工具pt-osc和gh-ost。

《MySQL是怎样运行的》

另外，也补充介绍一下2020年的书《MySQL是怎样运行的》。这本书封面上写的作者名字是”小孩子4919“，可见作者更加有个性，这也体现在书籍中。书中，一般会用打比方的方式介绍或者引入数据库的一些概念，然后再较为深入的进行介绍。非常适合新手从零开始学习MySQL，了解他的基本概念，以及对应的原理。在覆盖面上，涵盖了诸如安装配置、字符集、InnoDB、查询优化基础、事务等相关内容，并且都尽可能从非常基础的开始讲起。

例如，在介绍MySQL启动选项和系统变量的时候，作者使用了手机中的“设置”进行类比；再比如，很少有书籍会介绍“字符集和比较规则”，但这也是一个新手难以理解的地方，本书则使用了独立章节进行介绍，并从计算机底层的二进制存储开始引入结束，可以说是深入浅出。

当然，作者也做了一些取舍，也就只能放弃非常深入全面的解析每个模块的细枝末节。总得来说，这本书作为打开MySQL大门的引导，是非常合适的。

是《MySQL实战》，不是《MySQL实战45讲》

另外，“MySQL实战”在领域还有一个非常有名系列是由丁奇和极客时间推出“MySQL实战45讲”。本书和该系列没有什么直接关系。

最后

最后，搜索引擎这么强大，为什么还要读书？相比互联网上零散的信息与知识，书籍则提供完整的、系统的介绍某个领域的知识。而，作为专业领域的从业者，通常都需要了解领域的方方面面，书籍或者手册则是非常好开始。

疫情来到之初，因为对于病毒和传播了解较少，难以感受到疫情在过去的三年如此巨大的改变了大家的生活。而这次ChatGPT变革则是处在自己所在的互联网/基础科技领域，已经感受到了ChatGPT将较为深刻的改变很多内容。

(more…)