问题背景:
客户反馈slave每到凌晨就出现延迟现象,需要排查原因
1>首先查看master库每天凌晨有什么操作:
分析binlog日志
1 mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000204 > mysql-bin.000204.sql
查看日志在凌晨有大量的delete操作
#191126 0:08:35 server id 1 end_log_pos 1073744234 CRC32 0x11bf4f5d Table_map: `user`.`table_name` mapped to number 4012691
# at 1073744234
#191126 0:08:35 server id 1 end_log_pos 1073744393 CRC32 0xa3229214 Delete_rows: table id 4012691 flags: STMT_END_F
### DELETE FROM `user`.`table_name`
### WHERE
### @1=259121
### @2='2019-11-25'
### @3=1
### @4=1
### @5=0
### @6='2019-11-25'
### @7='08:30'
### @8='2019-11-25'
### @9='17:30'
### @10=540
### @11=''
### @12=''
### @13=NULL
### @14=''
### @15=''
### @16=NULL
### @17=0
### @18=0
### @19=0
### @20=0
### @21=0
### @22=540
### @23=0
### @24=0
### @25=0
### @26=0
### @27=0
### @28='{}'
# at 1073744393
#191126 0:08:35 server id 1 end_log_pos 1073744424 CRC32 0x5a03e7aa Xid = 25909247548