数据库误删库、表怎么办?

一、Oracle

<一>、误删数据(delete/truncate)

• 使用闪回表语句

flashback table table_name to timestamp to_date(‘YYYY-MM-DD HH24:MI:SS’, ‘2018-01-01 12:00:00’);

闪回表就是对表的数据做回退，回退到之前的某个时间点，其利用的是undo的历史数据，与undo_retention设置有关，默认是14400分钟（1天）

<二>、误删表(drop)

• 可以通过Oracle的回收站找回

drop table test;

select * from recyclebin t where T.original_name = 'TEST' and type = 'TABLE';

• 找回语句

flashback TABLE TEST TO BEFORE DROP;

注意:
1)、若有多次drop记录,默认会恢复最新的一次drop记录
2)、drop时使用purge(彻底删除)或清空回收站是无法再恢复的

<三>、误删库

• 使用闪回库语句

flashback database to timestamp to_date(‘YYYY-MM-DD HH24:MI:SS’, ‘2018-01-01 12:00:00’);

二、Mysql

<一>、binlog的三种格式

首先可以了解下binlog的三种格式

• Statement:每一条会修改数据的SQL都会记录在binlog中
• Row:不记录Sql语句上下文信息,仅保存哪条记录被修改
• Mixed:Statement和Row的混合体

1、Statement

• 只记录执行的Sql,不需要记录每一行数据的变化,因此极大的减少了binlog的日志量,提升了系统的性能,比如说update user set name = ‘张三’ where id = 1,Statement格式下就记录的是这条语句
• 但是如果语句中包含一些函数,在备库执行后,可能就出现两边1的情况,比如说自增id、uuid等
• 或者使用delete语句带limit也会出现主备不一致的情况
• 或者使用当前时间now()的情况

2、Row

• 需要记录每一条记录被修改成什么样子了,会非常清楚的记录每一行数据被修改的细节
• 会产生日志量太大的问题

3、Mixed

• 系统会自动判断该用Statement还是Row,一般的语句修改使用Statement格式保存binlog;对于一些Statement无法准确完成主从复制的操作,则采用Row格式保存binlog

<二>、误删行(delete)

• 用FlashBack工具(mysqlbinlog)通过闪回把数据恢复过来
• 原理是修改binlog的内容,拿回原库重放,使用这个方案的前提是确保binlog_format = row和binlog_row_image = FULL

预防误删数据的方法

• 把sql_safe_updates参数设置为on,这样一来,如果忘记在delete或者update语句中写where条件,或者where条件里面没有包含索引字段的话,这条语句的执行就会报错
• 代码上线前,经过专人审计

<三>、误删表(truncate/drop)

• 使用delete命令删除的数据,可以用FlashBack来恢复,而如果误删表(truncate/drop)命令删除,就无法通过flashback恢复
• 即使配置了binlog_format = row,使用truncate/drop,记录的binlog还是statement格式,binlog只有一个truncate/drop语句是恢复不出来的
• 这种情况下,要想恢复数据,就需要使用全量备份,加增量日志的方式了。这个方案要求线上有定期的全量备份,并且实时备份binlog

预防误删库/表方法
账号分离:

• 只分配给开发DML权限,而不给truncate/drop 权限,如果开发人员有DDL需求,可以把语句交给DBA操作
• DBA日常也只用只读账号操作,必要时候才使用有DDL权限账号

制定操作规范:

• 在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以 后再删除这张表
• 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作
  必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表