如何在生产环境mysql删除亿万级数据解并且不影响数据库主从延迟的解决方案

目录

前言

为什么在生产上主从环境情况下mySQL特别容易卡死

不要去怪设计不要去怪开发我们devops靠自己

场景一、当要被删除的数据量远大于保留的数据量的场景下的做法

操作涉及数据量及环境

烂机器环境下的执行情况

好机器环境下的执行情况

场景二、当要被删除的数据量远小于保留的数据量的场景下的做法

分场景1、被删除的数据很小小到只会引起10分钟内的主从延迟-不建议

操作涉及数据量及环境

烂机器环境下的执行情况

好机器环境下的执行情况

分场景2、被删除的数据不小，但是如果直接delete一定会引起15分钟以上的主从延迟

烂机器环境下的执行情况

好机器环境下的执行情况

最终对于生产mysql的日志清理策略的best practice

附录

自动监控mysql主从延迟报警shell脚本-behind_master.sh

使用CentOS的crontab设置监控脚本每5s运行一次写法

自动发送告警信息到企业微信接口（aldi-cupidmq）的python脚本

企业微信收到主从延迟后的展示效果

本方案适合：无关业务的“日志数据”，但往往日志数据是最最占用我们的整体系统性能的，因此对这样的日志，我们是需要进行定期清理的。

如果你要说：业务数据也需要那么我告诉你，业务数据肯定用的是本方案中的场景2中的分场景2模式（只有这一条路），但是业务数据会暴发到你连本方案都无法覆盖的那一天的（很快的，如我上一家公司：几千万的会员生成业务流水），那么当本方案都失效时怎么办？

答案就是：垂直折分，hash一致算法，sharding sphere就要用上了，对于这一块涉及到的面太庞大了因此我需要写一段时间，当我写完也会分享给到大家。

开始进入正文：

如果你是单机，如果你是自己在家玩。你的数据库里有亿级数据，你来一条：

然后你慢慢等个几小时，等到你的mysql暴了、硬盘被烧了都没事。

如果你在公司的生产环境，特别是在具有主从复制、1主多从甚至多主多从的环境下，你来一条delete命令，你知道会发生什么事吗？

它的原理其实是：mysql上的delete语句首先会同步到各个从库上，delete语句会产生redo日志也会同步在各个从库上，然后是mysql本身数据的binlog也在同步。三条操作*总mysql库量*你删除的数据量产生的：

导致了上面的“主从延迟”这样的一个问题。

当发生主从延迟时其实是不用怕的，当生产环境读写操作频繁，总会发生一定概率的主从延迟。偶尔在大促季，一天发生个1-2次并且只要在主从延迟发生时，从库可以在5-10分钟内追平主库就不构成任何影响。

但是，如果发生了主从延迟，这个从延尺不断的在加大时间，超过了20分钟，30分钟，往40、60分钟走时，此时的整个db群就是：读业务全部受影响，因为从还没执行完自己的任务还要去追主，但是主上不断的在写进大量的数据。一般为了让从能够追上主，你就必须“锁主库”。

我们都知道，在生产环境下是不能锁主库的，一锁，所有的订单或者相关的“写操作”都没法提交了。

那么就有人说了：让从慢慢追主吧。

但是，些时你的整体网站是读写分离的，从库追不平主库，整体的读业务又受影响。

这个痛苦啊，此时就会发生著名的“主从延迟土拨鼠之日”，这是一个悖论，即：

眼看着数据库里的日志越来越多、占用的磁盘越来越大、影响了日常的正常报表、运维工作，再不删，整体业务要严重受到影响。但是呢，当你要去删，就又出现了严重的主从延迟，一样影响业务。咽不下去也吐不出，活活被憋死！

“一千个观众眼中有一千个哈姆雷特”--《杀死比尔说的-哦，不是，是莎士比亚》

可是，我们在生产db上删除记录并且又能不影响主从同步的话就只有“一种”方法，我们在说任何方法前先来一个感性的认识，即我们先用“人类”可以懂的语言来描述一下这件事到底该怎么做。它其实可以分为两个场景来做，每个场景有不同的做法：

场景一、当要被删除的数据量远大于保留的数据量的场景下的做法

场景二、当要被删除的数据量远小于保留的数据量的场景下的做法

下面，就让我们来展开这两个场景吧。

假设我们实际要执行的是下面这样的一条sql：

这涉及到在生产的主库上：

删除：1700万条记录

实际需要保留的数据：30万条，条件： between '2020-01-01 17:20:00' and '2020-08-10 17:20:00'，30万条数据。

那么我们的做法为：

1) 照着要被删除的table名建立一个完全一模一样名字带tmp_前缀的table名

2）选取要保留的数据 into tmp_table

3）rename table 原来的table名 to deleted_原来的table名

