MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
224874 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

31 | 误删数据后除了跑路,还能怎么办?

跨机房或跨城市保存备份
HA系统的工作原理
制定操作规范
账号分离
搭建延迟复制的备库
使用mysqlbinlog解析和应用日志
使用全量备份和增量日志恢复数据
处理多个事务的误删数据
使用Flashback工具恢复数据
回忆误删数据事件的经历和恢复方法
各个环节共同保障可靠性
备份的有效性检查
rm删除数据
预防误删库/表的方法
误删库/表
误删行
课后话题
数据和服务的可靠性
误删数据后的处理方法

该思维导图由 AI 生成,仅供参考

今天我要和你讨论的是一个沉重的话题:误删数据。
在前面几篇文章中,我们介绍了 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。
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

本文总结了处理MySQL误删数据的方法。针对误删数据行,建议使用Flashback工具修改binlog内容来恢复数据;对于误删表或数据库,建议通过恢复备份或临时库进行数据恢复。此外,文章还提出了预防误删数据的建议,如设置sql_safe_updates参数和进行SQL审计。另外,还介绍了延迟复制备库的搭建以及预防误删库/表的方法。总的来说,文章通过具体案例和技术原理,为读者提供了处理误删数据的实用方法和预防措施。文章强调了预防的重要性,并提到了定期检查备份的有效性。同时,也提到了数据和服务的可靠性不仅是运维团队的工作,需要各个环节共同保障。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 实战 45 讲》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(58)

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

    作者回复: 👍 这个是血泪经验 拷贝文本执行,这个操作还可能存在字符集隐患。 这个事情更深一层逻辑,是你做了创造性的事情,非常优秀👍。 而这个运维同学认为他只是一个”复制粘贴执行的人”, 这种思路下是迟早会出问题的。

    2019-01-23
    10
    174
  • 無名小卒
    置顶
    对生产数据库操作,公司DBA提出的编写脚本方法,个人觉得还是值得分享,虽说可能大部分公司也可能有这样的规范。 修改生产的数据,或者添加索引优化,都要先写好四个脚本:备份脚本、执行脚本、验证脚本和回滚脚本。备份脚本是对需要变更的数据备份到一张表中,固定需要操作的数据行,以便误操作或业务要求进行回滚;执行脚本就是对数据变更的脚本,为防Update错数据,一般连备份表进行Update操作;验证脚本是验证数据变更或影响行数是否达到预期要求效果;回滚脚本就是将数据回滚到修改前的状态。 虽说分四步骤写脚本可能会比较繁琐,但是这能够很大程度避免数据误操作。

    作者回复: 👍 非常好的经验 如果能够切实执行,即使有出问题,也是可以很快恢复的 把这些脚本当做开发代码来维护,是一个很好的实践

    2019-01-23
    4
    140
  • Sancho
    说说我的故事:一次更新,少了一个条件,结果把全表更新了,用的是pg,当时dba说没发恢复。这是属于一个业务核心表,数据有6000多条。当时业务系统有本地缓存,业务系统的更新会发通知刷新,数据库操作的更新要去业务系统主动刷新。在dba操作完sql,说了影响行数之后,我立刻傻了。赶紧上报老大,技术群里大吼不要刷缓存。我们老大是个老司机,知道dba是指望不上了,立刻在另一个业务系统写了几行代码,然后发布上线。浏览器一个地址下去,内存里的数据全部返回到浏览了。。。

    作者回复: 这个老大是高手😆

    2019-07-05
    4
    87
  • 某、人
    总结下今天的知识点: 我觉得DBA的最核心的工作就是保证数据的完整性 今天老师也讲到了先要做好预防,预防的话大概是通过这几个点: 1.权限控制与分配(数据库和服务器权限) 2.制作操作规范 3.定期给开发进行培训 4.搭建延迟备库 5.做好sql审计,只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核 6.做好备份。备份的话又分为两个点. (1)如果数据量比较大,用物理备份xtrabackup。定期对数据库进行全量备份,也可以做增量备份。 (2)如果数据量较少,用mysqldump或者mysqldumper。再利用binlog来恢复或者搭建主从的方式来恢复数据。 定期备份binlog文件也是很有必要的 还需要定期检查备份文件是否可用,如果真的发生了误操作,需要恢复数据的时候,发生备份文件不可用,那就更悲剧了 如果发生了数据删除的操作,又可以从以下几个点来恢复: 1.DML误操作语句造成数据不完整或者丢失。可以通过flashback,不过我们目前用的是美团的myflash,也是一个不错的工具,本质都差不多.都是先解析binlog event,然后在进行反转。把delete反转为insert,insert反转为delete,update前后image对调。所以必须设置binlog_format=row 和 binlog_row_image=full. 切记恢复数据的时候,应该先恢复到临时的实例,然后在恢复回主库上。 2.DDL语句误操作(truncate和drop),由于DDL语句不管binlog_format是row还是statement.在binlog里都只记录语句,不记录image所以恢复起来相对要麻烦得多。只能通过全量备份+应用binlog的方式来恢复数据。一旦数据量比较大,那么恢复时间就特别长, 对业务是个考验。所以就涉及到老师在第二讲提到的问题了,全量备份的周期怎么去选择

    作者回复: 👍

    2019-01-23
    2
    59
  • 还一棵树
    我遇到过一个线上误truncate表的,最终选择的处理过程如下: 1、创建一个同版本的空mysql实例,建一个名字+结构一模一样的表 2、discard这个表的tablespace 3、从之前的备份集中 innobackupex --apply-log 并记录binlog位置(用innobackupex备份的)。还原后找到误操作表的.ibd文件,copy到新实例对应的位置 4、在之前创建的mysql实例上import tablespace 5、利用mysqlbinlog 处理增量数据 6、最后导出 再导入

    作者回复: 👍 这基本上是最快的恢复步骤了

    2019-01-24
    4
    57
  • Knight²º¹⁸
    很久之前,升级mongodb,在备份数据文件时,备份了指向数据文件的软连接(当时没注意是软连接),导致在删除数据文件后,再通过备份数据文件恢复数据时找不到文件,这时才发现自己备份的只是一个软连接,最后是通过备份节点才恢复的数据。当时还没自动化运维工具,线上操作也不规范。后来通过 chatrr +i 命令给所有重要的文件增加了 i 权限属性,这样哪怕 root 用户都无法直接删除文件。差点就跑路了?😂😂😂

    作者回复: 后来通过 chatrr +i 命令给所有重要的文件增加了 i 权限属性,这样哪怕 root 用户都无法直接删除文件。 mark

    2019-01-23
    3
    35
  • __困
    不知道老师还在吗,看到这里,恢复出临时库后,怎么应用到主库

    作者回复: 就要看情况了 如果原库是删表,就把临时库里面的表导过去,小表逻辑导,大表可以用“透明表空间机制”物理导; 如果原库是误删了一些行,那只能在临时库里面select数据出来,按照业务的需要去补了

    2019-05-12
    2
    25
  • Long
    又到了讲故事(事故)的时候了,历史上遇到过很多次事故。全表误删除,误更新不下于8次,有MySQL 的DB也有memory DB. 有一次同事比较搞笑的是,有一次一张重要的权限控制表更新,由于用的是workbench 界面工具当时写了where条件,但是在选中执行行的时候where条件在第二行,没选中,还在执行前的时候手动把session 级的sql_safe_updates=0了,也没有点开那个autocommit取消的按钮。然后一执行,全表更新了,导致全网只有一个用户可以正常登录。还有其他的误操作,总结历史遇到过的这类问题基本就是几类 1. 登错环境,以为是测试环境,一顿操作猛如虎,一看环境是生产,回头一看,表已经drop了…… 2. sql写的有问题,逻辑错误,或者条件缺失,常见的如不带where;or关键字的逻辑没有用括号括好 3. 还有一些奇葩的,比如where 字段1=字段2写成了字段1+字段2,逻辑等于判断变成了是否为1的判断了,大概率全表更新了。 错误解决大部分都是用备份恢复或者根据错误的逻辑来逻辑恢复。 还有一个,最近在尝试的,就是ibd文件中有坏页,只要一读到那个坏页,就会crash,报错spaceid page no should be多少多少,尝试了copy frm, ibd,ibdata, iblogfile这些表结构,数据文件,数据字典,undo redo 日志,也尝试用了undrop的工具也解析不出来。这个表比较特殊,是一个特殊库,没备份,表没有索引没法通过走索引跳过那个坏页的那些行,现在的状态是,只能用nysqldump恢复一部分数据。 我想通过16进制,自己慢慢找到那个脏写的数据,然后修改一下文件…… 老师有什么比较好的建议吗?或者后面会说到ibd文件的物理结构之类的吗? 感谢

    作者回复: 感谢你的分享,都是血泪教训。。 我看有几个是用的可视化工具导致的,后面还是尽量用MySQL客户端敲命令吧😆 ibd文件坏页我之前有回答过其他同学的,看下这个 https://weibo.com/1933424965/H3qIu0JYo?from=page_1005051933424965_profile&wvr=6&mod=weibotime

    2019-01-28
    24
  • 乔纳森
    感谢老师的讲解,我也分享一个吧; delete 删除2000w 左右的数据场景, 开发直接自己登mysql服务操作的 1. 导出主键id。到一个文件中 2. 一个循环取id, delete. for id in $( cat id.file) do mysql -hhost -pport -uuser -ppswd -e "delete from t where id=$id"; done 问题出在id.file,是用什么工具导出来的,里面一个id 列头部;内容大概如下 id 3 400 然后就删了全表了,因为这个删全表的时间非常长,在删完后,从库重放时出现了延迟; 还好我们有A--B--C ,主从架构,在C实例上用mydumper 导入和恢复(导入时,会关闭binlog,所以在A和B 上都导入了数据); 假如当时: 1. 有长事务监控的话,就可以及时发现,并杀掉delete 操作,避免悲剧发生 2. 假如知道用ibd 来恢复的话,就可以直接用文件拷贝,被sql 回放快多了 3. 当时还是太年轻了

    作者回复: 成长了:)

    2019-05-25
    4
    9
  • 运维老胡
    个人经历:一年前刚接手线上MySQL维护工作不久,某天线上一台MySQL意外重启导致复制中断,其中新来的DBA就告诉我需要重做从库,大周末的特意跑去公司重做从库,登上从库后没有进行环境的确认,以为只是主备复制,其实是双主复制,结果在从库上直接执行了drop database ,幸亏只删了2个小库,然后想起来有没有可能是双主。后来通过回复全库备份和应用binlog才恢复好被删的2个库的数据。 从中得到经验: 1、操作前必须确认环境。 2、从库断电重启导致复制异常,可以先跳过错误,后续业务低峰期再通过主从复制校验来修复。 3、一定要定期做好备份。

    作者回复: 血泪经验呀 其实重做从库不应该有“drop database”这种命令 直接在新的目录下重新创建一个新的 旧的从库要删除的时候,先mv到回收站,确认没问题后再rm 文件

    2020-03-22
    4
收起评论
显示
设置
留言
58
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部