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”,精彩内容不容错过,敬请期待!!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28218939/viewspace-2142168/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28218939/viewspace-2142168/