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

mysqldump有什么坑吗？

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

1.3.1. 坑一

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.3.2. 坑二

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 回想一下，从咱们上篇"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语句，如下：

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

到这里，是不是突然心弦一紧呢？ 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备份工具。

现在看innobackupex

2.1. innobackupex备份过程解读

A库清空查询日志

为了更清晰地追踪innobackupex是如何拷贝redo log的，我们在A库新开一个ssh会话2，使用如下脚本持续对表t_luoxiaobo进行插入操作（该表为innodb表），限于篇幅，请到如下为知笔记链接获取

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

A库使用innobackupex执行备份，使用strace命令抓取备份过程中的调用栈

查看general_log日志中的记录（删掉了加压脚本中的语句）

从上面的记录中可以看到，与mysqldump相比，innobackupex备份时对数据库的操作多了一个FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS，稍后对这句的作用进行解释

因为innobackupex是物理拷贝文件，数据并不像mysqldump那样通过对数据库表执行select语句查询进行备份，而是通过拷贝磁盘文件进行备份的，所以，主体的备份流程还需要看strace的调用栈，限于篇幅原因，详见为知笔记外链：http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl_2c2NZ33kSiac2ZRJlK3qIAQr2LjYMx2xMkCD

通过备份输出日志和strace调用栈，整理的流程图如下（全备） ：

2.2. innobackupex为什么需要这么做

nnobackupex备份时启动一个进程多个线程，通过拷贝磁盘文件实现物理备份，为了保证备份数据的一致性，需要在备份过程中恰当的时机发送一些加锁解锁语句与数据库实例进行交互，so…要了解innobackupex工具的整个备份过程中做了哪些事情，我们就需要查看general_log和备份过程中的日志输出（其实strace调用栈信息里就可以了解到innobackupex所做的所有事情，但是。。都是系统调用，看起来比较费劲），对于备份过程中的日志输出，这里就不再熬述，详见上文中的"全备流程图"，本小节我们只介绍general_log中的输出重点语句，如下：

• FLUSH NO_WRITE_TO_BINLOG TABLES、FLUSH TABLES WITH READ LOCK、SHOW MASTER STATUS、UNLOCK TABLES几个语句的作用与mysqldump备份过程中的这几个语句的作用一样

• FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS，该语句在mysqldump备份过程中没有 
  * 这句的作用是在所有的事务表和非事务表备份完成，获取了全局读锁，且使用SHOW MASTER STATUS语句获取了binlog pos之后，执行刷新redo log buffer中的日志到磁盘中，然后redo log copy线程拷贝这最后的redo log日志数据（为什么说是最后的redo log日志数据呢？因为此时使用FLUSH TABLES WITH READ LOCK加锁之后，使用UNLOCK TABLES释放全局读锁之前，不会再有新的请求进来，），拷贝完成之后就停止copy线程并关闭xtrabackup_logfile文件。然后再使用UNLOCK TABLES释放全局读锁。 
  * 详见姜承尧老师的推文：http://chuansong.me/n/372118651979

2.3. innobackupex有什么坑吗？

从上文中介绍的innobackupex的备份流程和原理上，我们可以得知，innobackupex工具备份过程中是不会出现前面提到的mysqldump备份工具的"坑一"的。因为innobackupex备份工具是在所有事务表和非事务表都备份完成之后才会执行UNLOCK TABLES释放全局读锁，so…从加锁之后，解锁之前不可能有任何其他的DML请求能够对数据做修改，从而保证的备份数据的一致性。

那么，mysqldump的"坑二"呢？我们来看下面请看演示过程

A库使用如下脚本持续对表t_luoxiaobo进行插入操作（该表为innodb表），限于篇幅，请到如下为知笔记链接获取(留意把program_name变量值改为"innobackupex")

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

A库新开一个ssh会话2，执行innobackupex备份，留意日志打印过程。从下面的结果中，我们可以看到报错终止了：

发生什么了？

• 首先，我们知道，innobackupex在备份事务表时，是没有对数据库加锁的，so..这个时候，其实DDL是允许执行的，innobackupex持续在备份innodb事务表期间，如果被执行DDL的表是在innobackupex备份完成之后发起，那么在下一次scan lsn的时候innobackupex将发现DDL更改，报错终止，如果是在备份非事务表期间发起的DDL，那么将被FLUSH TABLE WITH READ LOCK语句阻塞。所以，对于使用innobackupex备份的生产环境，要执行DDL语句，也需要避开备份时间

那么，除了这个，还有其他坑吗？

