苍茫 置顶2019-01-23有一次，我维护一张表，需要手动修改大量数据的状态，sql就很多，然后我保存到txt文件中以附件的形式发给部门老大审批，部门老大审批后转发邮件给运维，然后运维这哥们用的是360浏览器，他预览的sql，然后全部复制到客户端执行，但是问题也在这，360浏览器预览的时候由于文本偏长，到了某一条语句只有前半部分的update语句，没有后面的条件，然后就悲剧了。全表的状态都变成同一个。然后我就特别莫名其妙，还被老大批了一顿。说我写的脚本有问题。这锅我可不背，我把脚本在本地备份库跑了一遍又一遍就是没有问题。然后我再去运维哥们那，叫他再复制一下脚本就发现问题了。好在执行脚本前进行了表备份。扩展一下，如果你用谷歌浏览器就不会出现这种问题！发现问题后，立马恢复了数据作者回复: 👍 这个是血泪经验
拷贝文本执行，这个操作还可能存在字符集隐患。
这个事情更深一层逻辑，是你做了创造性的事情，非常优秀👍。
而这个运维同学认为他只是一个”复制粘贴执行的人”， 这种思路下是迟早会出问题的。 1 32- linhui0705 置顶2019-01-23对生产数据库操作，公司DBA提出的编写脚本方法，个人觉得还是值得分享，虽说可能大部分公司也可能有这样的规范。
修改生产的数据，或者添加索引优化，都要先写好四个脚本：备份脚本、执行脚本、验证脚本和回滚脚本。备份脚本是对需要变更的数据备份到一张表中，固定需要操作的数据行，以便误操作或业务要求进行回滚；执行脚本就是对数据变更的脚本，为防Update错数据，一般连备份表进行Update操作；验证脚本是验证数据变更或影响行数是否达到预期要求效果；回滚脚本就是将数据回滚到修改前的状态。
虽说分四步骤写脚本可能会比较繁琐，但是这能够很大程度避免数据误操作。作者回复: 👍 非常好的经验
如果能够切实执行，即使有出问题，也是可以很快恢复的
把这些脚本当做开发代码来维护，是一个很好的实践 24 
2019-01-23总结下今天的知识点:
我觉得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的方式来恢复数据。一旦数据量比较大,那么恢复时间就特别长,
对业务是个考验。所以就涉及到老师在第二讲提到的问题了，全量备份的周期怎么去选择展开作者回复: 👍
1 21
2019-01-25我只想说，作者功力过于深厚了！ 12- 2019-01-24我遇到过一个线上误truncate表的，最终选择的处理过程如下：
1、创建一个同版本的空mysql实例，建一个名字+结构一模一样的表
2、discard这个表的tablespace
3、从之前的备份集中 innobackupex --apply-log 并记录binlog位置（用innobackupex备份的）。还原后找到误操作表的.ibd文件，copy到新实例对应的位置
4、在之前创建的mysql实例上import tablespace
5、利用mysqlbinlog 处理增量数据
6、最后导出 再导入展开作者回复: 👍
这基本上是最快的恢复步骤了 1 10 
2019-07-05说说我的故事：一次更新，少了一个条件，结果把全表更新了，用的是pg，当时dba说没发恢复。这是属于一个业务核心表，数据有6000多条。当时业务系统有本地缓存，业务系统的更新会发通知刷新，数据库操作的更新要去业务系统主动刷新。在dba操作完sql，说了影响行数之后，我立刻傻了。赶紧上报老大，技术群里大吼不要刷缓存。我们老大是个老司机，知道dba是指望不上了，立刻在另一个业务系统写了几行代码，然后发布上线。浏览器一个地址下去，内存里的数据全部返回到浏览了。。。作者回复: 这个老大是高手😆
9
2019-01-28又到了讲故事(事故)的时候了，历史上遇到过很多次事故。全表误删除，误更新不下于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 6
2019-01-23很久之前，升级mongodb，在备份数据文件时，备份了指向数据文件的软连接(当时没注意是软连接)，导致在删除数据文件后，再通过备份数据文件恢复数据时找不到文件，这时才发现自己备份的只是一个软连接，最后是通过备份节点才恢复的数据。当时还没自动化运维工具，线上操作也不规范。后来通过 chatrr +i 命令给所有重要的文件增加了 i 权限属性，这样哪怕 root 用户都无法直接删除文件。差点就跑路了？😂😂😂作者回复: 后来通过 chatrr +i 命令给所有重要的文件增加了 i 权限属性，这样哪怕 root 用户都无法直接删除文件。
mark 1 4- 2019-07-25老师，您好！
文章提到：“在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库”。
将临时实例设置成线上备库的前提，是备库还未应用主库删除库/表的 binlog 吧？
如果备库同步了主库的 binlog，也把库/表删除了，这时候该怎么做？（在临时库设置 gtid_next 跳过该删除操作么？如果不支持 gtid 又该咋办？）
谢谢老师！展开 1 2 
2019-05-12不知道老师还在吗，看到这里，恢复出临时库后，怎么应用到主库作者回复: 就要看情况了
如果原库是删表，就把临时库里面的表导过去，小表逻辑导，大表可以用“透明表空间机制”物理导；
如果原库是误删了一些行，那只能在临时库里面select数据出来，按照业务的需要去补了 2
2019-03-28图3中 select * from t where id>1 for update 少了 "for update"? 2
2019-01-23老师，请教。假如我有数据库的物理备份和逻辑备份（mydumper），因为 mydumper 导出的数据是按表名分开存放的，那么表误删数据的时候优先考虑逻辑备份（误删数据表的备份集）+binlog 恢复比物理备份恢复会快点？基于此，我总感觉物理备份只是在要恢复整个实例时才会优先考虑，而恢复整个实例的场景又是比较少的，毕竟一般大家的线上架构至少都是主从模式。所以逻辑备份被物理备份更实用。这种想法算是说得通吗？作者回复: 其实是要看表的大小
如果是一个大表，逻辑恢复还是比较慢的，毕竟用物理备份来恢复出实例，相对会快些。
当然如果你已经有了一个成熟系统用逻辑恢复来实现，也不用改它，主要关注一下是否满足SLA就可以了^_^
facebook就是主要用逻辑备份的 2
2019-01-23CREATE 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
1 2
2019-01-23个人觉得，预防同样很重要，一般的dml操作，我是先ctas要操作的数据，drop/truncate 的时候先逻辑备份。作者回复: 对的，备份的意识很重要。
不过“drop/truncate 的时候先逻辑备份”这么做的不多^_^
主要的原因是逻辑备份可能会对系统有额外消耗。（全表扫描） 2
2019-05-25感谢老师的讲解，我也分享一个吧；
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. 当时还是太年轻了展开作者回复: 成长了：）
1- 2019-01-24老师，我们现在需要将一个库下面的所有表的数据同步到另外一个库，每个表有几百万数据吧，大约十多张表。有什么好的方法吗？
作者回复: 原库的这几个表还会继续更新吗？ 如果会继续更新，就用搭主备的方法；
如果没更新了，后面有一个文章专门讲这个问题哈 1 
2019-01-23我有个问题，那么如果说需要修改数据。在没有自动化平台这种情况下，为了避免数据丢失，我觉得有两种方式可行。如果说是删除整个表，可以使用rename 操作。第二种就是进行备份。我想问老师，那种方法更合理一些？rename是DDL语句，会不会锁表？作者回复: 你的需求是要删表吗？
如果是要删表，就rename，过了观察时间再drop 1
2019-01-23早~ 1
2020-02-06rm的如果是主库， 如果有部分binlog还没送出去， 即使HA选出新的master，那是否有可能丢失部分数据呢？
2019-12-19在一家公司的一个生产库上所有表的数据少了2周的数据，后面dba通过binlog找回了，但是还有些数据不一致，也没有说啥原因，怀疑是不是dba误操作了