mysqldump与innobackupex备份过程你知多少(三)

相关阅读:

mysqldump与innobackupex备份过程你知多少(二)

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备份工具。

下一篇"mysqldump与innobackupex备份过程你知多少(四)"我们将接着介绍"innobackupex”,精彩内容不容错过,敬请期待!!

本文分享自微信公众号 - 沃趣科技(woqutech)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-07-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏腾讯社交用户体验设计

ISUX Xcube智能一键生成H5

51420
来自专栏钱塘大数据

理工男图解零维到十维空间,烧脑已过度,受不了啦!

让我们从一个点开始,和我们几何意义上的点一样,它没有大小、没有维度。它只是被想象出来的、作为标志一个位置的点。它什么也没有,空间、时间通通不存在,这就是零维度。

34330
来自专栏haifeiWu与他朋友们的专栏

复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负...

30040
来自专栏Ken的杂谈

【系统设置】CentOS 修改机器名

18230
来自专栏钱塘大数据

中国互联网协会发布:《2018中国互联网发展报告》

在2018中国互联网大会闭幕论坛上,中国互联网协会正式发布《中国互联网发展报告2018》(以下简称《报告》)。《中国互联网发展报告》是由中国互联网协会与中国互联...

13750
来自专栏腾讯高校合作

【倒计时7天】2018教育部-腾讯公司产学合作协同育人项目申请即将截止!

15920
来自专栏怀英的自我修炼

考研英语-1-导学

英二图表作文要重视。总体而言,英语一会比英语二难点。不过就写作而言,英语二会比英语一有难度,毕竟图表作文并不好写。

12110
来自专栏前端桃园

知识体系解决迷茫的你

最近在星球里群里都有小伙伴说道自己对未来的路比较迷茫,一旦闲下来就不知道自己改干啥,今天我这篇文章就是让你觉得一天给你 25 个小时你都不够用,觉得睡觉都是浪费...

22340
来自专栏FSociety

SQL中GROUP BY用法示例

GROUP BY我们可以先从字面上来理解,GROUP表示分组,BY后面写字段名,就表示根据哪个字段进行分组,如果有用Excel比较多的话,GROUP BY比较类...

5.2K20
来自专栏微信公众号:小白课代表

不只是软件,在线也可以免费下载百度文库了。

不管是学生,还是职场员工,下载各种文档几乎是不可避免的,各种XXX.docx,XXX.pptx更是家常便饭,人们最常用的就是百度文库,豆丁文库,道客巴巴这些下载...

44630

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励