分析MySql的binlog

2022-09-09 12:09:44

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 服务

  • 作者:knowwait
  • 原文链接:https://blog.csdn.net/knowwait/article/details/125672050
    更新时间:2022-09-09 12:09:44