记一次生产环境数据误删——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记录模式,做好保留备份,并且对代码程序就行日常评审,避免误删除数据。