第三十讲：误删数据后除了跑路，还能怎么办？

第三十讲：误删数据后除了跑路，还能怎么办？

简概

还是平淡的开头

​        今天我要和你讨论的是一个沉重的话题：误删数据。
​        在前面几篇文章中，我们先容了 MySQL 的高可用架构。
​        当然，传统的高可用架构是不能预防误删数据的，因为主库的一个 drop table 命令，会通过 binlog 传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。固然我们之前遇到的大多数的数据被删，都是运维同学或者 DBA 背锅的。
​        但实际上，只要有数据操作权限的同学，都有大概踩到误删数据这条线。今天我们就来聊聊误删数据前后，我们可以做些什么，减少误删数据的风险，和由误删数据带来的丧失。
​        为了找到解决误删数据的更高效的方法，我们必要先对和 MySQL 相干的误删数据，做下分类：

• 利用 delete 语句误删数据行；
  
• 利用 drop table 或者 truncate table 语句误删数据表；
  
• 利用 drop database 语句误删数据库；
  
• 利用 rm 命令误删整个 MySQL 实例。
  

误删行

​        在第 24 篇文章中，我们提到如果是利用 delete 语句误删了数据行，可以用 Flashback 工具通过闪回把数据规复回来。
​        Flashback 规复数据的原理，是修改 binlog 的内容，拿回原库重放。而可以或许利用这个方案的前提是，必要确保 binlog_format=row 和 binlog_row_image=FULL。
​        具体规复数据时，对单个事务做如下处理：
对于 insert 语句，对应的 binlog event 类型是 Write_rows event，把它改成 Delete_rows event 即可；同理，对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event；而如果是 Update_rows 的话，binlog 内里记录了数据行修改前和修改后的值，对调这两行的位置即可。
​        如果误操作不是一个，而是多个，会怎么样呢？比如下面三个事务：

1. (A)delete ...
2. (B)insert ...
3. (C)update ...

复制代码

​        如今要把数据库规复回这三个事务操作之前的状态，用 Flashback 工具剖析 binlog 后，写回主库的命令是：

1. (reverse C)update ...
2. (reverse B)delete ...
3. (reverse A)insert ...

复制代码

​        也就是说，如果误删数据涉及到了多个事务的话，必要将事务的顺序调过来再执行。
​        必要说明的是，我不发起你直接在主库上执行这些操作。规复数据比较安全的做法，是规复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，规复回主库。
​        为什么要这么做呢？这是因为，一个在执行线上逻辑的主库，数据状态的变动往往是有关联的。大概由于发现数据题目的时间晚了一点儿，就导致已经在之前误操作的基础上，业务代码逻辑又继续修改了其他数据。所以，如果这时候单独规复这几行数据，而又未经确认的话，就大概会出现对数据的二次粉碎。
​        当然，我们不止要说误删数据的事后处理办法，更紧张是要做到事前预防。我有以下两个发起：

• 把 sql_safe_updates 参数设置为 on。如许一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件内里没有包含索引字段的话，这条语句的执行就会报错。
  
• 代码上线前，必须经过 SQL 审计。
  

​        你大概会说，设置了 sql_safe_updates=on，如果我真的要把一个小表的数据全部删掉，应该怎么办呢？
​        如果你确定这个删除操作没题目的话，可以在 delete 语句中加上 where 条件，比如 where id>=0。但是，delete 全表是很慢的，必要天生回滚日志、写 redo、写 binlog。所以，从性能角度思量，你应该优先思量利用 truncate table 或者 drop table 命令。
利用 delete 命令删除的数据，你还可以用 Flashback 来规复。而利用 truncate /drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来规复了。​        为什么呢？
​        这是因为，纵然我们设置了 binlog_format=row，执行这三个命令时，记录的 binlog 还是 statement 格式。binlog 内里就只有一个 truncate/drop 语句，这些信息是规复不出数据的。
​        那么，如果我们真的是利用这几条命令误删数据了，又该怎么办呢？
误删库 / 表

​        这种情况下，要想规复数据，就必要利用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，而且实时备份 binlog。
​        在这两个条件都具备的情况下，假如有人中午 12 点误删了一个库，规复数据的流程如下：

• 取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
  
• 用备份规复出一个临时库；
  
• 从日志备份内里，取出凌晨 0 点之后的日志；
  
• 把这些日志，除了误删除数据的语句外，全部应用到临时库。
  

​        这个流程的示意图如下所示：
   

   
          图 1 数据规复流程 -mysqlbinlog 方法          关于这个过程，我必要和你说明如下几点：

