mysqldump与innobackupex备份过程你知多少（一）

罗小波·沃趣科技沃趣技术 2023-03-10

作者罗小波·沃趣科技高级数据库技术专家

出品沃趣科技

| 导读

想必搞数据库的都知道：

mysqldump优点：mysqldump的优点就是逻辑备份，把数据生成sql形式保存，在单库，单表数据迁移，备份恢复等场景方便，sql形式的备份文件通用，也方便在不同数据库之间移植。对于innodb表可以在线备份。
mysqldump缺点：mysqldump是单线程，数据量大的时候，备份时间长，甚至有可能在备份过程中非事务表长期锁表对业务造成影响(sql形式的备份恢复时间也比较长)。mysqldump备份时会查询所有的数据，这可能会把内存中的热点数据刷掉
innobackupex优点：物理备份可以绕过mysql server层，加上本身就是文件系统级别的备份，备份速度块，恢复速度快，可以在线备份，支持并发备份，支持加密传输，支持备份限速
innobackupex缺点：要提取部分库表数据比较麻烦，不能按照基于时间点来恢复数据，并且不能远程备份，只能本地备份，增量备份的恢复也比较麻烦。如果使用innobackupex的全备+binlog增量备份就可以解决基于时间点恢复的问题。

要查看备份过程中这俩备份工具都对数据库做了什么操作，想必大家都知道：可以打开general_log来查。那么问题来了，general_log输出的信息都代表什么？如果不这样做会怎样？这两个备份工具会不会有什么平时被忽略的坑？请看下文分析，也许……你会发现原来之前对这俩备份工具好像也不是那么了解!

环境信息

服务器配置：
* CPU：4 vcpus
* 内存：4G
* 磁盘：250G SAS
* 网卡：Speed: 1000Mb/s
操作系统：CentOS release 6.5 (Final)
数据库版本：MySQL 5.7.17
xtrabackup版本：2.4.4
主从IP（文中一些演示步骤需要用到主备复制架构）：
* 主库：192.168.2.111（以下称为A库）
* 从库：192.168.2.121（以下称为B库）
数据库关键配置参数
* 主库：双一，log_slave_updates，log-bin，binlog_rows_query_log_events=ON，server-id=3306111，gtid_mode=ON，enforce_gtid_consistency=ON，auto_increment_increment=2，auto_increment_offset=1
* 备库：双一，log_slave_updates，log-bin，binlog_rows_query_log_events=ON，server-id=3306121，gtid_mode=ON，enforce_gtid_consistency=ON，auto_increment_increment=2，auto_increment_offset=2
测试库表创建(这里在同一个库下创建两个表，一个表为innodb引擎，一个为myisam引擎)

| 先看mysqldump

1.mysqldump备份过程解读

通常，使用mysqldump备份期间，为了使得数据库中加锁时间尽量短，会使用--single-transaction选项来开启一个一致性快照事务，为了使得备份期间能够获得一个与数据一致的binlog pos点，会使用--master-data选项，现在登录A库主机，使用这俩选项执行备份演示。

先在数据库中打开general_log：

使用mysqldump备份（使用strace捕获执行过程中的调用栈），这里紧以备份测试库luoxiaobo为例进行演示：

备份完成之后，查看general_log中的内容（去掉了一些无用信息）：

查看strace抓取的调用栈信息，限于篇幅，详见为知笔记链接：

http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl_2c2NZ33kSiac3oxBB40tGQNY2L6Z_M2LtLbG

上面的strace信息是不是看起来和general_log中的信息很像啊？因为general_log中记录的就是mysqldump发送过去的sql语句：

从上面general_log和strace信息对比我们可以知道，strace信息代表了mysqldump进程对数据库进程发送了哪些请求信息，general_log代表了数据库中所有的客户端sql请求操作记录，这就是大家熟知的mysqldump备份过程中的关键步骤，那么。。问题来了，mysqldump备份过程中为什么需要这些步骤？不这么做会怎样？下面对这些步骤逐一使用演示步骤进行详细解释

2. mysqldump备份过程中的关键步骤

（1） FLUSH TABLES和FLUSH TABLES WITH READ LOCK的区别

FLUSH TABLES

强制关闭所有正在使用的表，并刷新查询缓存，从查询缓存中删除所有查询缓存结果，类似RESET QUERY CACHE语句的行为
在MySQL 5.7官方文档描述中，当有表正处于LOCK TABLES … READ语句加锁状态时，不允许使用FLUSH TABLES语句（另外一个会话执行FLUSH TABLES会被阻塞），如果已经使用LOCK TABLES … READ语句对某表加读锁的情况下要对另外的表执行刷新，可以在另外一个会话中使用FLUSH TABLES tbl_name … WITH READ LOCK语句（稍后会讲到）
注意：

* 如果一个会话中使用LOCK TABLES语句对某表加了表锁，在该表锁未释放前，那么另外一个会话如果执行FLUSH TABLES语句会被阻塞

* 如果一个会话正在执行DDL语句，那么另外一个会话如果执行FLUSH TABLES 语句会被阻塞

* 如果一个会话正在执行DML大事务（DML语句正在执行，数据正在发生修改，而不是使用lock in share mode和for update语句来显式加锁），那么另外一个会话如果执行FLUSH TABLES语句会被阻塞

FLUSH TABLES WITH READ LOCK

关闭所有打开的表，并使用全局读锁锁定整个实例下的所有表。此时，你可以方便地使用支持快照的文件系统进行快照备份，备份完成之后，使用UNLOCK TABLES语句释放锁。
FLUSH TABLES WITH READ LOCK语句获取的是一个全局读锁，而不是表锁，因此表现行为不会像LOCK TABLES和UNLOCK TABLES语句，LOCK TABLES和UNLOCK TABLES语句在与事务混搭时，会出现一些相互影响的情况，如下：
* 如果有表使用了LOCK TABLES语句加锁，那么开启一个事务会造成该表的表锁被释放（注意是任何表的表锁，只要存在表锁都会被释放，另外，必须是同一个会话中操作才会造成这个现象），就类似执行了UNLOCK TABLES语句一样，但使用FLUSH TABLES WITH READ LOCK语句加全局读锁，开启一个事务不会造成全局读锁被释放
* 如果你开启了一个事务，然后在事务内使用LOCK TABLES语句加锁和FLUSH TABLES WITH READ LOCK语句加全局读锁（注意，是对任何表加表锁，只要使用了LOCK TABLES），会造成该事务隐式提交
* 如果你开启了一个事务，然后在事务内使用UNLOCK TABLES语句，无效
* 官方文档中还有一句："如果有表使用LOCK TABLES语句加表锁，在使用UNLOCK TABLES语句解锁时会造成该表的所有事务隐式提交"，个人认为这是理论上的说法，或者说本人能力有限，暂未想到可能会造成这种情况的原因，因为实际上使用LOCK TABLES语句语句时，开启一个事务会造成自动解锁（前面已经提到过），而如果在事务内使用LOCK TABLES语句会造成事务隐式提交（前面已经提到过），所以实际上不可能出现在事务内使用UNLOCK TABLES语句解锁LOCK TABLES语句的情况，而如果是使用FLUSH TABLES WITH READ LOCK语句，如果执行该语句之前存在LOCK TABLES加的表锁，则FLUSH TABLES WITH READ LOCK语句发生阻塞，如果是已经执行FLUSH TABLES WITH READ LOCK语句，LOCK TABLES语句发生阻塞，不会再有任何的表锁和互斥锁能够被获取到（新的非select和show的请求都会被阻塞）。所以不可能出现UNLOCK TABLES语句解锁时造成隐式提交
注：
* FLUSH TABLES WITH READ LOCK语句不会阻塞日志表的写入，例如：查询日志，慢查询日志等
* FLUSH TABLES WITH READ LOCK语句与XA协议不兼容
* 如果一个会话中使用LOCK TABLES语句对某表加了表锁，在该表锁未释放前，那么另外一个会话如果执行FLUSH TABLES WITH READ LOCK语句会被阻塞，而如果数据库中lock_wait_timeout参数设置时间太短，mysqldump将会因为执行FLUSH TABLES WITH READ LOCK语句获取全局读锁超时而导致备份失败退出
* 如果一个会话正在执行DDL语句，那么另外一个会话如果执行FLUSH TABLES WITH READ LOCK语句会被阻塞，如果数据库中lock_wait_timeout参数设置时间太短，mysqldump将会因为执行FLUSH TABLES WITH READ LOCK语句获取全局读锁超时而导致备份失败退出
* 如果一个会话正在执行DML大事务（DML语句正在执行，数据正在发生修改，而不是使用lock in share mode和for update语句来显式加锁），那么另外一个会话如果执行FLUSH TABLES WITH READ LOCK语句会被阻塞，如果数据库中lock_wait_timeout参数设置时间太短，mysqldump将会因为执行FLUSH TABLES WITH READ LOCK语句获取全局读锁超时而导致备份失败退出

FLUSH TABLES tbl_name [，tbl_name] … WITH READ LOCK

刷新表并获取指定表的读锁。该语句首先获取表的独占MDL锁，所以需要等待该表的所有事务提交完成。然后刷新该表的表缓存，重新打开表，获取表读锁（类似LOCK TABLES … READ），并将MDL锁从独占级别降级为共享。在该语句获取表读锁、降级MDL锁之后，其他会话可以读取该表，但不能修改表数据及其表结构。
执行该语句需要RELOAD和LOCK TABLES权限
该语句仅适用于基表（持久表），不适用于临时表，会自动忽略，另外在对视图使用该语句使会报错。
与LOCK TABLES语句类似，在使用该语句对某表加锁之后，再同一个会话中开启一个事务时，会被自动解锁
MySQL5.7官方文档描述说：这种新的变体语法能够使得只针对某一个表加读锁的同时还能够同时刷新这个表，这解决了某表使用LOCK TABLES … READ语句加读锁时，需要刷新表不能使用FLUSH TABLES语句的问题，此时可以使用FLUSH TABLES tbl_name [，tbl_name] … WITH READ LOCK语句代替，但是，官方描述不太清晰，实测在同一个会话中使用LOCK TABLES … READ语句加读锁时，不允许执行该语句（无论操作表是否是同一张表），会报错：ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction，但是如果在不同的会话中，那么，如果表不相同，允许执行，表相同，则FLUSH TABLES tbl_name [，tbl_name] … WITH READ LOCK语句发生等待
该语句同一个会话重复执行时，无论是否同一个表，都会报错：ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transactio，如果是不同会话不同表则允许执行，但是表相同则发生等待

（2) 修改隔离级别的作用

为什么要执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ语句呢？因为后续需要使用START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT语句开启一个一致性事务快照，根据事务一致性读要求，一致性事务快照只支持RR隔离级别，在其他隔离级别下执行语句START TRANSACTION /!40100 WITH CONSISTENT SNAPSHOT会报如下警告信息：

限于篇幅，本文将分期进行推送，下一篇"mysqldump与innobackupex备份过程你知多少（二）"我们将接着介绍"mysqldump备份过程中的关键步骤"之"使用WITH CONSISTENT SNAPSHOT子句的作用"与"使用savepoint来设置回滚点的作用"，精彩内容不容错过，敬请期待！！

| 作者简介

罗小波·沃趣科技高级数据库技术专家