MySQL 高频面试题解析 第08期：物理备份和逻辑备份的区别

作者简介

马听，多年 DBA 实战经验，对 MySQL、 Redis、ClickHouse 等数据库有一定了解，专栏《一线数据库工程师带你深入理解 MySQL》、《Redis 运维实战》作者。

这是专栏《MySQL 高频面试题解析》的最后一篇，感谢您的阅读。

这个专栏前 4 节由无为完成，后四节由马听完成，文稿互相审核。总结了两位作者在这些年自己或者朋友遇到过的高频面试题，希望能和你一起讨论这些问题。

由于能力有限，系列文章难免会存在错误或者遗漏，如果您有任何建议，可以私信给“悦专栏”公众号，或者加文末“小悦”微信进行私信，我们会第一时间进行反馈。

下面进入今天的内容。

在工作中，MySQL 的备份是一项重点工作，而备份策略及备份方式，也是我们经常讨论的话题，在面试中也少不了一些关于备份相关问题的讨论。

MySQL 的备份工具有很多，本节内容就选逻辑备份工具中使用最多的 mysqldump 和物理备份工具中使用最多的 xtrabackup 来进行对比讲解。

1 mysqldump 备份恢复原理

首先来聊聊 mysqldump 的备份原理：

• 与 server 建立连接，并初始化 session；
• 执行 FTWRL(flush tables with read lock)；
• 设置当前会话隔离级别为 RR；
• 开启事务并创建快照；
• 获取当前 binlog 位置；
• 解锁所有表；
• 对指定的库和表进行 dump；
• 备份完成。

而使用 mysqldump 备份出的文件要进行恢复，实际就是执行 SQL 文件的过程，这里就不展开讲解了。

2 Xtrabackup 备份恢复原理

再来看看 xtrabackup 的备份原理：

• 开启 redo log 拷贝线程，从最新的检查点开始顺序拷贝 redo log；
• 拷贝事务引擎表的数据文件；
• 等到事务引擎数据文件拷贝结束，通知调用 FTWRL；
• 备份非事务引擎数据文件及其他文件；
• 获取 binlog 位点信息；
• 停止复制 redo log；
• 解锁 UNLOCK TABLES；
• 释放锁，备份结束；
• 备份完成。

同时也讲下 xtrabackup 的恢复原理：

• 模拟 MySQL 进行 recover，将 redo log 回放到数据文件中；
• 等到 recover 完成，重建 redo log；
• 将数据文件复制到 MySQL 数据目录；
• 完成还原。

恢复的目的实际可以看成把备份的数据恢复到一个一致性位点的过程，那么怎么保证事务引擎和非事务引擎在同一个位点呢？

又回到备份时的逻辑，因为非事务引擎是在执行 FTWRL 后进行的数据文件拷贝，这个过程数据库处于只读的，因此非事务引擎对应的就是 FTWRL 的位点。

而 InnoDB 的 idb 文件拷贝是在 FTWRL 前做的，拷贝出来的不同表的 idb 文件最后更新时间点很可能不一样，但是 InnoDB 的 redo log 是从备份开始一直持续拷贝的，拷贝一直持续到 FTWRL 后，所以最终通过应用 redo log 的 idb 数据位点也是和 FTWRL 一致的。

3 两者的区别

看完两个工具的备份原理，再来聊聊它们的区别：

3.1 加锁时间

两个工具都会对 MySQL 加全局读锁，但是 mysqldump 在备份开始的时候加的；而 xtrabackup 是在拷贝完事务引擎表的数据文件后，再加的全局读锁。

3.2 备份恢复时间

由于 mysqldump 备份时，实际是去数据库中执行：

select * from table_name;

恢复是在数据库里导入备份出的 SQL 语句。

而 xtrabackup 备份时，是拷贝的物理文件；

恢复时直接复制物理文件。

因此 xtrabackup 备份恢复的时间要比 mysqldump 短很多。

3.3 适用场景

正是因为 mysqldump 备份时产生表结构和数据的 SQL 语句，因此其适用于数据量较少、跨版本数据库备份恢复、单库单表备份等场景。

而 xtrabackup 适用于大数据量、整库备份等场景。

专栏《MySQL 高频面试题解析》系列文章推荐

第01期：一条 update 语句的生命历程

第02期：当前读和快照读的区别

第03期：InnoDB 怎么做表空间迁移

第04期：RR 隔离级别下真的不会产生幻读吗

第05期：MVCC 怎么实现的

第06期：复制的演进历程

第07期：有哪些死锁场景

第08期：物理备份和逻辑备份的区别