误删数据的分类:
如果是使用 delete 语句误删了数据行,可以用 Flashback 工具通过闪回把数据恢复回来。
Flashback 恢复数据的原理,是修改 binlog 的内容,拿回原库重放。而能够使用这个方案的前提是,需要确保 binlog_format=row
和 binlog_row_image=FULL
。
如果一个事务里面有多个操作: 比如:
(A)delete ...
(B)insert ...
(C)update ...
用 Flashback 工具解析 binlog 后,写回主库的命令是:
(reverse C)update ...
(reverse B)delete ...
(reverse A)insert ...
也就是说,如果误删数据涉及到了多个事务的话,需要将事务的顺序调过来再执行。
但是不建议在主库上操作,方法如下: 恢复出一个备份,或者找一个从库作为临时库,在这个临时库上执行这些操作,然后再将确认过的临时库的数据,恢复回主库。
这是因为,一个在执行线上逻辑的主库,数据状态的变更往往是有关联的。可能由于发现数据问题的时间晚了一点儿,就导致已经在之前误操作的基础上,业务代码逻辑又继续修改了其他数据。所以,如果这时候单独恢复这几行数据,而又未经确认的话,就可能会出现对数据的二次破坏。
事前预防建议:
但是delete全表是很慢的,需要生成回滚日志、写 redo、写 binlog。不如使用truncate / drop table 。
使用 delete 命令删除的数据,可以用 Flashback 来恢复。而使用 truncate /drop table 和 drop database 命令删除的数据,就没办法通过 Flashback 来恢复了。 因为我们配置了 binlog_format=row,执行这三个命令时,记录的 binlog 是 statement 格式的。binlog 里面就只有一个 truncate/drop 语句,这些信息是恢复不出数据的。
这种情况下,要想恢复数据,就需要使用全量备份,加增量日志的方式了。这个方案要求线上有定期的全量备份,并且实时备份 binlog。
在这两个条件都具备的情况下,假如有人中午 12 点误删了一个库,恢复数据的流程如下:
注意点:
–stop-position
参数执行到误操作之前的日志,然后再用–start-position
从误操作之后的日志继续执行;set gtid_next=gtid1;begin;commit
; 先把这个 GTID 加到临时实例的 GTID 集合,之后按顺序执行 binlog 的时候,就会自动跳过误操作的语句。即使这样,mysqlbinlog 方法恢复数据还是不够快,主要原因有两个:
加速办法:
start slave
之前,先通过执行change replication filter replicate_do_table = (tbl_name)
命令,就可以让临时库只同步误操作的表。如果一个库的备份特别大,或者误操作的时间距离上一个全量备份的时间较长,比如一周一备的实例,在备份之后的第 6 天发生误操作,那就需要恢复 6 天的日志,这个恢复时间可能是要按天来计算的。
延迟复制的备库是一种特殊的备库,通过 CHANGE MASTER TO MASTER_DELAY = N
命令,可以指定这个备库持续保持跟主库有 N 秒的延迟。
比如你把 N 设置为 3600,这就代表了如果主库上有数据被误删了,并且在 1 小时内发现了这个误操作命令,这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行 stop slave,再通过之前介绍的方法,跳过误操作命令,就可以恢复出需要的数据。
其实,对于一个有高可用机制的 MySQL 集群来说,最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除,而只是删掉了其中某一个节点的数据的话,HA 系统就会开始工作,选出一个新的主库,从而保证整个集群的正常工作。
查看权限可以使用 show grants
命令查看账户的权限。
线上误truncate表的,参考其他方案,处理过程如下:
1、创建一个同版本的空mysql实例,建一个名字+结构一模一样的表 2、discard这个表的tablespace 3、从之前的备份集中 innobackupex --apply-log 并记录binlog位置(用innobackupex备份的)。还原后找到误操作表的.ibd文件,copy到新实例对应的位置 4、在之前创建的mysql实例上import tablespace 5、利用mysqlbinlog 处理增量数据 6、最后导出 再导入