目录
前言
为什么在生产上主从环境情况下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就要用上了，对于这一块涉及到的面太庞大了因此我需要写一段时间，当我写完也会分享给到大家。
开始进入正文：
如果你是单机，如果你是自己在家玩。你的数据库里有亿级数据，你来一条：
delete from user_behavior_logs;然后你慢慢等个几小时，等到你的mysql暴了、硬盘被烧了都没事。
如果你在公司的生产环境，特别是在具有主从复制、1主多从甚至多主多从的环境下，你来一条delete命令，你知道会发生什么事吗？
为什么在生产上主从环境情况下mySQL特别容易卡死
它的原理其实是：mysql上的delete语句首先会同步到各个从库上，delete语句会产生redo日志也会同步在各个从库上，然后是mysql本身数据的binlog也在同步。三条操作*总mysql库量*你删除的数据量产生的：
- 网络io
- CPU消耗
- 磁盘读写
- 等等等其它
导致了上面的“主从延迟”这样的一个问题。
当发生主从延迟时其实是不用怕的，当生产环境读写操作频繁，总会发生一定概率的主从延迟。偶尔在大促季，一天发生个1-2次并且只要在主从延迟发生时，从库可以在5-10分钟内追平主库就不构成任何影响。
但是，如果发生了主从延迟，这个从延尺不断的在加大时间，超过了20分钟，30分钟，往40、60分钟走时，此时的整个db群就是：读业务全部受影响，因为从还没执行完自己的任务还要去追主，但是主上不断的在写进大量的数据。一般为了让从能够追上主，你就必须“锁主库”。
我们都知道，在生产环境下是不能锁主库的，一锁，所有的订单或者相关的“写操作”都没法提交了。
那么就有人说了：让从慢慢追主吧。
但是，些时你的整体网站是读写分离的，从库追不平主库，整体的读业务又受影响。
这个痛苦啊，此时就会发生著名的“主从延迟土拨鼠之日”，这是一个悖论，即：
眼看着数据库里的日志越来越多、占用的磁盘越来越大、影响了日常的正常报表、运维工作，再不删，整体业务要严重受到影响。但是呢，当你要去删，就又出现了严重的主从延迟，一样影响业务。咽不下去也吐不出，活活被憋死！
不要去怪设计不要去怪开发我们devops靠自己
“一千个观众眼中有一千个哈姆雷特”--《杀死比尔说的-哦，不是，是莎士比亚》
可是，我们在生产db上删除记录并且又能不影响主从同步的话就只有“一种”方法，我们在说任何方法前先来一个感性的认识，即我们先用“人类”可以懂的语言来描述一下这件事到底该怎么做。它其实可以分为两个场景来做，每个场景有不同的做法：
场景一、当要被删除的数据量远大于保留的数据量的场景下的做法
场景二、当要被删除的数据量远小于保留的数据量的场景下的做法
下面，就让我们来展开这两个场景吧。
场景一、当要被删除的数据量远大于保留的数据量的场景下的做法
假设我们实际要执行的是下面这样的一条sql：
delete from user_behavior_logs where created_datetime between '2016-08-10 17:20:00' and '2019-12-31 17:20:00';这涉及到在生产的主库上：
删除：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语句省略，因为这个太简单了）
insert into tmp_user_behavior_logs
(
ak,gu,ln,st,os,
ss,ip,bruser_behavior_logs,lan,fv,ifj,ifc,brs,cp,pn,pl,chn,sv,ev,et,pt,prn,created_datetime
)
select
ak,gu,ln,st,os,
ss,ip,bruser_behavior_logs,lan,fv,ifj,ifc,brs,cp,pn,pl,chn,sv,ev,et,pt,prn,now()
from user_behavior_logs where created_datetime between '2020-01-01 17:20:00' and '2020-08-10 17:20:00';
rename table user_behavior_logs to deleted_user_behavior_logs;
rename table tmp_user_behavior_logs to user_behavior_logs;
drop table deleted_user_behavior_logs;操作涉及数据量及环境
烂机器环境下的执行情况
以上的操作位于：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、被删除的数据很小小到只会引起10分钟内的主从延迟-不建议
第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秒的间隔以让从尽量追上主库。
分场景2、被删除的数据不小，但是如果直接delete一定会引起15分钟以上的主从延迟
假设我们实际要执行的是下面这样的一条sql：
delete from user_behavior_logs where created_datetime between '2020-04-07 09:00:00' and '2020-08-07 14:00:00';这涉及到在生产的主库上：
删除：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都是秒级，期间无任务报警。
结论
这种手法，只有在非营业时间去做，并且这点时间是完全可以忍受的，但是这种需求只应该每半年或者季度发生一次。
最终对于生产mysql的日志清理策略的best practice
- 策略一、如果需要删除的数据很多，多到比如说需要删除相当于原表数据内的50%，并且这个总量超过10个gb的话，都必须在非业务时间，有足够的空余时间（8小时内）才能去做这样的操作，操作前必须建立1v1数据库验证这个手法可以在8小时内完成，然后才可以去正式生产上做操作。并且这种操作视业务量，一般6个月或者最频繁3个月一次足以了；
- 策略二、如果需要删除的数据远大于需要保留的数据，比如说需要保留的数据不过百万来条，10个gb以内，完全可以使用场景一中的“5步曲”去做这个操作；
- 场景2中的分场景1，不建议原因有两点：1）你根本无法控制自动脚本的跑delete语句的准确率，特别是生产环境，你能确保定时触发的delete语句每次都删除的数据量是你规定的吗？2）如果在高并发环境下，为了确保被自动触发的delete语句永远是安全的你就必须去控制这个delete语句的数据范围，一般会控制在很少值，那么就是你删除的速度远远跟不上进入的数据，你的分小段delete清理日志手段或者在一开始业务量小的情况下有一定的效果，但是如果业务一旦爆增这种“涓涓溪流”的行为是毫无任何意义的；
- 无论采取的是策略一还是策略二，绝对不可以设成“自动脚本”，必须全程人为干涉和监控。就算用的是策略二、半年这么幸苦一晚上也是值得的；
附录
自动监控mysql主从延迟报警shell脚本-behind_master.sh
#!/bin/bash
#desc:脚本
#通过从库监控Seconds_Behind_Master的值监控延迟情况。
#该值为null或着超过告警阈值会报错.
#本脚还通过mysql命令执行情况判定mysql服务可用状态。
#author:hahaxiao_mk
#date:2018/04/27
#source ~/.bash_profile
#source ~/.bashrc
#----Seconds_Behind_Master的值
v_sbm='NULL'
#----检测域值,单位s
v_threshold=1
#----机器标示
v_machine_mark=ymkmysql
MYSQL_HOME=/usr/local/mysql
#-----发送告警信息函数
function f_send_msg()
{
echo "准备发送主从迟告警:${1} ${2}" >> /home/appadmin/behind_master.log
python /home/appadmin/send_alert_msg.py ${1}$2 101 1
#调用alert告警${v_java_home_bin}/java -jar /opt/config/inf/alarm.jar 1 $1 $2
}
#-----判定mysql服务状态
starttime=$(date +%Y-%m-%d\ %H:%M:%S)
v_mysql_status=`mysql -uroot -phaha -P3306 -h10.0.0.1 -e "show slave status\G"|grep Seconds_Behind_Master`
echo "开发库10.0.0.1于 ${starttime} -> 主从延迟目前为：${v_mysql_status}" >> /home/appadmin/behind_master.log
if [ $? -eq 1 ]
then
v_err_msg="mysql is not available! "
# f_send_msg ${v_mobile} ${v_err_msg}
echo ${v_err_msg}
f_send_msg ${v_err_msg}
exit
fi
#------判定延迟情况
v_sbm=`echo ${v_mysql_status}|awk -F ":" '{print $2}'`
if [ "${v_sbm}" = " NULL" ]
then
v_err_msg="开发库10.0.0.1于 ${starttime} -> 发生主从延迟为: ${v_sbm}!"
# f_send_msg ${v_mobile} ${v_err_msg}
#echo ${v_err_msg}
f_send_msg ${v_err_msg}
elif [ ${v_sbm} -gt ${v_threshold} ]
then
v_err_msg="开发库10.0.0.1发生主从延迟${v_sbm}s!"
echo ${v_err_msg} >> /home/appadmin/behind_master.log
#f_send_msg ${v_mobile} ${v_err_msg}
#echo ${v_err_msg}
f_send_msg ${v_err_msg}
fi使用CentOS的crontab设置监控脚本每5s运行一次写法
crontab -e然后把下面这一陀复制进去吧(crontab的最小条件为分钟，因此要做成秒必须化解成以下的语句，这是一个实用技巧
* * * * * sh /home/appadmin/behind_master.sh
* * * * * sleep 5; sh /home/appadmin/behind_master.sh
* * * * * sleep 10; sh /home/appadmin/behind_master.sh
* * * * * sleep 15; sh /home/appadmin/behind_master.sh
* * * * * sleep 20; sh /home/appadmin/behind_master.sh
* * * * * sleep 25; sh /home/appadmin/behind_master.sh
* * * * * sleep 30; sh /home/appadmin/behind_master.sh
* * * * * sleep 35; sh /home/appadmin/behind_master.sh
* * * * * sleep 40; sh /home/appadmin/behind_master.sh
* * * * * sleep 45; sh /home/appadmin/behind_master.sh
* * * * * sleep 50; sh /home/appadmin/behind_master.sh自动发送告警信息到企业微信接口（aldi-cupidmq）的python脚本
#!/usr/bin/python
import re
import requests
import time
import json
import sys
url='http://localhost:9081/alertservice/sendMsg'
if (len(sys.argv)>1):
inputedmsg=sys.argv[1]
msgtype=sys.argv[2]
modelId=sys.argv[3]
print('input message->'+inputedmsg+' input msgtype->'+msgtype+' modelId->'+modelId)
currentTime=time.strftime('%Y/%m/%d %H:%M:%S',time.localtime(time.time()))
print 'current time is ', currentTime
if(msgtype=='101'):
wechatmsg='Issue happened on ' +currentTime +':\n'+ inputedmsg
wechatcontent={"modelId": modelId, "content": wechatmsg}
wechatheaders = {"content-type": "application/json; charset=UTF-8", "type": "101"}
req = requests.post(url, data=json.dumps(wechatcontent),headers=wechatheaders)
print(req.text)
elif(msgtype=='102'):
print('send mail msg')
else:
print('inputed msgtype required 101|102')
else:
print('inputed msg can not be null')企业微信收到主从延迟后的展示效果
