日均万条数据丢失,一张思维导图纵观MySQL数据安全体系

作者:亚搏app官网    发布时间:2020-02-09 12:43    浏览:150 次

[返回]

作者介绍

作者介绍 :杨奇龙,前阿里数据库团队资深DBA,主要负责淘宝业务线,经历多次双十一,有海量业务访问DB架构设计经验。目前就职于有赞科技,负责数据库运维工作,熟悉MySQL性能优化、故障诊断、性能压测。

洪凌,新炬网络资深MySQL工程师,超过7年MySQL数据库运维经验,擅长数据库运维体系、集群架构建设,熟悉MySQL性能优化,对数据库监控系统也有着独特的理解。

简介

主从复制作为MySQL的精髓,有两大困难:主从数据的延时与数据的不一致性。针对数据不一致的排查处理,相信各位大佬们都有丰富的处理经验,我就不多说了,本文主要是给大家分享一个工作中遇到的奇葩事例:由于一个极隐式的骚操作,导致从库丢失数据!

和团队内部的同事一起沟通,讨论了MySQL数据库系统数据安全性问题,主要针对MySQL丢数据 、主从不一致的场景 ,还有业务层面使用不得当导致主备库数据结构不一样的情况,本文是基于以上的讨论和总结做的思维导图。

环境描述

思维导图内容展示OSBBU:数据库服务器要配置BBU,BBU在电源供应出现问题的时候,为RAID控制器缓存提供电源。当电源断电时,BBU电力可以使控制器内缓存中的数据可以保存一定时间(根据BBU的型号而决定)。用户只需要在BBU电力耗尽之前恢复正常供电,缓存中的数据即可被完整的写回RAID中,避免断电导致数据丢失防止OS异常断电导致数据无法正常落盘磁盘禁用cache,MySQL的 O_DIRECT 方式可以跳过pagecache写数据单机

业务环境:短时间内,业务蓬勃发展,客户量从一两万一下增加到几十万用户。

(1)redo log

数据库环境,如下图:

innodb_flush_log_at_timeout

问题描述

5.6.6: 每隔一秒将redo log buffer中的数据刷新到磁盘

某天,主库10.0.0.1的CPU使用率突然升高,均值达到80%+,导致Keepalived的VIP漂移至从库10.0.0.2。理论上丢失的是切换过程中的几秒钟数据,业务侧对丢失的这几秒数据表示没关系,那么这个事件到此应该就结束了。

= 5.6.6:每隔innodb_flush_log_at_timeout秒将数据刷新到磁盘中去

然而当天晚上,业务打电话过来说:丢失了部分用户信息,导致用户登入不了,要求帮忙恢复数据并查找数据丢失的原因。本文对数据恢复这块就不具体展开了,稍微提一下,这边因为新主10.0.0.2已经有数据写入,只能对比用户表数据把新主少的数据导入进去。

(2)binlog

初步排查

sync_binlog =1

对于主从复制丢失数据,解决方法没有捷径,只能老老实实地跟踪数据复制过程,查看是哪里出了问题。

(3)innodb buffer data

选择丢失数据中时间比较近的,时间为2017-06-09 13:36:01,ID为849791这条数据,来跟踪整个复制过程,因为日志只保留14天的。

不同的flush mathod刷数据的图形展示。图片来自hatemysql.com。

分析主库binlog日志,binlog日志中是有这条记录的。

(4)InnoDB 落盘

分析从库日志:因为数据库配置了relay_log_purge与log-slave-updates,所以中继日志已经找不到这个时间点了,只能查看从库binlog日志。然而在从库的binlog日志中并未找到这条记录,说明这条数据是未执行,排除后期人为删除的,那么数据为何不被执行呢?或者说数据是在执行过程中丢失的?

MySQL数据落盘的路径,图片来自李春hatemysql.com。

数据分析

主从不一致主库insert之后再回滚 ,主备库自增主键不一致使用replace into操作,导致主备库自增主键不一致set session sql_log_bin=0业务架构

无可用的中继日志怎么办?难道没办法查了?于是我决定观察和对比一下丢失的数据,看看其中是否含有什么规律,是不定时丢失数据,还是会在某些特定时刻丢失数据。

常见的双写

整理了一下某表丢失的数据,通过观察、分析丢失数据的属性:

“丢”数据的场景

从图中可以看出,丢失的数据的插表时间都是在每分钟的前两秒。这不由地让人思考,为何丢失的数据是每分钟前2秒的呢?而且数据丢失的时间间隔也不是很长,基本隔几天就肯定有数据丢失。经过这样分析,事情似乎就变得简单了。

