MySQL惊魂记:线上数据库故障排查与修复

原文标题:破防了,谁懂啊家人们:记一次mysql问题排查

原文作者:阿里云开发者

冷月清谈:

本文记述了一次因数据同步错误导致线上MySQL数据库故障的排查和修复过程。由于使用了`create table as`语句创建临时表,导致临时表缺少原表的索引和主键,后续操作引发了一系列问题,包括数据重复、自增ID失效等。文章详细介绍了问题的发现、分析和解决步骤,并反思了线上操作的规范性和风险控制。

怜星夜思:

1、文章中提到使用`create table as`语句会导致新建表缺少原表的索引和主键,那么还有哪些常见的SQL操作需要注意,避免类似问题发生?
2、在进行线上数据库操作时,如何保证操作的安全性,避免误操作或数据丢失?
3、文章最后提到了针对数据导入源头进行限制,避免类似事件再次发生,那么具体可以采取哪些措施进行源头控制?

原文内容

阿里妹导读


某天用户反馈线上产品报错,本文记录了这次mysql问题排查和修复的过程,希望给大家参考。

一、前言

谁懂啊家人们,作为一名java开发,原来以为mysql这东西,写写CRUD,不是有手就行吗;你说DDL啊,不就是设计个表结构,搞几个索引吗。
键盘撒一把冻干,我家猫也能来上班。——粥师傅
结果一次线上出问题了,一环接一环,不仅猫上不了班,晚上还得等我加班回家,眼巴巴吃不到冻干。

(你看我锤不锤你就完事了)

1.1 表结构

简单介绍一下出问题的表。
一张元数据表,提取出重点部分,抽象出来的结构如下,
(id, group, code, name,property1, property2, ...)
id
group
code
name
property
1
业务1
事件1
吃冻干

2
业务1
事件2
喂猫粮

3
业务2
事件1
睡觉

4
业务3
事件10086
下班

...
...
...
...


主键primary key:id
唯一键unique key:group + code,
也就是说在该group内,code是唯一的。
此外,我们有一个dataworks离线任务,每天会往该表中写入记录,采用insert ignore into的方式,如果遇到重复的group+code,就不写入。
整体逻辑比较清晰明了。数据量级也比较小,每个group大约几百上千条数据,总数据量不到10w。

二、问题排查和修复过程

2.1 最初的问题

某天用户反馈线上产品报错,迅速排查发现,上述表中新接入了一个业务:在dataworks接入了一个新的group(假设名字叫bad_group),同步任务在当天异常往mysql表里导了千万量级数据(其中实际有效的只有几千条,其余为脏数据),导致线上产品查询缓慢、报错。定位到问题以后,第一反应是把错误的bad_group的数据先全部清掉,保留其他group的数据,恢复上线查询,然后再慢慢想办法重新导入正确数据。
顺带一提,以下SQL执行等全程都使用弹内DMS平台进行操作。

2.2 初步思路

清理错误数据v1
DELETE FROM MY_TABLE
WHERE group = 'bad_group';

直接执行上面这个SQL进行普通数据变更可行吗?显示不行,有经验的同学都知道,在千万量级下,清理大量数据会超过binlog限制,导致SQL无法被执行。

因此我们直接用的是另一个方案,无锁数据变更,SQL依旧和上面保持一致,关于无锁变更的描述可见平台的介绍:

本以为用无锁变更差不多就能解决问题了,然而执行过程中发现由于数据量比较大,无锁变更分批执行SQL效率非常低,估算大概要2h以上来清空这几千万的脏数据,不能接受这个方案,执行了几分钟果断放弃。

2.3 另辟蹊径

于是只能换一种方式。重新考虑这个问题,我们需要保留的数据仅仅只有千万中的不到10万条非bad_group的数据,因此除了删除bad_group数据这种方法,更简单的是将有效数据先copy到一张临时表中,然后drop原表,再重新创建表,将临时表中数据拷贝回来。为什么drop表会比delete数据快呢,这也是一个重要知识点。

DROP
TRUNCATE
DELETE
删除内容
删除整张表数据,表结构以及表的索引、约束和触发器
删除全部数据
删除部分数据(可带where条件)
语句类型
DDL
DDL
DML
效率
最高
较高
较低
回滚
无法回滚
无法回滚
可以回滚
自增值
-
重置
不重置

举个不那么恰当的例子,好比房东把房子租给别人,到期后发现房子里全都是垃圾,DELETE语句是将这些垃圾一件一件清理出来,只保留原来干净的家具。TRUNCATE相当于一把火把房子里所有东西都烧了,DROP语句就是房子直接不要了。
这里drop和truncate的方案都可以选择,我们采用了房子不要了的方案,直接drop表:
清理错误数据v2
-- 将正常数据复制到临时表
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 表坏了

当天一切正常。然而好景不长,第二天,有同学往表里导数时发现了问题,在没有指定id的情况下,灌入的所有行id=0。我一脸黑人问号?
id不是默认主键吗,怎么会这样,重新打开表结构一看,所有的索引都消失了!
此时心里凉了半截,马上回想到一定是这个语句有问题:
-- 将正常数据复制到临时表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';

赶紧问了下GPT:

果不其然,create table as 只会复制表的列信息结构和数据,不会复制表索引、主键等信息。
也就是说,这张表已经被玩坏了!现在回看这个问题,当时至少有两种方式避免这个问题:
  1. 不使用drop语句。使用truncate语句,保留原表结构。
