MySQL误删数据怎么恢复（1）delete误删恢复
source link: https://blog.51cto.com/u_13874232/5088774
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
误删数据包含如下四种情况
（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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK