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