案例 - 误删千万的表

某天下午，正在给测试库录入数据，看到有截断的warnings，就把表delete掉，重新导入，在做这个导入操作的同时，还在线上给业务做一个update数据的操作，突然之间，误把测试库的secureCRT当做测试的窗口，进行了一个delete操作，2秒后还没删除成功，再看一下表名字，原来是线上业务的表，当场懵逼了

马上ctrl + c 取消delete语句，但因为用的是myshard中间件，索引 ctrl + v 没用有，delete的语句，再就传递到下面挂在的mysql里执行，于是2000万的表就这样被删除了，全球一共9个节点，也因为同步dml sql，而导致所有节点的该表被删除

不过myshard的有一个保护机制，就是每行数据都会加入一个__deleted字段，myshard的delete数据，相当于mysql层面的 update deleted = 1 而已，3天之后，才会真正把数据删掉，目的是防止人为误操作

于是我选择业务使用最多的其中一个节点，进入mysql层面，SET sql_log_bin = 0 ，然后进行

update 表 set deleted=0 where deleted=1

恢复一个节点需要5分钟，把第一个节点恢复以后，马上通知业务，把全球使用改表的业务，都切到已经恢复的那个节点，其他节点先不要使用，于是逐步恢复其他节点，最后一共消耗20分钟把数据恢复回来

当然这5分钟期间，虽然所有节点的这个表都是没有数据，但不一定查询不到数据，因为有redis在扛着，redis的命中率有95%，只有在redis上查询不到数据，接着往DB上查询，才会出现取不到数据的情况

当时以为这样就完事了，于是去开会，开会回来后，业务说数据库的同步有问题，数据不一致了......

与内核开发的同事调查，发现进行误操作的那个节点的数据，不能顺利同步到其他节点，原因2千万的表，delete语句一下去，就生成了18G的binlog，而myshard的sync同步进程，识别不了超过4G以上的binlog文件，把位置点变成一个负数，而负数位置点的监控是我们监控脚本没有覆盖到的

-rw-rw----. 1 mysql mysql 101M May 31 16:33 mysql-bin.270756 -rw-rw----. 1 mysql mysql  18G May 31 16:38 mysql-bin.270757 -rw-rw----. 1 mysql mysql 101M May 31 16:38 mysql-bin.270758

做法是跳过那个18G的binlog，直接去连下一个binlog，先恢复同步，同步落后了20G，等同步追完以后，大概造成了4个小时的数据延迟，充值延迟导致

这个mysql-bin.270757的二进制日志，用了5分钟生成，其实当时还可以做一个补救措施，就是进入myshard下面的MySQL层面，直接把唯一一条delete语句kill掉，那就可以减少binlog的大小，以及数据的生成时间，不过当时太急没有反应过来

18G的binlog，99%是那条delete SQL生成的row模式的数据文件，但期间还有其他业务正常的SQL，于是使用了大众点评的binlog2sql恢复工作，目的是把二进制文件，解析成具体的SQL语句

使用mysqlbinlog去解析也可以，只不过看不到具体的SQL语句，但业务是需要看到具体的SQL，才能进行恢复，因为比如一些update a = a + 100 类似充值的语句，就不能直接更新了，它是依赖过去的值，如果过去的值都不准确（数据延迟），那么更新回去会出问题的，这样要让业务去衡量怎么处理，所以得把具体的SQL语句提取出来

https://github.com/danfengcao/binlog2sql

其实阿里也有一个binlog解析工具，只不过阿里最后输出的结果是反向解析，比如delete的语句，阿里的工具会生成insert，我的场景只需要提取正常的sql即可，不需要反向解析，于是没有采用

但不管是大众点评，还是阿里的工具，都需要在线去连接数据库的，目的是为了获取information_schema.columns表，去获取表的字段名字，再替换到binlog解析后的@1，@2，@3字段，但我并不想在online的DB上去解析这个18G的binlog，担心消耗cpu资源，我把这个18G的binlog拷贝一个测试库上

想了很久才想到一个方法，就是把测试库，伪装成online的数据库，把测试库的schema都删掉，然后把当前的binlog名字，替换成18G的binlog名字，觉得脑洞有点大，但工具能用了，当时写的脚本关键部分是这样子的

able_array=( 表1 表2 表3 ....... 数据库上所有的表名 ） for table_name in "${table_array[@]}";do     python binlog2sql.py -h127.0.0.1 -P端口 -u账号 -p密码  -d数据库 --start-file='18G的binlog文件' -t${table_name} > /data1/flashback/"${table_name}".sql done

于是就把18G的sql都解析出来了，比如像这样的格式，有SQL语句，有时间，每条SQL语句占用1行，于是就通知业务，再继续做恢复的事情

INSERT INTO `myshard`.`xxxxxxxxx`(`status`, `actor_uid`, `style`, `ctime`, `__deleted`, `__version`, `sid`, `date`, `utime`, `type`, `uid`) VALUES (0, xxxxxxxxx, 0, 1496219633, 0, xxxxxxxxx, xxxxxxxxx, 20170531, 1496219633, 1, 1605227422); #start 106192 end 106420 time 2017-05-31 16:33:53 INSERT INTO `myshard`.`xxxxxxxxx`(`status`, `actor_uid`, `style`, `ctime`, `__deleted`, `__version`, `sid`, `date`, `utime`, `type`, `uid`) VALUES (0, xxxxxxxxx, 0, 1496219633, 0, xxxxxxxxx, xxxxxxxxx, 20170531, 1496219633, 3, 1573543992); #start 258936 end 259164 time 2017-05-31 16:33:53 INSERT INTO `myshard`.`xxxxxxxxx`(`status`, `actor_uid`, `style`, `ctime`, `__deleted`, `__version`, `sid`, `date`, `utime`, `type`, `uid`) VALUES (0, xxxxxxxxx, 0, 1496219634, 0, xxxxxxxxx, xxxxxxxxx, 20170531, 1496219634, 3, 1509357246); #start 473268 end 473496 time 2017-05-31 16:33:54 INSERT INTO `myshard`.`xxxxxxxxx`(`status`, `actor_uid`, `style`, `ctime`, `__deleted`, `__version`, `sid`, `date`, `utime`, `type`, `uid`) VALUES (0, xxxxxxxxx, 0, 1496219636, 0, xxxxxxxxx, xxxxxxxxx, 20170531, 1496219636, 1, 1610811581); #start 1114346 end 1114574 time 2017-05-31 16:33:56 INSERT INTO `myshard`.`xxxxxxxxx`(`status`, `actor_uid`, `style`, `ctime`, `__deleted`, `__version`, `sid`, `date`, `utime`, `type`, `uid`) VALUES (0, xxxxxxxxx, 0, 1496219636, 0, xxxxxxxxx, xxxxxxxxx, 20170531, 1496219636, 1, 1593006833); #start 1295075 end 1295303 time 2017-05-31 16:33:56

本次最大的教训是，在执行 delete ,rm ,rsync ,mv ,update的操作之前，要确认好ip,实例，心里要默念个5秒再执行操作，小心谨慎