• 为了加快数据规复，如果这个临时库上有多个数据库，你可以在利用 mysqlbinlog 命令时，加上一个–database 参数，用来指定误删表地点的库。如许，就避免了在规复数据时还要应用其他库日志的情况。
  
• 在应用日志的时候，必要跳过 12 点误操作的那个语句的 binlog：
  
  
  
  • 如果原实例没有利用 GTID 模式，只能在应用到包含 12 点的 binlog 文件的时候，先用–stop-position 参数执行到误操作之前的日志，然后再用–start-position 从误操作之后的日志继续执行；
    
  • 如果实例利用了 GTID 模式，就方便多了。假设误操作命令的 GTID 是 gtid1，那么只必要执行 set gtid_next=gtid1;begin;commit; 先把这个 GTID 加到临时实例的 GTID 集合，之后按顺序执行 binlog 的时候，就会自动跳过误操作的语句。
    
  
  

​        不外，纵然如许，利用 mysqlbinlog 方法规复数据还是不够快，重要原因有两个：

• 如果是误删表，最好就是只规复出这张表，也就是只重放这张表的操作，但是 mysqlbinlog 工具并不能指定只剖析一个表的日志；
  
• 用 mysqlbinlog 剖析出日志应用，应用日志的过程就只能是单线程。我们在第 26 篇文章中先容的那些并行复制的方法，在这里都用不上。
  

​        一种加快的方法是，在用备份规复出临时实例之后，将这个临时实例设置成线上备库的从库，如许：

• 在 start slave 之前，先通过执行change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；
  
• 如许做也可以用上并行复制技能，来加快整个数据规复过程。
  

​        这个过程的示意图如下所示。
   

   
          图 2 数据规复流程 -master-slave 方法          ​        可以看到，图中 binlog 备份系统到线上备库有一条虚线，是指如果由于时间太久，备库上已经删除了临时实例必要的 binlog 的话，我们可以从 binlog 备份系统中找到必要的 binlog，再放回备库中。
​        假设，我们发现当前临时实例必要的 binlog 是从 master.000005 开始的，但是在备库上执行 show binlogs 显示的最小的 binlog 文件是 master.000007，意味着少了两个 binlog 文件。这时，我们就必要去 binlog 备份系统中找到这两个文件。
​        把之前删掉的 binlog 放回备库的操作步骤，是如许的：

• 从备份系统下载 master.000005 和 master.000006 这两个文件，放到备库的日志目次下；
  
• 打开日志目次下的 master.index 文件，在文件开头加入两行，内容分别是 “./master.000005”和“./master.000006”;重启备库，目的是要让备库重新识别这两个日志文件；如今这个备库上就有了临时库必要的所有 binlog 了，创建主备关系，就可以正常同步了。
  

​        岂论是把 mysqlbinlog 工具剖析出的 binlog 文件应用到临时库，还是把临时库接到备库上，这两个方案的共同点是：
    误删库或者表后，规复数据的思绪重要就是通过备份，再加上应用 binlog 的方式。 ​        也就是说，这两个方案都要求备份系统定期备份全量日志，而且必要确保 binlog 在被从本地删除之前已经做了备份。但是，一个系统不大概备份无穷的日志，你还必要根据成本和磁盘空间资源，设定一个日志保存的天数。
​        如果你的 DBA 团队告诉你，可以保证把某个实例规复到半个月内的恣意时间点，这就表示备份系统保存的日志时间就至少是半个月。
​        另外，我发起你岂论利用上述哪种方式，都要把这个数据规复功能做成自动化工具，而且经常拿出来演练。为什么这么说呢？这里的原因，重要包括两个方面：

• 固然“发生这种事，大家都不想的”，但是万一出现了误删变乱，可以或许快速规复数据，将丧失降到最小，也应该不消跑路了。
  
• 而如果临时再手忙脚乱地手动操作，末了又误操作了，对业务造成了二次伤害，那就说不外去了。
  

耽误复制备库

