分析MySql的binlog

最新推荐文章于 2025-03-06 10:03:31 发布

knowwait

最新推荐文章于 2025-03-06 10:03:31 发布

阅读量398

点赞数

分类专栏： mysql 文章标签：大数据 mysql 运维

本文链接：https://blog.youkuaiyun.com/knowwait/article/details/125672050

版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文主要讨论了MySQL binlog日志过大导致磁盘空间占用过多的问题。通过解析binlog文件并分析其内容，发现可能由于大表ALTER操作或接口重复调用引起。解决方法包括调整binlog格式为mixed模式以减少日志量，以及设置自动清理策略如expire_logs_days和max_binlog_size。此外，还提供了清理过大binlog文件的具体SQL命令。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql的binlog日志过大，占用磁盘空间太多

binlog文件

首先分析找到binlog文件解析后分析一下：

登录mysql查看binlog的位置，如果开启了binlog，log_bin为ON

show variables like '%log%';

下图为具体的binlog文件

解析binlog文件

binlog文件是二进制文件，无法直接查看，需要先进行解析

在mysql的安装目录bin下，使用mysqlbinlog命令，解析对应的binlog文件成对应的sql文件

解析命令：

./mysqlbinlog --no-defaults  /root/binlog/mysql-bin.030437 > /root/binlog/mysql_bin_030437.sql

出现问题：mysqlbinlog: unknown variable 'default-character-set=utf8'

原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令

mysqlbinlog: unknown variable 'default-character-set=utf8'

解决办法：

./mysqlbinlog --no-defaults -vv --base64-output=decode-rows /root/binlog/mysql-bin.030437 > /root/binlog/mysql_bin_030437.sql

分析sql

上述步骤我们将binlog解析成了sql，打开sql查看，如果更新sql激增需要考虑是否对大表数据进行了alter操作，或者接口出现重复调用等操作。

处理方法

如果sql文件的记录的sql是正常的接口或者功能批量操作sql，那需要对binlog配置进行查看。

1、需要查看my.cnf中配置的binlog模式

binlog的模式有三种：statement、row、mixed。

statment模式

基于sql语句的复制(statement-based replication, sbr)，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条sql语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘io，提高性能。

缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

row模式

不记录每一条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

mixed模式

混合模式复制(mixed-based replication, mbr)：以上两种模式的混合使用，一般的复制使用statement模式保存binlog，对于statement模式无法复制的操作使用row模式保存binlog，mysql会根据执行的sql语句选择日志保存方式。

一般情况下设置mixed模式即可：修改my.cnf

[mysqld]
#设置日志格式
binlog_format = mixed

2、需清理大binlog文件

登录mysql后，如果只有部分binlog过大，按照文件名称清理：

mysql>PURGE MASTER LOGS TO ‘mysql-bin.030435′;

如果整体binlog过大，可以按照日期清理，清理某个日期之前的日志：

purge master logs before '2022-06-26 18:00:00';

设置自动清理：修改my.cnf

[mysqld]
#Binary Log自动删除的天数
expire_logs_days = 7 

#binlog每个日志文件大小
max_binlog_size = 100m

#binlog缓存大小
binlog_cache_size = 4m

#最大binlog缓存大小
max_binlog_cache_size = 512m

重启重启 MySQL 服务