MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

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

林晓斌 2019-01-23
今天我要和你讨论的是一个沉重的话题:误删数据。
在前面几篇文章中,我们介绍了 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/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(33)

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

    作者回复: 👍 这个是血泪经验

    拷贝文本执行,这个操作还可能存在字符集隐患。

    这个事情更深一层逻辑,是你做了创造性的事情,非常优秀👍。
    而这个运维同学认为他只是一个”复制粘贴执行的人”, 这种思路下是迟早会出问题的。

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

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

    2019-01-23
    21
  • 某、人
    总结下今天的知识点:
    我觉得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
    1
    19
  • 公号-技术人成长
    我只想说,作者功力过于深厚了!
    2019-01-25
    11
  • 还一棵树
    我遇到过一个线上误truncate表的,最终选择的处理过程如下:
    1、创建一个同版本的空mysql实例,建一个名字+结构一模一样的表
    2、discard这个表的tablespace
    3、从之前的备份集中 innobackupex --apply-log 并记录binlog位置(用innobackupex备份的)。还原后找到误操作表的.ibd文件,copy到新实例对应的位置
    4、在之前创建的mysql实例上import tablespace
    5、利用mysqlbinlog 处理增量数据
    6、最后导出 再导入

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

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

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

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

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

    mark

    2019-01-23
    3
  • 700
    老师,请教。假如我有数据库的物理备份和逻辑备份(mydumper),因为 mydumper 导出的数据是按表名分开存放的,那么表误删数据的时候优先考虑逻辑备份(误删数据表的备份集)+binlog 恢复比物理备份恢复会快点?基于此,我总感觉物理备份只是在要恢复整个实例时才会优先考虑,而恢复整个实例的场景又是比较少的,毕竟一般大家的线上架构至少都是主从模式。所以逻辑备份被物理备份更实用。这种想法算是说得通吗?

    作者回复: 其实是要看表的大小

    如果是一个大表,逻辑恢复还是比较慢的,毕竟用物理备份来恢复出实例,相对会快些。

    当然如果你已经有了一个成熟系统用逻辑恢复来实现,也不用改它,主要关注一下是否满足SLA就可以了^_^
    facebook就是主要用逻辑备份的

    2019-01-23
    2
  • CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `city` varchar(16) NOT NULL,
    `name` varchar(16) NOT NULL,
    `age` int(11) NOT NULL,
    `addr` varchar(128) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `city` (`city`)
    ) ENGINE=InnoDB;

    老师请教您16章的问题,您提到“city、name、age 这三个字段的定义总长度是36”,这个是怎么算出来的呢,varchar(16)是可以保存16个字符,占用了49个字节(utf8),所以我没想明白36是怎么来的。

    第二个问题是max_length_for_sort_data参数系统默认是1024,是1024个字节的意思吗?

    2019-01-23

     作者回复

    1. age(11)其实是4个字节哈
    2. 对,单位是字节

    谢谢老师,不过还是没明白,age是4个字节,city和name分别是49个字节,49+49+4=102字节,36是怎么来的呢?再次感谢

    作者回复: 哦 抱歉哈,我这边验证的时候默认用的latin1,是16+16+4

    2019-01-23
    1
    2
  • Cranliu
    个人觉得,预防同样很重要,一般的dml操作,我是先ctas要操作的数据,drop/truncate 的时候先逻辑备份。

    作者回复: 对的,备份的意识很重要。


    不过“drop/truncate 的时候先逻辑备份”这么做的不多^_^
    主要的原因是逻辑备份可能会对系统有额外消耗。(全表扫描)

    2019-01-23
    2
  • null
    老师,您好!

    文章提到:“在用备份恢复出临时实例之后,将这个临时实例设置成线上备库的从库”。

    将临时实例设置成线上备库的前提,是备库还未应用主库删除库/表的 binlog 吧?
    如果备库同步了主库的 binlog,也把库/表删除了,这时候该怎么做?(在临时库设置 gtid_next 跳过该删除操作么?如果不支持 gtid 又该咋办?)

    谢谢老师!
    2019-07-25
    1
  • 简海青
    感谢老师的讲解,我也分享一个吧;
    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
    1
  • __困
    不知道老师还在吗,看到这里,恢复出临时库后,怎么应用到主库

    作者回复: 就要看情况了

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

    2019-05-12
    1
  • 张仕华
    图3中 select * from t where id>1 for update 少了 "for update"?
    2019-03-28
    1
  • catalina
    老师,我们现在需要将一个库下面的所有表的数据同步到另外一个库,每个表有几百万数据吧,大约十多张表。有什么好的方法吗?

    作者回复: 原库的这几个表还会继续更新吗? 如果会继续更新,就用搭主备的方法;
    如果没更新了,后面有一个文章专门讲这个问题哈

    2019-01-24
    1
  • AI杜嘉嘉
    我有个问题,那么如果说需要修改数据。在没有自动化平台这种情况下,为了避免数据丢失,我觉得有两种方式可行。如果说是删除整个表,可以使用rename 操作。第二种就是进行备份。我想问老师,那种方法更合理一些?rename是DDL语句,会不会锁表?

    作者回复: 你的需求是要删表吗?
    如果是要删表,就rename,过了观察时间再drop

    2019-01-23
    1
  • 511
    早~
    2019-01-23
    1
  • 虎小牙
    就像看弹幕一样,评论区永远很精彩!!!
    2019-11-08
  • godtrue
    自己好像没有误删数据的经验,之前公司小,也不直接操作数据库。目前公司对于数据库的操作比较严格,业务开发中物理删除是禁用的,修改数据也是先逻辑修改再插入。如果是数据结转,更需要层层审核最后有DBA操刀。
    预防为主,防治结合,我们做到了,基本将问题扼杀在摇篮里。
    2019-08-04
收起评论
33
返回
顶部