Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

31 | 误删数据后除了跑路,还能怎么办? #42

Open
git-zjx opened this issue Oct 10, 2019 · 0 comments

Comments

@git-zjx
Copy link
Owner

@git-zjx git-zjx commented Oct 10, 2019

误删行
如果是使用 delete 语句误删了数据行,可以用 Flashback 工具通过闪回把数据恢复回来

Flashback 恢复数据的原理,是修改 binlog 的内容,拿回原库重放。而能够使用这个方案的前提是,需要确保 binlog_format=rowbinlog_row_image=FULL

具体恢复数据时,对单个事务做如下处理:

  1. 对于 insert 语句,对应的 binlog event 类型是 Write_rows event,把它改成 Delete_rows event 即可;
  2. 同理,对于 delete 语句,也是将 Delete_rows event 改为 Write_rows event;
  3. 而如果是 Update_rows 的话,binlog 里面记录了数据行修改前和修改后的值,对调这两行的位置即可

对于多个事务,例如:

(A)delete ...
(B)insert ...
(C)update ...

现在要把数据库恢复回这三个事务操作之前的状态,用 Flashback 工具解析 binlog 后,写回主库的命令是:

(reverse C)update ...
(reverse B)delete ...
(reverse A)insert ...

也就是说,如果误删数据涉及到了多个事务的话,需要将事务的顺序调过来再执行

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
1 participant
You can’t perform that action at this time.