MySQL误操作的语法与回滚

前段时间我们有个开发同学写了一条更新语句没有加上where条件更新了全库，后来通过了阿里的DTS进行了恢复。但是这个也勾起了我的好奇，如果是自建的系统我们该如何通过binlog恢复数据呢，我们又该怎么预防这些毁灭性的语句呢

几种情况

• delete误删数据行
• drop table 或者 truncate table
• drop database

预防

不带where条件的预防

在update | delete 时候经常有小机灵不带where条件的情况，这种场景下mysql设置了安全模式。

//查看是否打开安全模式
SHOW VARIABLES LIKE '%sql_safe_updates%'

全局设置安全模式

对新打开会话生效,重启数据库失效

set global sql_safe_updates  = 1 | 0  ; 

当前会话设置

set sql_safe_updates  = 1 | 0  ;  

重启后也生效的方案

Server System Variables
MySQL的系统变量貌似不能在配置文件中进行修改，但是可以在重启的时候启动一个init脚本变相实现

1. 在mysql.cnf下添加

#init
init-file='/etc/mysql/conf.d/init.sql'

2. init.sql 写入

set global sql_safe_updates  = 1;

3. 重启数库
   
   

误删行的恢复

MyFlash,美团的一个开源产品，使用它是因为中文文档够详细它级基本原理也有说明介绍。而且它自持表级的回滚和按语句的回滚。

安装

1. 下载源码

   git clone git@github.com:Meituan-Dianping/MyFlash.git

2. 进入MyFlash/binary测试

   ./flashback -h

3. 如果能正常参看到帮助信息，设置软链接

   ln /usr/local/src/MyFlash/binary/flashback  -s /usr/bin/

配置 binlog

log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index

或者

log-bin = bin-log
binlog_format =  row      #行的日志格式

重启

service mysqld start

查看是否配置成功

show variables like '%log_bin%'

测试MyFlash

1. 进入binlog目录
2. flashback --binlogFileNames=./bin-log.000001
3. 执行闪回文件

   mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql --socket=/var/lib/mysql/mysql.sock test

   如果出现

   mysqlbinlog: unknown variable 'default-character-set=utf8'

那么屏蔽mysql配置里的

#default-character-set = utf8

4. 回滚

   mysqlbinlog --skip-gtids  binlog_output_base.flashback | mysql --socket=/var/lib/mysql/mysql.sock test

误删表/库

误删表库的操作binlog日志可以恢复DML，但是DDL就GG了。

恢复

恢复表/库需要做的是做定期的数据库备份

1. 取最近的一次全量备份
2. 用备份恢复出一个临时库
3. 从binlog日志里取出0点以后的数据
4. 把这些日志进行回放

ps.为了加快回滚速度可以知道相应表/库的数据进行恢复

其他手段

延迟备份

这个我一直认为是我和运维同学意淫出来的功能，后面查了下才知道MySQL5.6后已经支持了这个配置 CHANGE MASTER TO MASTER_DELAY。但是这个配置在自建集群中可以灵活配置，在一些云产品中就可能存在设置不了的情况了。

规范的数据库流程/权限/操作规范

1. 做到权限最小化，只给业务开发DML权限，DDL可以走工单
2. 删除数据表之前先对表进行改名操作，然后观察一段时间确保吴影响
3. 改表名加固定后缀，并且管理员只能删除固定后缀的表