记一次生产环境数据误删——mysql数据恢复

记一次生产环境数据误删——mysql数据恢复

概要

mysql数据误删除，数据恢复篇。

技术名词解释

• binlog
• Binlog_row_image

技术细节

1.了解一下binlog 的三种模式

binlog日志有三种模式
1)ROW(row-basedreplication,RBR):
日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

优点:
能清楚记录每一个行数据的修改继节，能完全实现主从数据同步和数据的恢复。而且不会出现某些特定情况下存储过程或function无法被正确复制的问题。

缺点:
批量操作，会产生大量的日志，尤其是alter table会让日志量暴涨。

2)STATEMENT (statement-based replication,SBR):
记录每一条修改数据的SQL语句(批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件)，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

优点:
日志量小，减少磁盘IO，提升存储和恢复速度。

缺点:
在某些情况下会导致主从数据不一致，比如last_insert_id0、now0等函数。

3)MIXED (mixed-based replication,MBR):
以上两种模式的合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式

企业场景如何选择binlog的模式
1.如果生产中使用MySQL的特殊功能相对少(存储过程、触发器、函数)。选择默认的语句模式Statement.

2如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。

3.如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row 模式，但是要注意，该模 式的binlog日志量增长非常快

原文链接

2.mysql的Binlog_row_image参数学习

binlog_row_image为full时，表无论有没有主键约束或者唯一约束binlog都会记录所有前后镜像；
binlog_row_image为minimal时，如果表有主键或唯一索引，前镜像只保留主键列，后镜像只保留修改列；如果表没有主键或唯一索引，前镜像全保留，后镜像只保留修改列；
binlog_row_image为noblob时，如果表有主键或唯一索引，修改列为text/blob列，前镜像忽略text/blob列，后镜像包含被修改的text/blob列；如果表有主键或唯一索引，修改列不是text/blob列，前后镜像忽略text/blob列。如果表没有主键或唯一索引，修改列为text/blob列 ，前后镜像全保留；如果表没有主键或唯一索引，修改列不是text/blob列，前镜像全保留，后镜像忽略text/blob列。

3.查询数据库binlog 记录模式

必须为FULL 和 ROW 才会记录完整数据 才可恢复
查询binlog记录模式

show variables like ‘%binlog_row_image%’;

select @@binlog_format;

4.解析binlog文件

如果不知道binlog文件存放路径，可参考:查看mysql binlog存储路径
例如：例如需要将binlog文件 mysql_bin_30002.004082 解析为具体的sql文件binglog22.sql

可参考如下命令：

mysqlbinlog --base64-output=decode-rows -v mysql_bin_30002.004082 > binglog22.sql;

5.分析解析后的文件

可使用cat命令

cat binglog22.sql  |grep  1931258658328176640 -C 150;

说明：cat 文件名 |grep 关键字 -C xx行
搜索关键字，并且展示前后150行

6.数据恢复

第5步已经拿到具体的delete语句了，其中将每个字段的value也都有记录，现在我们只需将delete语句转为insert语句即可
如：

--解析binlog的日志数据为
### DELETE FROM `xxxxx_fi`.`t_cas_paymentbill_tc`
### WHERE
###   @1=1960940266006240256
###   @2=1960829659743519744
###   @3=310747310931860480
###   @4=1960829659743519744
###   @5=1960848994092429312
###   @6=1012443468640817152
###   @7=1960848994092429312

-- 转为insert语句则为
INSERT INTO `xxxxx_fi`.`t_cas_paymentbill_tc`
VALUES(
  1960940266006240256,
  1960829659743519744,
  310747310931860480,
  1960829659743519744,
  1960848994092429312,
  1012443468640817152,
  1960848994092429312
  );

小结

注意生产环境的binlog记录模式，做好保留备份，并且对代码程序就行日常评审，避免误删除数据。