原文标题:破防了,谁懂啊家人们:记一次mysql问题排查
原文作者:阿里云开发者
冷月清谈:
怜星夜思:
2、在进行线上数据库操作时,如何保证操作的安全性,避免误操作或数据丢失?
3、文章最后提到了针对数据导入源头进行限制,避免类似事件再次发生,那么具体可以采取哪些措施进行源头控制?
原文内容
阿里妹导读
一、前言
键盘撒一把冻干,我家猫也能来上班。——粥师傅
(你看我锤不锤你就完事了)
1.1 表结构
id
|
group
|
code
|
name
|
property
|
1
|
业务1
|
事件1
|
吃冻干
|
|
2
|
业务1
|
事件2
|
喂猫粮
|
|
3
|
业务2
|
事件1
|
睡觉
|
|
4
|
业务3
|
事件10086
|
下班
|
|
...
|
...
|
...
|
...
|
主键primary key:id
二、问题排查和修复过程
2.1 最初的问题
顺带一提,以下SQL执行等全程都使用弹内DMS平台进行操作。
2.2 初步思路
DELETE FROM MY_TABLE
WHERE group = 'bad_group';
直接执行上面这个SQL进行普通数据变更可行吗?显示不行,有经验的同学都知道,在千万量级下,清理大量数据会超过binlog限制,导致SQL无法被执行。
2.3 另辟蹊径
DROP
|
TRUNCATE
|
DELETE
|
|
删除内容
|
删除整张表数据,表结构以及表的索引、约束和触发器
|
删除全部数据
|
删除部分数据(可带where条件)
|
语句类型
|
DDL
|
DDL
|
DML
|
效率
|
最高
|
较高
|
较低
|
回滚
|
无法回滚
|
无法回滚
|
可以回滚
|
自增值
|
-
|
重置
|
不重置
|
举个不那么恰当的例子,好比房东把房子租给别人,到期后发现房子里全都是垃圾,DELETE语句是将这些垃圾一件一件清理出来,只保留原来干净的家具。TRUNCATE相当于一把火把房子里所有东西都烧了,DROP语句就是房子直接不要了。
-- 将正常数据复制到临时表 CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';
– 删除原表
DROP TABLE MY_TABLE;
– 将临时表重命名为原表
RENAME TABLE TEMP_TABLE TO MY_TABLE;
执行成功后,count(*)了一把数据量级,发现确实回到正常水准,于是问题就那么初步解决了。然而如果问题那么容易就解决了,那就不会记录在ATA。上面的SQL留下了一个巨坑,有经验的同学可能一眼就看出来了😭😭😭,如果没有看出来的话,继续下文。
2.4 表坏了
-- 将正常数据复制到临时表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';
赶紧问了下GPT:
-
不使用drop语句。使用truncate语句,保留原表结构。
-- 将正常数据复制到临时表 CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';
– 清空原表数据,但不删除表
TRUNCATE TABLE MY_TABLE;
– 将临时表数据插入到原表
INSERT INTO MY_TABLE SELECT * FROM TEMP_TABLE;
-
使用CREATE TABLE LIKE 语句创建临时表,复制原表结构。
-- 创建和原表结构一样的临时表 CREATE TABLE TEMP_TABLE LIKE MY_TABLE;
– 将正常数据复制到临时表
INSERT INTO TEMP_TABLE SELECT * FROM MY_TABLE WHERE group <> ‘bad_group’;– 删除原表
DROP TABLE MY_TABLE;
– 将临时表重命名为原表
RENAME TABLE TEMP_TABLE TO MY_TABLE;
2.5 我觉得还能抢救一下
id
|
group
|
code
|
name
|
property
|
1
|
业务1
|
事件1
|
吃冻干
|
|
2
|
业务1
|
事件2
|
喂猫粮
|
|
3
|
业务2
|
事件1
|
睡觉
|
|
4
|
业务3
|
事件10086
|
下班
|
|
...
|
...
|
...
|
...
|
|
0(新导入)
|
业务1(重复数据)
|
事件1(重复数据)
|
吃冻干
|
|
0(新导入)
|
业务1(重复数据)
|
事件2(重复数据)
|
喂猫粮
|
|
0(新导入)
|
业务1
|
事件3
|
吃罐头
|
|
...
|
...
|
...
|
...
|
主键缺失导致插入了许多条id为0的数据,但应用不依赖mysql的自增id,暂时不影响线上应用查询结果;group+code的unique key缺失导致可能插入了重复数据,但应用侧做了去重兜底逻辑。也就是说不幸中的万幸,产品侧暂时无感,赶紧想办法挽回。
-
保留原有数据的同时,将表的主键、唯一键和查询索引进行重建。
-
将今天新导入的id=0的数据根据原唯一键的规则重新导入。
-- 1.复制id=0的数据到临时表, CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE id = 0;
– 2.删除源表中id=0的记录
DELETE FROM MY_TABLE WHERE id = 0;– 3.重建索引
ALTER TABLE MY_TABLE ADD INDEX …;
– 4.导回id=0的新数据
INSERT IGNORE INTO MY_TABLE SELECT * FROM TEMP_TABLE;
仔细思考,这次使用CREATE TABLE AS是没有问题的,因为这张临时表并不重要。DELETE由于数据量不大也没有性能问题。出于谨慎,上述4个SQL也是通过4个工单一个个提交执行的,便于中间过程观察。思路清晰,这次应该ok!
[ERROR] Duplicate entry '0' for key 'PRIMARY'【解决方法】:https://help.aliyun.com/document_detail/198139.html
TraceId : 0b8464d617047224212725080d867f
百思不得其解,按理想情况,重新导回数据后,id应该是从此刻的最大id开始自增才对(假设表中有10000条数据,那么新插入的数据理应id=10001),为什么还是0,并且还重复了?难道是之前的CREATE TABLE AS语句导致auto increment被清为0了?
ALTER TABLE MY_TABLE AUTO_INCREMENT = 10001;
然而仍然报这个错误。
MODIFY COLUMN `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID';
至此问题解决。
三、总结