​        固然我们可以通过利用并行复制来加快规复数据的过程，但是这个方案仍然存在“规复时间不可控”的题目。如果一个库的备份特殊大，或者误操作的时间距离上一个全量备份的时间较长，比如一周一备的实例，在备份之后的第 6 天发生误操作，那就必要规复 6 天的日志，这个规复时间大概是要按天来计算的。
​        那么，我们有什么方法可以缩短规复数据必要的时间呢？如果有非常核心的业务，不允许太长的规复时间，我们可以思量搭建耽误复制的备库。
​        这个功能是 MySQL 5.6 版本引入的。一样平常的主备复制布局存在的题目是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。
​        耽误复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N 秒的耽误。比如你把 N 设置为 3600，这就代表了如果主库上有数据被误删了，而且在 1 小时内发现了这个误操作命令，这个命令就还没有在这个耽误复制的备库执行。这时候到这个备库上执行 stop slave，再通过之前先容的方法，跳过误操作命令，就可以规复出必要的数据。
​        如许的话，你就随时可以得到一个，只必要最多再追 1 小时，就可以规复出数据的临时实例，也就缩短了整个数据规复必要的时间。
预防误删库 / 表的方法

​        固然常在河边走，很难不湿鞋，但终究还是可以找到一些方法来避免的。所以这里，我也会给你一些减少误删操作风险的发起。
第一条发起是，账号分离。

​        如许做的目的是，避免写错命令。比如：我们只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。纵然是 DBA 团队成员，日常也都规定只利用只读账号，必要的时候才利用有更新权限的账号。
第二条发起是，制定操作规范。

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

​        着实，对于一个有高可用机制的 MySQL 集群来说，最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除，而只是删掉了此中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。
​        这时，你要做的就是在这个节点上把数据规复回来，再接入整个集群。当然了，如今不止是 DBA 有自动化系统，SA（系统管理员）也有自动化系统，所以也许一个批量下线呆板的操作，会让你整个 MySQL 集群的所有节点都三军覆没。应对这种情况，我的发起只能是说尽量把你的备份跨机房，或者最好是跨都会保存。
小结

​        今天，我和你讨论了误删数据的几种大概，以及误删后的处理方法。
但，我要强调的是，预防远比处理的意义来得大。​        另外，在 MySQL 的集群方案中，会时不时地用到备份来规复实例，因此定期检查备份的有效性也很有必要。
​        如果你是业务开发同学，你可以用show grants命令查看账户的权限，如果权限过大，可以发起 DBA 同学给你分配权限低一些的账号；
​        你也可以评估业务的紧张性，和 DBA 商量备份的周期、是否有必要创建耽误复制的备库等等。数据和服务的可靠性不止是运维团队的工作，最终是各个环节一起保障的结果。
问答

​        今天的课后话题是，回忆下你亲身经历过的误删数据变乱吧，你用了什么方法来规复数据呢？你在这个过程中得到的履历又是什么呢？
答案

第一个变乱

​        有一次，我维护一张表，必要手动修改大量数据的状态，sql就很多，然后我保存到txt文件中以附件的形式发给部门老大审批，部门老大审批后转发邮件给运维，然后运维这哥们用的是360浏览器，他预览的sql，然后全部复制到客户端执行，但是题目也在这，360浏览器预览的时候由于文本偏长，到了某一条语句只有前半部分的update语句，没有背面的条件，然后就悲剧了。全表的状态都酿成同一个。然后我就特殊莫名其妙，还被老大批了一顿。说我写的脚本有题目。这锅我可不背，我把脚本在本地备份库跑了一遍又一遍就是没有题目。然后我再去运维哥们那，叫他再复制一下脚本就发现题目了。幸亏执行脚本前进行了表备份。扩展一下，如果你用谷歌浏览器就不会出现这种题目！发现题目后，立马规复了数据
老师点评

@苍茫 同学提到了一个例子，我觉得值得跟大家分享一下。运维的同学直接拷贝文本去执行，SQL 语句截断，导致数据库执行出错。从浏览器拷贝文本执行，是一个非常不规范的操作。除了这个例子内里说的 SQL 语句截断题目，还大概存在乱码题目。一样平常这种操作，如果脚本的开发和执行不是同一个人，必要开发同学把脚本放到 git 上，然后把 git 地点，以及文件的 md5 发给运维同学。如许就要求运维同学在执行命令之前，确认要执行的文件的 md5，跟之前开发同学提供的 md5 相同才气继续执行。另外，我要特殊点赞一下 @苍茫 同学复现题目的思绪和追查题目的态度。
第二个变乱


​        很久之前，升级mongodb，在备份数据文件时，备份了指向数据文件的软连接(其时没注意是软连接)，导致在删除数据文件后，再通过备份数据文件规复数据时找不到文件，这时才发现本身备份的只是一个软连接，末了是通过备份节点才规复的数据。其时还没自动化运维工具，线上操作也不规范。厥后通过 chatrr +i  命令给所有紧张的文件增加了 i 权限属性，如许哪怕 root 用户都无法直接删除文件。差点就跑路了？
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。