(1)slave_skip_counter 不合理

深入调查

slave_skip_counter =1slave_skip_counter 1

首先,关闭log-slave-updates、relay_log_purge等一切影响判断的额外参数设置,等待一段时间后,再来对比某表新数据丢失情况。

(2)DB Crash,OS正常

可以看到又有新数据丢失,根据这些丢失数据再来排查问题。

innodb_flush_log_at_trx_commit=0事务提交时,不刷新缓存,系统刷新的频率是1s,故会丢失1s的数据。

首先先查主库,查看主库的binlog日志状体信息状态。

innodb_flush_log_at_trx_commit=1事务提交时,会刷新到磁盘,保证事务落盘,故不丢数据。

就以2017-6-17 15:17:02最新丢失的这条数据来跟踪,查看主库10.0.0.2上的binlog日志中是否存在这条数据:

innodb_flush_log_at_trx_commit=2事务提交时,刷新到os cache,系统没有crash,数据无丢失。

结果显示主库是存在这条数据的。

(3)DB正常,OS Crash

在登入从库查看relay-log日志情况,发现relay-log日志生成太频繁,每分钟生成1个relay-log日志,而且有些文件大小又不一致。那么这套主从集群,业务肯定部署过分割relay-log日志的脚本,而且应该是flush来强制刷新的。如图:

带有 BBU

我们先来看从库relay-log日志中是否存在这条数据,查找17分生成的relay日志,提取前两秒能匹配的插入情况。

innodb_flush_log_at_trx_commit=0事务提交时,不刷新缓存,系统刷新的频率是1s,故会丢失1s的数据。

发现并没有这条insert操作,难道数据未写入relay日志,刷新日志时导致事务丢失? 把查询时间拉长至50秒。

innodb_flush_log_at_trx_commit=1事务提交时,会刷新到磁盘,保证事务落盘,故不丢数据。

发现也没有这条数据,并且数据跟前面两秒的一致,那么其它数据呢?会不会在下一个日志中?赶紧去18分生成的relay日志查看,发现这条数据在15:18分这个relay日志中的第一个事务的位置。

innodb_flush_log_at_trx_commit=2事务提交时,刷新到os cache,系统没有crash,数据无丢失。

那么是没有执行,还是执行过程丢失?仔细观察主库binlog与从relay-log日志的记录,也没能看出什么名堂,从事务开始到commit,都一样。

(4)slave非实时写redo和binlog丢失数据

问题定位

在slave机器上会存在三个文件来保证事件的正确重放:relay log、 relay log info、 master info。

如果一条数据无法比较,那就再随机拿出几条丢失的数据来跟踪。发现情况都一样,数据都已经复制到relay日志中,而且内容根本看不出为何不能执行。

(5)异步模式

唯一有疑点的是这些事务都在日志的第一个事务中。顿时,我有种想法,会不会强制刷新relay日志,造成日志的第一个事务有时不执行,或执行过程中丢失?如果是脚本引起的,那么除去这些事务未执行外,肯定还有其它事务也未执行。

事务T1写入binlog buffer;dumper线程通知slave有新的事务T1;binlog buffer进行checkpoint;slave因为网络不稳定,一直没有收到t1;master挂掉,slave提升为新的master,t1丢失。

那么,我就随机选择几个relay-log日志,找到第一个事务。

(6)semi sysnc

具体分析如下:

after_commit

再登入从库查询结果:

比如主库操作update t1 set val=1 where id=10将val从5修改为1 。

可以看出从库数据并未更新。

会话session1在主库提交update t1 set val=1 where id=10 ;commit;主库根据二阶段提交将数据持久化到innodb和提交日志binlog;同步日志到slave ,并等待slave 返回ack信息,等待的实际时间以 rpl_semi_sync_master_timeout 为准,超过该设置时间则超时,主库返回给客户端成功写入信息。接收到来自slave的ack信息,返回成功给OK客户端。

随后,随机分析了几个relay日志,发现第一个事务都未被执行。而且操作的表都是有不同的,操作也是有不同,有insert、update等等。顿时感觉,事情大条了。如果每个日志的第一个事务都未执行,那么从库要缺少多少条数据?不敢想象,现在业务还在上升期,不久业务量会是现在的几倍,甚至更多,到那时就不是用户投诉那么简单了。

分析:

又抓取了部分relay日志情况,第一个事务也都未被执行。我可以肯定了,是所有relay日志的第一个事务都未被执行。

