故事开始

 快要下班了，心情是这样的。

 

 突然电话响了，一看电话号码是项目甲方负责人的号码

 

 

 菊花一紧，难道出什么问题了，硬着头皮接通了电话。

 

 

@####@，你们程序怎么回事 ，某个业务的数据界面一条都查询不到了。。。。。

 

我们马上排查一下，看看那什么情况（心理活动：尼玛，尼玛，我怎么知道啊）？？？

 

莫慌，先搞条SQL到数据库查一把     阿弥陀佛，祈祷只是查询条件的问题。

 

 

哦豁、哦豁、数据库里也没了，用户做了一年的任务数据都没了，我尼玛！！！！

 

甲方要求今天必须恢复数据，什么问题导致的你们后面自己排查去。

 

好好好，是是是

 

（恢复数据不都是小菜一碟的事毕竟我们数据库每天凌晨有定时全量备份，也开了binlog日志的，哈哈，这难不倒我）

恢复数据没问题啊

binlog里面把删除语句找出来，逆向改成插入语句不就恢复了，这事不就欧了。

 

说干就干，先让公司现场运维同事帮忙把binlog拷贝出来

 

开始搞binlog，通过命令： mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2024-12-30 16:49:35' --stop-datetime='2024-12-30 17:00:00'  mysql-bin.000103 >outsql1.sql;找出对应时间点的语句删除。

 

 

 

找到删除语句还是很顺利，但是

binlog 中的删除语句只有被删除数据的ID，没有删除数据的详细数据，tmd 这怎么搞（后面再说原因，先恢复数据要紧）

 

没办法只能拷贝全量备份文件了

 

问题又来了，公司运维说科技运维说拷贝不出来，文件太大了要拷贝1个小时（备份源文件 7G）     打工人今天注定是无法按时下班了   跟现场一顿沟通 tmd 发现竟然没有压缩，压缩了一下，瞬间缩水了很多，终于拷贝出来了   你以为拷贝出来就没问题了嘛？   最开始想简单了，想的就是把全量备份还原到本地，然后在本地把对应业务丢失数据的表搞成脚本恢复到生产上就完事了   MD，但是一顿操作发现，7G的数据全量还原到本地也tmd太太太慢了。还原了1个小时才还原不到10%，这不是到明天早上都不一定能还原完   7G的文件，就别想用普通的编辑软件打开了，肯定是不行的。   没办法，只能2条腿走路，先让它慢慢还原着，然后再去找找看有没有其他办法可以从7G的这个文件里把需要的数据筛出来   一顿Google 、度娘加ChatGPT后发现通过 mysqldump -u root -p mydatabase > mydatabase_backup.sql 备份的文件，不管一个表有多少数据，备份文件中都只会生成了一条插入语句   再通过这个命令： cat testdb_all.sql |grep -E ‘INSERT INTO `personchangeinfo`’ > personchangeinfo1.sql 就可以从全量备份文件中找出并生成一个新文件   通过实测是可从 7G的文件里面找出具体的某张表的插入语句的，这样就好办了，不用还原整改备份，可以单表进行还原，节省了一大把时间。（tmd 今晚应该不用熬通宵了）   到了这一步至少可以先恢复到丢失数据当天凌晨的数据了，凌晨之后的的数据好像无能为力了（不知道各位大神有没有什么好办法？），硬着头皮给现场项目方打电话汇报结果。   一看时间已经晚上11点了，MD天选的打工人。  

说明：

数据丢失的原因：程序缺陷导致，一个平常不用的功能，刚好年底了用到了

binlog里面为啥只有删除数据的ID没有详情：因为Binlog 格式设置的是“Mixed”

总结：

1、设Binlog模式要慎重，还得的按照自己项目实际情况去设置 binlog 类型，不然可能会对恢复数据造成麻烦。

binlog 有三种格式：

• • Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
  • Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
  • Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。 

Statement

Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。（比如update user set name="张三" where id >1 and id <10000,假设被修改的数据有2000条，那么Row的日志量就是2000条，而Statement只是这条sql语句一条日志而已，所以Statement的日志量相对Row会少很多）

但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。

所以使用 Statement 格式会出现一些数据一致性问题。

Row

从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。

Row 格式的日志内容会非常清楚地记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

Mixed

从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。

在 Mixed 模式下，系统会自动判断 该 用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。

Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。

 

根据这次经历发现 Mixed 某些语句下是不会记录数据详情的，我们这次的删除语句就是Delete FROM Table where ID IN （ID1,ID2）,IN里面有很多ID，大量ID时 Mixed 模式下binlog是不会记录每行数据的详情地，真是血的教训换来的经验 。  

2、全量备份是必要的，这次就靠全量备份活过来了，binlog的增量备份也是很必要的可以尽量减少数据丢失，但是要注意binlog的模式设置。 

3、cat testdb_all.sql |grep -E ‘INSERT INTO `personchangeinfo`’ > personchangeinfo1.sql ； 这个命令很实用，可以从大文件里面查找匹配内容并输出到新文件 

4、用mysqldump 备份的文件，压缩比很可观的，别忘了压缩。