线上环境MySQL数据误删一例

GrowthDBA 2022-01-08

634

晚上19:18，下班回家的地铁上，电话铃突然响了...，接通电话：

“路哥，还在公司吗？”

“已经下班啦，在地铁上”

“我把生产环境今晚22点要上线的全表数据删了，能回来救个急吗？”

“......（其实我的内心是拒绝的，本来能早点回家的）”

“......路哥，挺急的......”

“好，我往回走（幸好地铁只开出一站地）”

.....

挂掉电话，坐上了反方向回公司的地铁。回公司的路上，心情很复杂，懂得都懂。22点上线，19点把要上线的数据删了。。。我。。。

内心再“惊涛骇浪”又有什么用，谁让咱DBA的职责之一就是救火呢，事情已经发生，那就好好履行我们的职责。转念一想：体现价值的时候到了

。

数据是怎么删除的 & 数据恢复

定位数据是如何被删除的

回到公司的第一件事情，就是向当事人确认数据是何时、如何删除的。

事件还原经过：

被删表所在的数据库是即将要上线新功能所在的库，因与该系统实例上其他库无业务关联且独立，初始化的数据早在一月前就部署到了生产环境，但此库还未正式启用。

因上线的新功能涉及到和我司其余系统的交互，被删表所在库产生了上线前1天与其他系统联调功能验证的上线数据和中间测试数据。上线之际，在做最后程序调试和数据校验过程中，删除测试数据的时候意外发生了。

当事人本想使用以下SQL完成测试数据清理工作：

DELETE FROM [table_name] t1 WHERE id NOT IN (SELECT t2_id FROM [table_name] t2);

但是真实操作时候报了语法错误，无奈重新编写删除测试数据SQL，可能是由于基于上线前的各项检查项较多，疏忽大意了，将删除测试数据的SQL没有编写完整，写成如下形式直接回车执行了：

DELETE FROM [table_name] WHERE id;

悲剧就这样发生了，整张表约15W行数据全部被删除。

数据恢复

经过和当事人沟通确认，上线新库中共计10张表，代码层面均有关联关系。1张表的数据被删，其余表的数据都混乱了，无法使用，包括其他关联系统产生的中间数据都得删掉，然后重新通过该系统再次处理重新联调写入。

脑子中快速想出几种恢复方案：

1、通过回滚工具，如binlog2sql、my2sql、MyFlash进行回滚操作；

2、通过物理全备+binlog方式（截止到删除时间点前）恢复到中间机，然后把单表逻辑备份出来，再还原到生产环境；

3、一天前，做了一个这个库的全库逻辑备份，直接全库恢复。

这时，各个项目组的主干队员都已经在会议室坐定，开始讨论最终的恢复方案。

方案1：使用回滚工具回滚。无奈为了更快的写入速度和减少日志写入量，binlog_row_image=MINIMAL的配置并不会保留数据行的前镜像，所以无法使用回滚工具，方案1被PASS掉。

方案2：物理全备+binlog方式。备份方式是每天晚上的23点物理全备，加上申请中间恢复机，恢复和回放binlog的时间，无法保证在22点（还有不到2小时）上线，方案2被PASS掉。

方案3：拿前一天这个库的全库逻辑备份进行恢复，确定了各系统清理混乱数据的时间、重新联调写入的时间和数据库恢复的时间可以满足上线时间点，最终敲定使用方案三，数据一致性和恢复时间都满足需求。

对于需要清理混乱数据任务的其余项目组骨干成员，都是10多年经验的Java老兵，所以不用担心误删数据的问题。我和当事人再次确认了全库数据不可用后，同步开始了恢复数据的工作。

备份时的语句如下：

/usr/local/mysql3306/bin/mysqldump -uroot -p --single-transaction --set-gtid-purged=off -B [database_name] > /backup/[database_name]_`date +%Y-%m-%d_%H-%M-%S`.sql

删除原库：

DROP DATABASE [database_name];

备份数据还原：

mysql3306 -p < /backup/[database_name]_`date +%Y-%m-%d_%H-%M-%S`.sql

