高级DBA带你解决Mysql主从集群主库产生过多binlog文件引起生产服务器硬盘爆满处理方法实战全网唯一

高级DBA带你解决Mysql主从集群产生过多binlog文件引起生产服务器硬盘爆满处理方法实战全网唯一

一、事故描述

生产环境数据库服务器突然硬盘爆满报警，业务停止，监控短信过来了，一看硬盘满了，再看数据库文件路径一大堆binlog文件直接把服务器硬盘挤满！

看到这里我已经知道问题所在了，当然还是给大家带一点Mysql官方的日志提示！
如果用到mysql主从集群，这个时候也把从库的主从同步关掉吧，主库都不正常了，主从同步肯定也不正常！
去从库执行stop slave;

二、Mysql运行日志报警信息汇总

MySQL报警日志如下：

Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.


2024-04-03T04:52:18.055879Z 537 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.002570' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-04-03T05:02:18.101641Z 537 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.002570' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.

OS errno 28 - No space left on device
这个英语提示已经很明显了，告诉你硬盘满了！
表数据不能写入，binlog日志不能写入！

三、通用解决方法

核心思路就是清理binlog日志，这个思路！

（一）配置减小binlog的超时时间间隔

查看当前binlog超时配置

show variables like '%expire%';

binlog_expire_logs_seconds配置与expire_logs_days配置是互斥，2个配置只能配置1个！
新版本用的是binlog_expire_logs_seconds配置，老版本用的是expire_logs_days！这个自己判断！

把这2个值的其中一个值改小！比如之前7天改成1天，之前2天总秒数改成1天的总秒数！

这个expire_logs_days值之前是7，我们改成1！
在 MySQL 8.0中，使用binlog_expire_logs_seconds设置日志过期时间，废弃参数：expire_logs_days。其默认设置的binlog过期时间是30天。

你可以通过执行以下命令来查看当前的binlog_expire_logs_seconds值：

show variables like '%binlog_expire_logs_seconds%';

如果你需要修改binlog的最大超时时间，可以通过以下方式进行设置：

• 方式一：在配置文件中设置binlog过期时间：

  expire_logs_days=1  #/etc/my.cnf 改配置文件需要重启mysql服务，配置文件从7改成1，避免重启失效！
  

set global expire_logs_days=1;  #临时改，不需要重启MYSQL服务器，重启之后会读取配置！由之前7改成1

• 方式二：使用命令行设置binlog过期时间：

  set global binlog_expire_logs_seconds=1*24*60*60;#临时改，不需要重启MYSQL服务器！重启之后会读取配置
   set global binlog_expire_logs_seconds=86400   #由之前的2592000改小成86400的值
  

  上述命令中，将binlog_expire_logs_seconds设置为2*24*60*60，即表示设置binlog的过期时间为72小时。你可以根据自己的需求来设置相应的时间。

设置完成后，你需要刷新logs以使更改生效：

flush logs;        #生效配置

请注意，修改binlog的最大超时时间可能会影响数据库的性能，因此请谨慎操作。在进行任何更改之前，请务必备份重要的数据和数据库配置。

配置文件/etc/my.cnf也同步改一下！避免重启之后失效！修改完成之后！再核实一下！

show variables like '%expire%';

（二）人工按时间段清理binlog日志文件

上一个步骤配置已经改好，比如配置1天，我们就把1天之前的binlog清理掉！可以多预留1天，尽量别删多了

purge binary logs before '2024-04-01';  #4月2日之前binlog文件清理掉

purge binary logs before '2024-04-01 22:00:00';  #新版本到秒，旧版本只到日！

比如今天是4月3日，配置1天超时，只保留4月1日0点之后的binlog文件！
删除的时候按你配置的超时时间预留一下！

删除完之后看一下binlog状态

show binary logs;  #删除完之后看一下binlog状态SQL

删除完成之后，再看硬盘空间，硬盘释放！问题解决！

df -h   #linux 命令

确定硬盘释放，问题解决！

如果用到主从集群，把主从同步重启，如果还是失败，主从库数据同步一下，主从同步配置重新配置！

（三）额外解决方法（直接关闭binlog,直接清空binlog日志文件）不推荐

步骤1关闭binlog

如果没有配置主从集群，可以直接将主库的binlog关掉！你是开发机器，或者是从库非主库！或者没有用到主从同步集群！
配置文件
my.cnf加skip-log-bin

在 MySQL 中，二进制日志（Binary Log）是用来记录所有对数据库进行更改的日志。它包含了所有增删改操作的详细信息，这些信息可以用于数据的恢复、备份以及数据库的同步等操作。MySQL 提供了一个参数skip-log-bin，可以用来关闭二进制日志的记录。

skip-log-bin的作用为：MySQL 的二进制日志通常用于主从复制、数据恢复、数据库同步等场景。然而，在某些特定的场景下，可能希望跳过二进制日志的记录。常见的场景包括：

• 性能优化：在某些高性能的应用场景下，由于二进制日志的记录会增加一定的系统开销，可能希望关闭二进制日志来提高性能。
• 特定问题的避免：有些特定的问题可能与二进制日志的记录相关，因此可以通过关闭二进制日志来避免这些问题的发生。