清理错误数据v3
-- 将正常数据复制到临时表
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;

  1. 使用CREATE TABLE LIKE 语句创建临时表,复制原表结构。

清理错误数据v4
-- 创建和原表结构一样的临时表
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. 保留原有数据的同时,将表的主键、唯一键和查询索引进行重建。
  2. 将今天新导入的id=0的数据根据原唯一键的规则重新导入。
但我们知道,执行添加唯一键的语句时,会检查此时表里是否有不满足唯一的数据,如果有的话该语句会被拒绝执行。因此这批带有重复的新数据的干扰,不能直接alter table add unique key。
灵机一动,采取和昨日一样的临时表方案,即先将id=0的数据复制到临时表,删除原表中所有id=0的数据,然后重建索引,再将id=0的数据使用insert ignore into语句导回来。对应的SQL:
重建表
-- 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!

当执行完上面第2条语句,删除id=0的数据后,执行了select count(*)简单确认了一下,没想到这一确认还真出了问题,delete过后数据条数没有变?!经过紧张的思考🤔,新机子哇伊自摸一刀子:猜测大概率是主备没有实时同步。关于这一点,我们线上用的MYSQL是主库,工单执行的SQL也是在主库执行,但DMS控制台为了不影响线上正常使用,是在备库进行查询,正常情况下主备库会实时同步。但当一些耗时SQL执行时,就会出现同步延迟。为了验证这一点,可以在主库select count(*),DMS也提供了切换选项,只是默认会选备库。
这张截图是后来我咨询了DBA后帮忙查询到的结果,确实是有延迟。
继续重建索引,包括主键primary key、唯一键unique key、普通索引key。没有问题。
最后一步,将id=0的数据从临时表导回原表,就可以回家喂🐱了,然而工单一直执行报错。
[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了?

按照这个思路,回忆起之前在日常环境写假数据的时候,如果指定了一个比较大的id,那么后续所有新数据都会在这个id基础上生成(比如当前表中只有10条记录,id=10,插入一条id=100的数据,后续数据就会接着id=101继续生成。)尝试过后发现依旧报错。
我有点汗流浃背了。
为什么不管用?又用GPT查询了设置表auto increment值的方法:
ALTER TABLE MY_TABLE AUTO_INCREMENT = 10001;

然而仍然报这个错误。

绝望。
此时已经夜里快十点,周围没有什么人了,本来空调澎湃吹动热气的声音也不知不觉趋于安静,我望向对面楼栋,灯光明灭可见。一月小寒的夜晚有些冷,我突然想起李清照的那句“冷冷清清,凄凄惨惨戚戚”,不就在描绘这个场景吗?
最后的最后,再次对比日常库的正常表结构,发现原来是id的auto increment也消失了。原来还是create table as 留下来的坑,难怪之前重新设置auto increment也不生效。为什么没有第一时间发现到这一点,因为按上面gpt的回答,该语句对"列结构"是可以正常复制的,只有索引、主键等信息会丢失,原以为"AUTO_INCREMENT"是属于id这一列的列信息,看起来并不是。
重新设置id使用自增:
MODIFY COLUMN `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID';

至此问题解决。

三、总结

一切的问题源自对create table as这个语句的不熟悉,这个语句建表导致的表主键、索引、auto_increment的丢失。
不熟悉的SQL不能乱跑😭😭😭
后面也在反思在线上使用drop和truncate有些激进。不过当时考虑到是内部应用并且查询已经不可用了。也欢迎读者同学们思考和反馈,针对这样的场景是否有更好处理建议。 
顺便说明:后续我们针对odps导入mysql源头就做了限制,防止这类事情再次发生。
​​

使用insert into ... select ...语句将数据从一个表复制到另一个表时,需要确保目标表的结构和源表一致,包括列名、数据类型、索引等。否则可能导致数据插入失败或数据不一致。

建立数据导入监控机制,实时监控数据导入过程,例如导入数据量、导入速度等,及时发现异常情况并进行处理。

在数据导入之前进行数据校验,例如检查数据格式、数据量、数据内容等,确保数据符合预期,避免脏数据进入数据库。

合理使用数据库权限管理机制,根据不同的角色分配不同的权限,例如只允许特定用户进行DDL操作或访问敏感数据,避免越权操作。

truncate table语句会清空表数据,但保留表结构,包括索引和主键。与之相比,drop table语句会删除整个表,包括表结构。所以在需要清空数据但保留表结构的场景下,应该优先使用truncate table

对数据导入进行限流,例如限制每分钟导入的数据量,避免因为数据量过大导致数据库负载过高或出现性能问题。

进行DDL操作,例如修改表结构、添加索引等,需要评估对线上业务的影响,特别是在大数据量场景下,DDL操作可能导致锁表,影响业务正常运行。建议在业务低峰期进行DDL操作,并提前做好数据备份和回滚方案。

使用数据库管理工具进行操作,例如DMS、Navicat等,这些工具提供了可视化界面和操作日志,方便进行操作审计和问题追溯。

建立完善的操作规范和流程,例如操作前进行风险评估、备份数据、进行充分测试、多人审核等。特别是在生产环境进行操作时,更要谨慎操作,避免因为误操作导致数据丢失或业务中断。