MySQL binlog打满问题分析

Posted by [Kohn] on Thursday, June 29, 2023 本文阅读量

1 背景

最近遇到个问题, mysql实例由于binlog打满了磁盘挂了, 通过工具排查binlog的内容:

mysqlbinlog --no-defaults --base64-output=decode-row -vv mysql-bin.000001 -d DATABASE

发现大部分内容是一个后台每30s执行一次的update语句导致, 这个update语句 会更新A表, 而A表中有一个超大的字段, 每次update都会向binglog中写一份, 于是导致binlog过大, 来不及清理.

2 分析

实际上这个update语句的业务需求仅仅是轮询更新状态, 只需要修改其中一个 status字段就可以了, 按理来说没必要每次都把整行数据写到binlog里, 起初还 以为是代码问题, 明明只需要更新status字段但是实际上把整个ORM对象都给更 新了. 实际排查下来发现代码并没有问题, 确实仅仅更新了status字段.

那么问题应该就在于mysql的binlog机制了. 搜索资料发现, mysql的binlog有三种格式:

  • ROW: 每次都把整行内容记录下来. 缺点是对于比如说alter语句, 会把所有行都记录下来, 会导致binlog很大
  • STATEMENT: 每次仅记录执行的SQL语句
  • MIXED: 有些情况记录行内容, 有些情况记录语句

通过以下SQL可以查询当前数据库用的是哪种格式

show variables like 'binlog_format';

也可以进行设置:

set binlog_format='statement';

3 解决

由于用的是公司提供的标准数据库实例, 不太好定制mysql参数, 因此没有采用 设置成STATEMENT格式这种方式. 最终通过优化update执行逻辑, 尽量只在必要 的时候执行update来降低binlog生成速度.

4 附件

容器内的mysql实例抓包脚本

containerID=$(kubectl get pod -n $1 $2  -o template --template='{{range .status.containerStatuses}}{{if eq .name "galera"}}{{slice .containerID 9}}{{end}}{{end}}')
pid=$(docker inspect --format {{.State.Pid}} $containerID)
nsenter -n -t $pid tcpdump -i any -s 0 -l -w - dst port $3| strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
    if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
    {
        if (defined $q) { print "$q\n"; }
        $q=$_;
    } else {
        $_ =~ s/^[ \t]+//; $q.=" $_";
    }
}'

在pod所在宿主机上执行命令:

bash tcpdump.sh NAMESPACE PODNAME PORT