4）rename table tmp_table to 原来的table名

5）drop table deleted_原来的table名

它化成具体的操作就是以下这么几条sql（create table语句省略，因为这个太简单了）

以上的操作位于：base 1000万条记录，同时使用压力测试工具不断的往数据库中以每5秒进5000条数据的速度插入新数据，master slaver主从情况下，在4c cpu, 8gb ram，非ssd磁盘执行情况：

对于insert into ...select from...语句涉及到30万数据量的情况下，执行时间为：16s，执行期间有报主从同步，主从同步一开始值有点高为70s，这个报警持续了5分钟左右即消失；

对于rename与drop语句执行只用了1s，执行过程无任何主从同步报警；

结论

就算主从报警，为完全可接受范围内。

以上的操作位于：base 1000万条记录，同时使用压力测试工具不断的往数据库中以每5秒进5000条数据的速度插入新数据，master slaver主从情况下，在64c cpu, 256gb ram，ssd磁盘执行情况：

对于insert into ...select from...语句涉及到30万数据量的情况下，执行时间为：1.3s，执行期间，有报主从同步，主从同步一开始值为5s，这个报警持续了15s就消失了；

对于rename与drop语句执行只用了1s，执行过程无任何主从同步报警；

结论

就算主从报警也可以忽略不计。

第1步：确定要被删除的id范围；

第2步：使用存储过程，分成小批量删除，每次删除的量不要超过（delete+where条件）万条。删除后停一下，再删下一批，全程最好有监控报警随时看着

以上的操作位于：base 2.1亿条记录，总共：460gb，同时使用压力测试工具不断的往数据库中以每5秒进5000条数据的速度插入新数据，master slaver主从情况下，在4c cpu, 8gb ram，非ssd磁盘执行情况：

对于delete from user_behavior_logs where id between 1  and 5000; 每隔30秒我做一次这样的delete操作。

实际操作时间为：3.7s，主从延迟报警持续了：59s即告结束。

结论

就算主从报警，为完全可接受范围内。

以上的操作位于：base 1000万条记录，每5秒进5000条数据，master slaver主从情况下，在64c cpu, 256gb ram，ssd磁盘执行情况：

对于delete from user_behavior_logs where id between 1  and 5000; 每隔30秒我做一次这样的delete操作。

实际操作时间为：1s，主从延迟报警持续了：8s即告结束。

结论

就算主从报警，为完全可接受范围内。每次删除需要少数据量，频率不能太高，每次删完当中需要有一个30-60秒的间隔以让从尽量追上主库。

假设我们实际要执行的是下面这样的一条sql：

这涉及到在生产的主库上：

删除：170万条记录

实际需要保留的数据：9000w条记录，条件为created_datetime between '2020-04-07 09:00:00' and '2020-08-07 14:00:00';

那么我们的做法为：

第一步：mysqldump成一个文件；

第二步：把dump出去的文件导入到一个新的表中去

第三步：使用分场景2中的rename手法来

注意：这个手法只有在非业务时间段即一般在零晨去做这个事情，mysqldump回新表时，会造成不小的主从延迟，来看一下本人的实际操作情况。

以上的操作位于：base 1亿条记录，总共：140gb，同时使用压力测试工具不断的往数据库中以每5秒进5000条数据的速度插入新数据，master slaver主从情况下，在4c cpu, 8gb ram，非ssd磁盘执行情况：

对于delete from user_behavior_logs where created_datetime between '2020-04-07 09:00:00' and '2020-08-07 14:00:00'; 要删除的数据多达：170w条，需要保留的有9900w条。

用mysqldump导出和恢复9900w条记录总计用了：6小时，造成了严重的主从同步，最后不得不锁主库，再用mysqldump追平从库，最后造成整个操作没法完成。

结论

该操作确实很耗时，在一般机器上很难模拟，也验证了，这种操作很耗资源。

以上的操作位于：base 1亿条记录，总共：140gb，同时使用压力测试工具不断的往数据库中以每5秒进5000条数据的速度插入新数据，master slaver主从情况下，在64c cpu, 256gb ram，ssd磁盘执行情况：

对于delete from user_behavior_logs where created_datetime between '2020-04-07 09:00:00' and '2020-08-07 14:00:00'; 要删除的数据多达：170w条，需要保留的有9000w条。

用mysqldump导出和恢复9900w条记录总计用了：3小时，从库每3分钟报一次主从同步，连续了3小时直到mysqldump把9900w条记录导入了新表才告终目。而后续的rename表名和drop都是秒级，期间无任务报警。

结论

这种手法，只有在非营业时间去做，并且这点时间是完全可以忍受的，但是这种需求只应该每半年或者季度发生一次。

然后把下面这一陀复制进去吧(crontab的最小条件为分钟，因此要做成秒必须化解成以下的语句，这是一个实用技巧