• 前面在介绍mysqldump备份过程中的FLUSH TABLES和FLUSH TABLES WITH READ LOCK语句的时候，提到过三个注意事项，innobackupex备份过程中为了获得一个一致性备份，仍然会使用这两个语句对数据库进行刷新表缓存、加全局读锁，也就是说，mysqldump使用这两个语句可能会踩到的坑，在innobackupex中也会碰到，如下： 
  * 1）、如果一个会话中使用LOCK TABLES语句对某表加了表锁，在该表锁未释放前，那么另外一个会话如果执行FLUSH TABLES和FLUSH TABLES WITH READ LOCK语句会被阻塞，而如果数据库中lock_wait_timeout参数设置时间太短，innobackupex将会因为执行FLUSH TABLES WITH READ LOCK语句获取全局读锁超时而导致备份失败退出 
  * 2）、如果一个会话正在执行DDL语句，那么另外一个会话如果执行FLUSH TABLES和FLUSH TABLES WITH READ LOCK语句会被阻塞，而如果数据库中lock_wait_timeout参数设置时间太短，innobackupex将会因为执行FLUSH TABLES WITH READ LOCK语句获取全局读锁超时而导致备份失败退出 
  * 3）、如果一个会话正在执行DML大事务（DML语句正在执行，数据正在发生修改，而不是使用lock in share mode和for update语句来显式加锁），那么另外一个会话如果执行FLUSH TABLES和FLUSH TABLES WITH READ LOCK语句会被阻塞，而如果数据库中lock_wait_timeout参数设置时间太短，innobackupex将会因为执行FLUSH TABLES WITH READ LOCK语句获取全局读锁超时而导致备份失败退出

• 但是，细心的童鞋可能已经发现了，innobackupex备份时的general_log中执行FLUSH NO_WRITE_TO_BINLOG TABLES语句之前，有这样一句语句：SET SESSION lock_wait_timeout=31536000，备份时会在session级别把锁超时时间改了，so…除了加表锁忘记释放之外，其他两种情况估计不太可能碰到锁超时的情况！！

当然，如果每天备份一次，那么我们不太可能让innobackupex在备份时，获取全局读锁时等待31536000秒，so……我们可以使用innobackupex的选项--kill-long-queries-timeout，来再获取全局读锁时，如果某查询阻塞了获得该FLUSH TABLE WITH READ LOCK语句时间超过这个阀值，那么就对该会话执行kill，杀掉这个连接，当然，你也许会说对数据做修改的不能杀，只能杀查询的，那么我们可以使用--kill-long-query-type=all|select选项。下面列出这俩选项的含义：

• --kill-long-query-type=all|select 
  * 该选项指定哪些类型的查询在指定的查询时间之后还没有执行完成时被kill掉，以释放阻塞加全局读锁的锁，默认值为all，有效值有：all和select 
  * 执行该选项需要有process和super权限

• --kill-long-queries-timeout=SECONDS

  * 该选项指定innobackupex在执行FLUSH TABLES WITH READ LOCK时碰到阻塞其获得锁的查询时，等待该参数指定的秒数之后，如果仍然有查询在运行，则执行kill掉这些查询 
  * 默认值为0，表示innobackupex 不启用尝试kill掉任何查询的功能
  

PS：

• 很多人喜欢在备份前先flush binary logs一把，其实在有大事务对数据进行修改时，一不小心可能就会出现数据库hang死，所以不建议这么做

• innobackupex备份期间，在数据库中创建的连接不要误杀，否则备份失败

3、总  结

作为专职的DBA：

• 我们一定一定要保持一种高度谨慎的态度，在数据库备份方案选型时，一定要根据自己的业务场景充分测试，校验，尽可能地把可能出现的深坑挖出来。

• 除了寻找适合自己的，可行的备份方案之外，更应该做好备份校验（备份是否成功完成、备份文件是否损坏）、备份恢复演练（备份文件是否可以正常恢复数据），以备不时之需。

• 对生产库的DDL操作、大事务、或者长时间锁表的操作，一定要避开备份时间，否则，你懂的……

注：

本小节演示的xtrabackup版本基于2.4.4，如果xtrabackup版本小于2.3，备份过程中的系统调用有一些不太一样，详情请参考链接：http://mysql.taobao.org/monthly/2016/03/07/

全文参考链接：

• https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

• https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_single-transaction

• https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data

• https://dev.mysql.com/doc/refman/5.7/en/commit.html

• https://dev.mysql.com/doc/refman/5.7/en/flush.html

• https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html

• https://dev.mysql.com/doc/refman/5.7/en/savepoint.html

• https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/innobackupex_option_reference.html

转载于:https://blog.51cto.com/moerjinrong/1949178