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

电玩女神 2022-06-12 03:43 228阅读 0赞

## 沃趣科技  罗小波 ##

mysqldump有什么坑吗？

想必大家都知道，mysqldump备份时可以使用--single-transaction + --master-data两个选项执行备份（老实讲，为图方便，本人之前很长一段时间，生产库也是使用mysqldudmp远程备份的），这样备份过程中既可以尽量不锁表，也可以获取到binlog pos位置，备份文件可以用于数据恢复，也可以用于搭建备库。看起来那么美好，然而，其实一不小心你就发现自己已经在坑里了。

1.3.1. 坑一

使用--single-transaction + --master-data时，myisam表持续不断插入，并用于搭建备库。

首先在A库上把myisam表的数据行数弄到100W以上

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

A库新开一个ssh会话2，使用如下脚本持续对表t\_luoxiaobo2进行插入操作（该表为myisam表），限于篇幅，请到如下为知笔记链接获取：

http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl\_2c2NZ33kSiac1Rgvxq1vgkhL21ibWU2cLidk

A库新开一个ssh会话3，清空查询日志：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

现在，A库在ssh会话3中，使用mysqldump备份整个实例

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2][]

备份完成之后，A库在ssh会话2中，停止持续造数脚本

A库在ssh会话2中，查看备份文件中的binlog pos

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3][]

A库在ssh会话3中，查看查询日志，可以发现在UNLOCK TABLES之后，select \*…t\_luoxiaobo2表之前，还有数据插入到该表中：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4][]

现在，我们将这个备份文件用于B库上搭建备库，并启动复制，可以发现有如下复制报错：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5][]

从上面的结果中可以看到，主键冲突了，也就是说备份的表t\_luoxiaobo2中的数据与备份文件中获取的binlog pos点并不一致，咱们现在在B库中，查询一下这个表中大于等于这个冲突主键的数据，从下面的结果中可以看到，备份文件中如果严格按照一致性要求，备份文件中的数据必须和binlog pos点一致，但是现在，备份文件中的数据却比获取的binlog pos点多了5行数据：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6][]

现在，咱们去掉--single-transaction选项，重新执行本小节以上步骤，重新搭建从库，看看是否还有问题（这里限于篇幅，步骤省略，只贴出最后结果）：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7][]

从上面的show slave status输出信息中我们可以看到，去掉了--single-transaction选项之后的备份，用于搭建备库就正常了。另外，我们重新在A库上查看查询日志也可以发现，只搜索到flush语句而没有搜索到unlock tables、set session transaction.. 、start transaction.. 语句，说明备份过程没有开启一致性快照事务，没有修改隔离级别，是全程加全局读锁的，mysqldump备份进程结束退出之后mysql server自动回收锁资源：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8][]

也许你会说，我们数据库环境很规范，没有myisam表，不会有这个问题，OK，赞一个。

1.3.2. 坑二

使用--single-transaction + --master-data时，innodb表执行online ddl，备份文件用于搭建备库（注意，本小节中的数据库实例与前一小节不同）。

这次我们操作Innodb表，在A库上先把t\_luoxiaobo表的数据也弄到几百万行。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 9][]

A库在ssh会话2中，使用如下脚本持续对表t\_luoxiaobo进行DDL操作（该表为innodb表），限于篇幅，请到如下为知笔记链接获取：

http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl\_2c2NZ33kSiac0tjwkE3KHkhU2\_9gwt3mTldI

A库在ssh会话3中，清空查询日志：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 10][]

现在，A库在ssh会话3中，使用mysqldump备份整个实例：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 11][]

A库在ssh会话2中，停止DDL添加脚本。

A库在ssh会话2中，查看备份文件中的binlog pos：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 12][]

现在，我们将这个备份文件用于在B库中搭建备库，并启动复制，从下面的结果中可以看到，复制状态正常：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 13][]

现在我们回到A库上，对表t\_luoxiaobo插入一些测试数据：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 14][]

在B库上查询复制状态和表t\_luoxiaobo中的数据：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 15][]

到这里，看起来一切正常，对不对？开心吗？先等等，请保持DBA一贯严谨的优良传统，咱们在主库上使用pt-table-checksum工具检查一下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 16][]

从上面的信息中可以看到，表luoxiaobo.t\_luoxiaobo的检测DIFFS 列为16，代表主从有数据差异，神马情况？别急，咱们先来分别在AB库查询下这张表的数据行数，从下面的结果可以看到，该表主从数据差异2097152行！！！

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 17][]

发生什么了？也许你会说，平时使用mysqldump不都是这样的吗？没毛病啊。

*  回想一下，从咱们上篇"[mysqldump与innobackupex备份过程你知多少（二）][mysqldump_innobackupex]"中 提到的"WITH CONSISTENT SNAPSHOT语句的作用" 时的演示过程可以知道，DDL的负载是刻意加上去的，还记得之前演示mysqldump使用savepoint的作用的时候，使用start transaction with consistent snapshot语句显式开启一个事务之后，该事务执行select之前，该表被其他会话执行了DDL之后无法查询数据，我们知道mysqldump备份数据的时候，就是在start transaction with consistent snapshot语句开启的一个一致性快照事务下使用select语句查询数据进行备份的。