不到半个小时，在大家伙的共同努力下，混乱数据的清理和数据恢复工作全部完成。重新联调写入的过程也没有发生什么问题。但上线代码过程中还是出了一点小插曲，不过最终在凌晨03:00左右全部上线成功并验收完毕。

收工，打车回家。

误删数据后的思考和测试

回忆一下当时的误删数据的SQL语句：

DELETE FROM [table_name] WHERE id;

据当事人回忆，主观认为这样书写SQL的形式理应该报错，没想到执行成功了。被删表的表结构如下：

CREATE TABLE `xxxxxx` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `app_key` varchar(64) COLLATE utf8_unicode_ci NOT NULL,  `app_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,  `owner_user_id` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'xxx',  `category_id` int(64) DEFAULT NULL COMMENT 'xxx',  `create_date` datetime DEFAULT NULL COMMENT 'xxx',  `modify_date` datetime DEFAULT NULL COMMENT 'xxx',  `icon_url` longtext COLLATE utf8_unicode_ci COMMENT 'xxx',  `status` varchar(4) COLLATE utf8_unicode_ci DEFAULT '0' COMMENT 'xxx',  `merge_to_app` int(11) DEFAULT NULL COMMENT 'xxx',  `merge_num` int(11) NOT NULL DEFAULT '0' COMMENT 'xxx',  PRIMARY KEY (`id`),  KEY `owner_user_id` (`owner_user_id`),  KEY `create_date` (`create_date`),  KEY `app_key` (`app_key`),  KEY `application_app_name_IDX` (`app_name`) USING BTREE,  KEY `application_status_IDX` (`status`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='xxxxxxxx';

说句实在话，我摊牌了，几乎没有见过SQL语句WHERE关键字后面直接跟字段如"DELETE FROM [table_name] WHERE [字段];"这样的写法。潜意识传统使用方法中，字段后面会继续书写如=、!=、<>、IN、LIKE等条件判断符号和条件。于是乎，有了下面的测试。

老样子，还是准备一张测试表，插入一些测试数据。

CREATE TABLE `student_info` (  `id` int NOT NULL AUTO_INCREMENT COMMENT '学生编号',  `student_name` varchar(20) NOT NULL COMMENT '学生姓名',  `address` varchar(100) DEFAULT '北京市' COMMENT '家庭住址',  `extra` varchar(50) DEFAULT NULL COMMENT '额外信息',  `remark` tinytext COMMENT '备注',  PRIMARY KEY (`id`),  UNIQUE KEY `uniq_extra` (`extra`)) ENGINE=InnoDB COMMENT='学生表信息表';INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10001, '赵甲', '北京市前门大街', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10002, '钱乙', '北京市东直门', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10003, '孙丙', '北京市西直门', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10004, '李丁', '北京市望京', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10005, '周戊', '北京市海淀区', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10006, '吴己', '北京市朝阳区', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10007, '郑庚', '北京市西城区', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10008, '冯戌', '北京市石景山区', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10009, '褚壬', '北京市昌平区', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(10010, '卫癸', '北京市小汤山', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(20001, 'Danny', 'America', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(20002, 'Jenny', 'Canada', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(20003, '工藤新一', 'Japan', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(20004, '王子', '北京市', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(20005, '武丑', '北京市', NULL, NULL);INSERT INTO student_info (id, student_name, address, extra, remark) VALUES(30001, '蒋寅', '上海市浦东新区', '交换生', NULL);SELECT * FROM student_info;

测试场景1：DELETE列值为NULL的tinytext字符串类型字段。

DELETE FROM student_info WHERE remark;

结果：数据不会被删除。

测试场景2：DELETE列值部分为NULL的varchar字符串类型字段。

DELETE FROM student_info WHERE extra;

结果：执行报错，ERROR 1292 (22007): Truncated incorrect DOUBLE value: '交换生'，数据不会被删除。

测试场景3：DELETE列值都有数据的varchar字符串类型字段。

DELETE FROM student_info WHERE student_name;

结果：执行报错，ERROR 1292 (22007): Truncated incorrect DOUBLE value: '赵甲'，数据不会被删除。

测试场景4：模拟生产环境误删数据操作。DELETE列值都有数据的int整型类型字段。

DELETE FROM student_info WHERE id;

结果：数据全部被删除。

测试场景5：DELETE列值部分有数据的double双精度数字类型字段。

上面我们的测试数据被删除了，我们再来构造一些测试数据。

ALTER TABLE student_info ADD COLUMN age DOUBLE DEFAULT NULL AFTER address;INSERT INTO student_info (id, student_name, address, age, extra, remark) VALUES(10001, '赵甲', '北京市前门大街', NULL, NULL, NULL);INSERT INTO student_info (id, student_name, address, age, extra, remark) VALUES(10002, '钱乙', '北京市东直门', 20.5, NULL, NULL);INSERT INTO student_info (id, student_name, address, age, extra, remark) VALUES(10003, '孙丙', '北京市西直门', 22.3, NULL, NULL);INSERT INTO student_info (id, student_name, address, age, extra, remark) VALUES(10004, '李丁', '北京市望京', 21.6, NULL, NULL);SELECT * FROM student_info;

执行测试语句：

DELETE FROM student_info WHERE age;

结果：匹配到数据的行都被删除了。

小结

通过上面的测试，我们可以得到有关"DELETE FROM [table_name] WHERE [字段];"这样SQL写法的几个结论：

1、WHERE后面所跟字段数据类型为字符串时，SQL不会执行成功，且数据不会被删除；

2、测试场景2、测试场景3的测试结果可以看出WHERE后面所跟字段会遍历匹配表中的数据，忽略掉NULL值，并且匹配到的数据会发生隐式类型转换，根据报错我们可以看出，隐式类型转换是将字符串类型的数据转换成DOUBLE双精度数字类型了。

还记得上篇文章MySQL之执行计划详解（二）我们学习了FORMAT=JSON格式的执行计划，大家应该还记得输出内容"attached_condition"的含义：对表访问时针对单表查询的条件。为了方便理解，还原了一下测试数据，看一下"DELETE FROM [table_name] WHERE [字段];"的执行计划。

DESC FORMAT=JSON DELETE FROM student_info WHERE address\G

由此可见："WHERE address"被优化器改写成了"where (0 <> `employee`.`student_info`.`address`)"，因为要与数字0进行匹配，且无法将字符串类型的字段address转换为数字类型，所以导致报错不能成功执行。由此得到下面第3个结论。

3、"DELETE FROM [table_name] WHERE [字段];"这种写法默认会将WHERE后面的[字段]类型转换为DOUBLE数字类型与数字0匹配，不等于0的数据都会被匹配到。所以测试场景4和测试场景5的结果也就理所应当了。毕竟0不能和字符串相比较，比较结果为FALSE，恒为0且等于0，所以不会被匹配到；NULL和0比较结果还是NULL，也不会被匹配到；只有[字段]类型为数字类型的时候才会匹配出不等于0的所有行，这也就理解了为什么全表会被删除了，因为id字段是主键（非空且唯一，设置自增属性后值是从1开始递增）。

SELECT id FROM student_info WHERE `employee`.`student_info`.`id` <> 0;SELECT 0 <> '北京市前门大街';SELECT 0 <> NULL;

经过这次误删数据的栗子🌰，得到了以下警示：

1、在执行UPDATE、DELETE操作时一定、一定、一定要注意书写格式，不要再使用"DELETE FROM [table_name] WHERE [字段];"这种SQL写法执行DELETE、UPDATE操作，侥幸的话，字段类型是字符串，无损；不幸的话，全表被删。所以还是不要抱有侥幸心理。

2、对生产环境心生敬畏，不要在执行框、命令行里直接拼写SQL，测试通过后再上线生产环境；最好先将DELETE、UPDATE语句的匹配条件改写成SELECT语句查看即将操作的数据是否属实。有条件的可以每次执行操作前都看一下执行计划，毕竟执行计划不会执行，以便提前发现问题。

3、备份的重要性。

4、团队合作的力量，众人拾柴火焰高。

工作中我们不可能遇到所有的问题，今天这个案例就是我自己经验累积的过程。大家是否也遇到过类似的情况呢？希望今天的文章能给大家带来收获，每天进步一点点，与君共勉。

end

mysql

文章转载自 GrowthDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

线上环境MySQL数据误删一例

评论