MySQL误删数据怎么恢复（1）delete误删恢复

精选原创

进击的CJR 2022-03-09 22:14:44 ©著作权

文章标签 mysql 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者进击的CJR的原创作品，请联系作者获取转载授权，否则将追究法律责任

误删数据包含如下四种情况

（1）使用 delete 语句误删数据行；

（2）使用 drop table 或者 truncate table 语句误删数据表；

（3）使用 drop database 语句误删数据库；

（4）使用 rm 命令误删整个 MySQL 实例；

使用delete语句误删数据

恢复方式：使用binlog，利用Flashback工具恢复，Flashback的原理是修改binlog拿到原库里回放，这个方案的前提是binlog_format=row 并且binlog_row_image=full

单个事务的处理：
1.insert 对应的 write_row event 改成delete_row event
2.delete 对应的 delete_row event 改成write_row event
3.update binlog中记录了修改前和修改后的值，对掉位置就可以了

多个事务的处理注意调整事务的顺序，

预防：
1.将sql_safe_updates设置为on，这样delete和update中无where子句的语句就会报错，生产如果要执行整表删除可以用truncate或者where 1=1。
2.上线，必须做sql审计，至少也要在测试环境完成验证。

将测试库中的某个表删除

解析binlog查看，先看下统计中，这个删除在不在这个binlog中，确认确实是在这个binlog日志中

根据binlog统计ddl dml  
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000007 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr

mysqlbinlog -v mysql-bin.000013 > /tmp/sql

解析出binlog

将这部分的delete_row event 改成write_row event

 mysqlbinlog  --base64-output=DECODE-ROWS -vv   mysql-bin.000013 | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@17.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-1][0-7]=//g'

生产insert语句就是delete掉的

binlog2sql的使用

安装

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

shell> pip install -r requirements.txt

-B, --flashback 生成回滚SQL解析范围控制
--start-file 起始解析文件，只需文件名，无需全路径 。必须。
--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime 起始解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。
--stop-datetime 终止解析时间，格式'%Y-%m-%d %H:%M:%S'。可选。默认不过滤。

对象过滤
-d, --databases 只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。
-t, --tables 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。
--only-dml 只解析dml，忽略ddl。可选。默认False。
--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

误删库test下的表test

根据删除时间，确认binlog位置

mysqlbinlog --start-datetime='2021-11-23 19:00:00' --stop-datetime='2021-11-23 19:15:00' -v mysql-bin.000013
BEGIN
/*!*/;
# at 1192
#211123 19:11:00 server id 572533306  end_log_pos 1254 CRC32 0xae413b6e         Table_map: `test`.`test` mapped to number 134
# at 1254
#211123 19:11:00 server id 572533306  end_log_pos 1709 CRC32 0x44c60949         Delete_rows: table id 134 flags: STMT_END_F

BINLOG '
RMycYRM6KiAiPgAAAOYEAAAAAIYAAAAAAAEABHRlc3QABHRlc3QABgMPDw8PDwoeAB4AHgAeAB4A
Pm47Qa4=
RMycYSA6KiAixwEAAK0GAAAAAIYAAAAAAAEAAgAG/8ABAAAAAmExAmEyAmEzAmE0AmE1wAIAAAAC
YjECYjICYjMCYjQCYjXAAwAAAAJjMQJjMgJjMwJjNAJjNcAEAAAAAmQxAmQyAmQzAmQ0AmQ1wAUA
AAACZTECZTICZTMCZTQCZTXABgAAAAJmMQJmMgJmMwJmNAJmNcAHAAAAATEBMQExATEBMcAIAAAA
ATIBMQExATEBMcAJAAAAATIBMgExATEBMcAKAAAAATMBMgExATEBMcALAAAAATEBMgExATEBMcAM
AAAAATMBMgExATEBMcANAAAAATMBMgExATEBMMAOAAAAATQBMgExATEBMMAPAAAAATUBMQEwATEB
MMAQAAAAATEBMQEwATEBMMARAAAAATYBMQEwATEBMMASAAAAATYBMQEwATEBMMATAAAAATgBMQEw
ATEBMMAUAAAAATkBMQEwATEBMMAVAAAAATgBMQEwATEBMMAWAAAAATcBMQEwATEBMMAXAAAAATEB
MQEwATEBMMAYAAAAATgBMQEwATEBMMAZAAAAATkBMQEwATEBMMAaAAAAATUBMQEwATEBMEkJxkQ=
'/*!*/;
### DELETE FROM `test`.`test`
### WHERE
###   @1=1
###   @2='a1'
###   @3='a2'
###   @4='a3'
###   @5='a4'
###   @6='a5'
### DELETE FROM `test`.`test`
.........
# at 1709
#211123 19:11:00 server id 572533306  end_log_pos 1740 CRC32 0x623ae51c         Xid = 182
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@VM-0-9-centos binlog]# 
[root@VM-0-9-centos binlog]# 
[root@VM-0-9-centos binlog]#

可以得到binlog的position位置

[root@VM-0-9-centos binlog]# mysqlbinlog --start-position=1709 --stop-position=1740  -v mysql-bin.000013

也可以根据这个解析出sql来确认出具体pos位置

python binlog2sql.py -h127.0.0.1 -P3306 -ucjr -p'cjr' -dtest -ttest --start-datetime='2021-11-23 19:00:00' --stop-datetime='2021-11-23 19:15:00

MyFlash的使用

下载安装
git clone https://github.com/Meituan-Dianping/MyFlash.git gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback

使用
--databaseNames databaseName to apply. if multiple, seperate by comma(,) --tableNames tableName to apply. if multiple, seperate by comma(,) --start-position start position --stop-position stop position --start-datetime start time (format %Y-%m-%d %H:%M:%S) --stop-datetime stop time (format %Y-%m-%d %H:%M:%S) --sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,) --maxSplitSize max file size after split, the uint is M --binlogFileNames binlog files to process. if multiple, seperate by comma(,)
--outBinlogFileNameBase output binlog file name base --logLevel log level, available option is debug,warning,error --include-gtids gtids to process --exclude-gtids gtids to skip

生成闪回binlog文件
binary/flashback --binlogFileNames=/root/mysql-bin.002712 --include-gtids='486bfa3b-d9f2-11ea-8b02-a01c8d40b01a:438640718' --databaseNames=workflow_1 --tableNames=sys_config --sqlTypes='UPDATE' --outBinlogFileNameBase=test_tb

执行闪回
mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql -uroot -proot

如果执行显示kill 查看操作系统日志，大部分是OOM原因
tail -500f /var/log/messages