为了证实这个问题，下面我们打开查询日志查看一下在start transaction with consistent snapshot语句和select … 之间是否有DDL语句，如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 18][]

现在，我们打开备份文件，找到表t\_luoxiaob的备份语句位置，可以看到并没有生成INSERT语句：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 19][]

到这里，是不是突然心弦一紧呢？ so……如果你决定继续使用mysqldump，那么以后搭建好备库之后，一定要记得校验一下主备数据一致性！！！

1.3.3. 有办法改善这这些问题吗？

在寻找解决办法之前，咱们先来看看mysqldump的备份选项--single-transaction和--master-data\[=value\]的作用和使用限制。

*  \--single-transaction   
    \* 此选项将事务隔离模式设置为REPEATABLE READ，并在备份数据之前向server发送START TRANSACTION SQL语句以显示开启一个事务快照。仅适用于InnoDB这样的事务表，由于是在事务快照内进行备份，这样可以使得备份的数据与获取事务快照时的数据是一致的，而且不会阻塞任何应用程序对server的访问。   
    \* 在进行单事务备份时，为确保有效的备份文件（正确的表内容和二进制日志位置），不能有其他连接应使用语句：ALTER TABLE，CREATE TABLE，DROP TABLE，RENAME TABLE，TRUNCATE等DDL语句。这会导致一致状态被破坏，可能导致mysqldump执行SELECT检索表数据时查询到不正确的内容或备份失败
    
    \* 注意：该选项仅适用于事务引擎表，对于MyISAM或MEMORY表由于不支持事务，所以备份过程中这些引擎表的数据仍可能发生更改
 *  \--master-data\[=value\]   
    
    
    \* 使用此选项备份时会在备份文件中生成change master to语句，使用的binlog pos是使用的备份server自己的binlog pos，可使用备份文件用于将另一台服务器（恢复这个备份文件的服务器）设置为备份server的从库。   
    \* 与--dump-slave选项类似，如果选项值为2，则CHANGE MASTER TO语句将作为SQL注释写入备份文件，因此仅供参考;当备份文件被重新加载时，这个注释不起作用。如果选项值为1，则该语句不会注释，并在重新加载备份文件时会生效（被执行）。如果未指定选项值，则默认值为1。  
    \* 指定此选项的用户需要RELOAD权限，并且server必须启用二进制日志，因为这个位置是使用show master status获取的（如果没有开启log\_bin参数，则show master status输出信息为空），而不是使用show slave status获取的。   
    \* --master-data选项自动关闭 --lock-tables选项。同时还会打开--lock-all-tables，除非指定了--single-transaction选项，在指定了--single-transaction选项之后，只有在备份开始时间内才加全局读取锁。

so……--single-transaction选项中明确说明了如果使用了该选项，那么在备份期间如果发生DDL，则可能导致备份数据一致性被破坏，select检索不到正确的内容。另外，该选项仅仅只适用于事务引擎表，不适用于非事务引擎。作为DBA，很多时候是非常无奈的，虽然有各种规范，但是保不齐就是有漏网之鱼，这个时候，生活还得继续，工作还得做好， 那么，有什么办法可以缓解这个问题吗？有的：

*  就如同上文中演示步骤中那样，去掉--single-transaction选项进行备份，此时单独使用--master-data选项时会自动开启--lock-all-tables，备份过程中整个实例全程锁表，不会发生备份数据与获取的binlog pos点不一致的问题，这样，用该备份来搭建备库时就不会出现数据冲突。但是问题显而易见，备份期间数据库不可用，如果采用这种方法，至少需要在业务低峰期进行备份。
 *  使用innobackupex备份工具。

> 下一篇"mysqldump与innobackupex备份过程你知多少（四）"我们将接着介绍"innobackupex”，精彩内容不容错过，敬请期待！！

**mysqldump有什么坑吗？**

**1.3.1. 坑一**

使用--single-transaction + --master-data时，myisam表持续不断插入，并用于搭建备库。

首先在A库上把myisam表的数据行数弄到100W以上

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

A库新开一个ssh会话2，使用如下脚本持续对表t\_luoxiaobo2进行插入操作（该表为myisam表），限于篇幅，请到如下为知笔记链接获取：

http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl\_2c2NZ33kSiac1Rgvxq1vgkhL21ibWU2cLidk

A库新开一个ssh会话3，清空查询日志：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

现在，A库在ssh会话3中，使用mysqldump备份整个实例

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2][]

备份完成之后，A库在ssh会话2中，停止持续造数脚本

A库在ssh会话2中，查看备份文件中的binlog pos

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3][]