第四步之前,master还未收到slave的ack信息,此时由于事务已经提交,除了session1,其他会话是可以看到 val=1。主库服务器down或者主库实例crash,此时发生HA切换。主库未接收到slave的ack信息,slave接收到日志并落盘,应用binlog更新。t1.val=1,此时业务切换到slave上能获取到一致的数据。如果在slave还未接收到binlog并且主库挂了,因为主库已经提交,此时主库t1.val是1而从库t1.val是5,主备不一致。

这个问题也可以是由于分割relay日志的脚本造成的。一般强制刷新日志是用flush命令来操作的,flush命令一般不会造成数据丢失,当然像他们这样频繁的操作,我是不知道会不会造成Bug,导致丢数据。还有在flush relay logs的时候有没有用到其他的什么操作呢?

after_sync

我决定抓一把数据库中操作过的命令。

比如主库操作update t1 set val=1 where id=10将val从5修改为1。

抓取命令,问题解决

会话session1在主库提交 :update t1 set val=1 where id=10;commit;主库将事务写入binlog。将binlog同步给slave,不提交。等待slave返回ack信息,等待的实际时间以rpl_semi_sync_master_timeout为准,如果超时master改为异步模式。接收到来自slave的ack信息,主库进行提交并且返回成功给OK客户端。

想到就做,登上从库主机、登入数据库,开启general_log日志。

分析:

坐等5分钟,打开日志,寻找每分钟前几秒的记录。

如果在第3步等待slave ack的过程中,主库发生crash(此时t1.val=5),HA 切换到slave,应用查询slave 。如果slave接收到binlog并发送ack给master,则t1.val=1。如果slave响应主库,但是主库crash ,此时因为主库还没提交t1.val=1, slave t1.val=5,但是主库启动恢复之后t1.val会变成5,主备还是一致的。如果slave未接收到事务和响应主库,此时t1.val=5,无论哪种状态,对于所有客户端数据库都是一致,事务都没有丢失。

哇!

知识点:两阶段提交

哇!

第一阶段是先prepare、再同步写redo log,第二阶段同步写binlog、再commit,如果在写入commit标志时崩溃,则恢复时,会重新对commit标志进行写入。

哇!

HA切换

你们猜我看到了什么? 我从未见过如此骚的操作!

(6)主从

上图,以表我的震惊。

binlog_format

为什么要跳过事务?你用stop slave与Start slave来刷新relay日志,已经刷新了我的三观,那,有必要跳过事务?这就解释得通了,为何relay日志第一个事务全都丢失。

ROW(最安全)MIXED(不推荐)STATEMENT(不推荐)

至此!问题已经清晰,是由于开发设置的分割relay日志的脚本,使用了非常规命令及sql_slave_skip_counter跳过事务命令来分隔relay-log日志,导致事务大量丢失。

sync_binlog

所以,创新是好事,但要打好基本功,别搞些骚操作。

=0:由os系统的刷新机制来控制,刷新数据到磁盘的频率=1:每次commit刷新到磁盘1:每N次提交刷新到磁盘

文章来自微信公众号:DBAplus社群

innodb_support_xa

版本要打开,保证binlog提交的顺序,否则乱序的binlog在恢复或者slave应用的时候会有问题,及以后废弃,始终支持两阶段提交。

crash safe

crash-safe就是将relay-info.log的信息保存在InnoDB的事务表中,这时执行relay log中的事务和写relay info在一个事务中,就能得到原子性保证。从而避免已执行的binlog位点和写入relay log info的位点信息不一致的情况发生。

IO thread

master-info-repository=TABLEsync_master_info=N:每N个event刷新一次表

SQL thread

relay-log-info-repository=TABLEsync_relay_info=N:每N个event刷新一次表

relay-log-recovery

当slave从库宕机后,假如relay-log损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,并且重新从master上获取日志,这样就保证了relay-log的完整性。

relay_log_info_repository = TABLErelay_log_recovery = 1

-log-recovery-when-sql-threads-position-is-unavailable/

semi_sync

after commit:master把每一个事务写到二进制日志并保存到磁盘上,并且提交(commit)事务,再把事务发送给从库,开始等待slave的应答。响应后master返回结果给客户端,客户端才可继续。after sync:master把每一个事务写到二进制日志并保存磁盘上,并且把事务发送给从库,开始等待slave的应答。确认slave响应后,再提交(commit)事务到存储引擎,并返回结果给客户端,客户端才可继续。

GTID

相比位点复制,能减少不一致的概率

参考资料

MySQL数据丢失讨论?p=395细看InnoDB数据落盘?p=503MySQL5.7 深度解析:Loss-Less半同步复制技术MySQL 5.7 Replication相关新功能说明

原文来自微信公众号:DBAplus社群

搜索