7

mysql误删数据怎么恢复(1)delete误删恢复 -利来国际app

陈家睿 2021-11-23
6448

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

(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 [email protected]_completion_type*/; /*!50530 set @@session.pseudo_slave_mode=0*/; [[email protected] binlog]# [[email protected] binlog]# [[email protected] binlog]#

可以得到binlog的position位置

[[email protected] 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
我们得到了误操作sql的准确位置在1709-1740之间,再根据位置进一步过滤,使用flashback模式生成回滚sql,检查回滚sql是否正确(注:真实环境下,此步经常会进一步筛选出需要的sql。结合grep、编辑器等)

执行
python binlog2sql.py -h127.0.0.1 -p3306 -ucjr -p'cjr' -dtest -ttest --start-file='mysql-bin.000013' --start-position=1709 --stop-position=1740 -b > rollback.sql
输出insert语句即为误删的数据


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





最后修改时间:2021-12-02 16:20:00
「喜欢文章,快来给作者赞赏墨值吧」
1人已赞赏
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论