A库在ssh会话3中，查看查询日志，可以发现在UNLOCK TABLES之后，select \*…t\_luoxiaobo2表之前，还有数据插入到该表中：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4][]

现在，我们将这个备份文件用于B库上搭建备库，并启动复制，可以发现有如下复制报错：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6][]

现在，咱们去掉--single-transaction选项，重新执行本小节以上步骤，重新搭建从库，看看是否还有问题（这里限于篇幅，步骤省略，只贴出最后结果）：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8][]

也许你会说，我们数据库环境很规范，没有myisam表，不会有这个问题，OK，赞一个。

**1.3.2. 坑二**

使用--single-transaction + --master-data时，innodb表执行online ddl，备份文件用于搭建备库（注意，本小节中的数据库实例与前一小节不同）。

这次我们操作Innodb表，在A库上先把t\_luoxiaobo表的数据也弄到几百万行。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 9][]

A库在ssh会话2中，使用如下脚本持续对表t\_luoxiaobo进行DDL操作（该表为innodb表），限于篇幅，请到如下为知笔记链接获取：

http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl\_2c2NZ33kSiac0tjwkE3KHkhU2\_9gwt3mTldI

A库在ssh会话3中，清空查询日志：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 10][]

现在，A库在ssh会话3中，使用mysqldump备份整个实例：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 11][]

A库在ssh会话2中，停止DDL添加脚本。

A库在ssh会话2中，查看备份文件中的binlog pos：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 12][]

现在，我们将这个备份文件用于在B库中搭建备库，并启动复制，从下面的结果中可以看到，复制状态正常：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 13][]

现在我们回到A库上，对表t\_luoxiaobo插入一些测试数据：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 14][]

在B库上查询复制状态和表t\_luoxiaobo中的数据：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 15][]

到这里，看起来一切正常，对不对？开心吗？先等等，请保持DBA一贯严谨的优良传统，咱们在主库上使用pt-table-checksum工具检查一下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 16][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 17][]

发生什么了？也许你会说，平时使用mysqldump不都是这样的吗？没毛病啊。

为了证实这个问题，下面我们打开查询日志查看一下在start transaction with consistent snapshot语句和select … 之间是否有DDL语句，如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 18][]

现在，我们打开备份文件，找到表t\_luoxiaob的备份语句位置，可以看到并没有生成INSERT语句：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 19][]

到这里，是不是突然心弦一紧呢？ so……如果你决定继续使用mysqldump，那么以后搭建好备库之后，一定要记得校验一下主备数据一致性！！！

**1.3.3. 有办法改善这这些问题吗？**

在寻找解决办法之前，咱们先来看看mysqldump的备份选项--single-transaction和--master-data\[=value\]的作用和使用限制。

> 下一篇"mysqldump与innobackupex备份过程你知多少（四）"我们将接着介绍"innobackupex”，精彩内容不容错过，敬请期待！！

[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1]: /images/20220612/2e0fe04445114e2ead67becab7ab568e.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1]: /images/20220612/c40e4b39565c400388d048ca09adabd7.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2]: /images/20220612/2ba5d8af87614967ae8631ebe3206db5.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3]: /images/20220612/d99e377cdecd4a48939c411482c15f7c.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4]: /images/20220612/3b0ab2b5c6b84ca8aa491440495444b8.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5]: /images/20220612/94cd3e6c03524b4195e7f5b213e24d05.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6]: /images/20220612/4e63ed1346474981b2566a526f232dcd.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7]: /images/20220612/4187aec6b43a41fb82f3e03a13e6ba81.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8]: /images/20220612/7fe2ef18b6344df4ab081ae66de46baa.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 9]: /images/20220612/7585e087eceb44bc8bac21f76d7d8485.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 10]: /images/20220612/4fd15b2c11c844c48aed984802b2862b.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 11]: /images/20220612/19d97be36efe4f198525240a05303855.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 12]: /images/20220612/ae2b25a1f27d42b8a07d2d24d6e9b5c9.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 13]: /images/20220612/17fad0a576b14cde9f66ff8f2f0704da.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 14]: /images/20220612/7e4a202c81194c37b22d8d164af47893.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 15]: /images/20220612/aa1c2fd13f7e4866b65e1e521eecb1ac.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 16]: /images/20220612/5a8ff97693b54a239009453e9aa31e46.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 17]: /images/20220612/f4b4896a5afa49f0924068f91f34dcb4.png
[mysqldump_innobackupex]: http://mp.weixin.qq.com/s?__biz=MjM5NjUwNjEyMQ==&mid=2650547928&idx=1&sn=b12a6ba31a30668901806baf31a7d926&chksm=bee0b9be899730a845fde780e549e8169e1fd30b6ca4ceab14f4aa34925a9485e90d3123a7fc&scene=21#wechat_redirect
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 18]: /images/20220612/f46da608901449d0982995597a799178.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 19]: /images/20220612/e64233e1f5eb4b5b8ff17f15123152a1.png