要使用 skip-log-bin参数，需要将其添加到 MySQL 的配置文件中（通常是 my.cnf 或者 my.ini ）。下面是一个示例的配置文件：

[mysqld] skip-log-bin

将上述配置保存在 my.cnf 文件中，然后重启 MySQL 服务即可生效。需要注意的是，在使用 skip-log-bin 参数时，需要注意以下几点：

• 数据一致性：关闭二进制日志可能会导致主从复制、数据恢复等功能无法正常工作，因此在关闭二进制日志之前，需要确保没有依赖二进制日志的相关功能。
• 安全性：二进制日志可以用于数据的备份和恢复，因此关闭二进制日志可能会对数据的安全性产生一定的影响。在关闭二进制日志之前，需要评估其对数据安全性的影响。
• 性能影响：关闭二进制日志可以提高系统的性能，但同时也会导致数据的灾难恢复能力下降。在关闭二进制日志之前，需要权衡性能和数据恢复能力之间的平衡。

步骤2重启MySQL服务，并且确认binlog已经关闭

SHOW VARIABLES LIKE 'log_bin';  #查看是否开启binlog

笔者是开发机所以直接关了，或者是主从同步的从节点，也可以直接关掉，节省硬盘空间！

步骤3人工删除binlog的历史文件释放硬盘（已经关闭配置之后再执行）

进入到mysql的文件路径下

rm -rf mysql-bin*   #直接删除之前产生的binlog日志

你也可以一个一个删除，或者一批一批删除日志，因为已经提前关闭了binlog，所以删除是没有影响的！如果你没有关闭配置，删除文件，会导致mysql出现错误，重启服务会报错找不到binlog文件，会引起报错不能正常启动！

四、mysql binlog概念介绍

MySQL 的二进制日志（Binary Log，也称为 binlog）是 MySQL 服务器用于记录数据库操作的日志文件。它用于实现主从复制、数据备份和恢复等功能。

二进制日志以二进制格式记录了对 MySQL 数据库执行的各种操作，例如插入、更新和删除数据等。每条日志记录包含了关于操作的详细信息，包括操作的类型、涉及的表、执行的用户等。

二进制日志的主要用途包括：

1. 主从复制：主服务器将二进制日志传输到从服务器，从服务器根据日志记录执行相同的操作，从而实现数据的复制和同步。
2. 数据恢复：通过二进制日志，可以将数据库恢复到特定的时间点或执行特定的操作，以进行数据恢复。
3. 审计和监控：二进制日志可以用于审计数据库操作，追踪用户活动和检测潜在的安全问题。

在 MySQL 中，可以通过配置来启用二进制日志，并设置日志的存储位置、格式和其他相关参数。常见的二进制日志格式包括基于语句的日志（STATEMENT）和基于行的日志（ROW）。

了解和管理二进制日志对于确保数据库的可靠性、安全性和可扩展性非常重要。它提供了一种有效的方式来跟踪和复制数据库操作，以及进行数据恢复和故障排除。具体的使用和配置方法可以参考 MySQL 的官方文档和相关资料。

再介绍一下MySQL 的binlog有三种模式

MySQL 的binlog有三种模式：ROW模式、STATEMENT模式和MIXED模式。这三种模式的区别如下：

• ROW模式：
  • 优点：在ROW模式下，bin-log中可以不记录执行的SQL语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以ROW的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和触发无法被正确复制问题。
  • 缺点：在ROW模式下，所有的执行语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
• STATEMENT模式：
  • 优点：并不需要记录每一条SQL语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。
  • 缺点：在某些情况下会导致master-slave中的数据不一致（如sleep()函数、last_insert_id()，以及user-defined functions(udf)等会出现问题）。
• MIXED模式：是以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL 会根据执行的SQL语句选择日志保存方式。

如需配置 MySQL 的binlog模式，可以在 MySQL 的配置文件中添加参数binlog_format，参数值设置为对应的模式即可，如需配置ROW模式，可将参数值设置为ROW。

通常需要保证主从数据一致性，默认row模式！把超时日志时间配置短一点！

五、问题总结

还是binlog配置最大值没有提前配置好，这个以后大家提前注意配置好，因为增量数据是有限的，当然瞬间爆满的也是存在的，这个涉及到字段的频繁更新，UPDATE的范围涉及到row模式（常见的二进制日志格式包括基于语句的日志（STATEMENT）和基于行的日志（ROW））！
笔者还遇到主从同步中从库relaylog爆满的问题，这个文章参考我另一篇文章：

https://blog.csdn.net/nasen512/article/details/136641303

笔者简介
国内某一线知名软件公司企业认证在职员工：任JAVA高级研发工程师,大数据领域专家,数据库领域专家兼任高级DBA!10年软件开发经验！现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA，软件架构师。直接参与设计国家级亿级别大数据项目!并维护真实企业级生产数据库300余个!紧急处理数据库生产事故上百起，挽回数据丢失所造成的灾难损失不计其数!并为某国家级大数据系统的技术方案（国家知识产权局颁布）专利权的第一专利发明人！