高级DBA带你解决MySql主从集群集群主库产生更多binlog引起数据无法正常写入引起生产事故紧急处理方法实战解决方法（全网唯一）

高级DBA带你解决MySql主备集群主库产生更多binlog引起数据无法正常写入引起生产事故紧急处理方法实战解决方法（全网唯一）

一、事故描述

早上刚来，监控报警，短信来了，业务主数据库宕机了，硬盘爆了，写入操作失败！有客户投诉了，新增订单报错，全世界炸锅！老板着急了就在旁边看着解决，DBA的我又来救火了！一个技术人员的工作日常，携程、淘宝、阿里云、包括微信包括我们的银行的业务系统交易转账其实都有过一些业务暂停的事故，真实情况是每次处理事故有一部分工程师们再背后救火解决！所以我希望社会尊重与感恩每一个开发工程师，整个社会的信息化运作正常背后都有无数的技术人员的付出与汗水维持着！

二、检查mysql运行日志

（1）日志位置

在这里插入图片描述

SHOW VARIABLES LIKE 'datadir';

在 MySQL 8 中，默认情况下错误日志的存放目录为数据目录，默认的日志文件名为hostname.err（其中hostname是主机名）。

要查看错误日志的位置，可以使用以下 SQL 语句：
在这里插入图片描述

SHOW VARIABLES LIKE 'log_error%';

二进制日志默认存放在数据目录下，文件名通常为mysql-bin.000001、mysql-bin.000002等形式。要查看二进制日志的相关信息，可使用如下 SQL 语句：

SHOW VARIABLES LIKE '%log_bin%';

慢查询日志默认也在数据目录下，文件名通常为hostname-slow.log。

请注意，这些默认路径可能会因安装方式和操作系统的不同而有所差异。如果 MySQL 进行了特殊配置，日志的存放目录可能会发生变化。最准确的方式是查看 MySQL 的配置文件（如my.cnf或my.ini）中与日志相关的配置项，以确定具体的日志路径。

另外，在 Linux 系统中，数据目录的常见默认位置是/var/lib/mysql；在 Windows 系统中，数据目录通常位于C:\ProgramData\MySQL\MySQL Server 8.0\data。但具体路径还是可能因安装和配置的不同而有所变化。

言归正传，翻看MYSQL运行日志如下：

2024-06-29T13:37:34.509217Z 1815645 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T13:47:34.553881Z 1815645 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... 2024-06-29T16:07:24.262173Z 1816136 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T16:17:24.307688Z 1816136 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T16:26:32.221467Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T16:36:32.267194Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T16:46:32.314193Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T16:56:32.359167Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T17:06:32.406056Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T17:16:32.451803Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T17:26:32.497897Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T17:36:32.543610Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T17:46:32.588789Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T17:56:32.633854Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T18:06:32.678581Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T18:16:32.725184Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T18:26:32.770824Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T18:36:32.815621Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.
2024-06-29T18:46:32.861446Z 1816356 [ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.009642' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.

在这里插入图片描述

二、检查MYSQL的数据文件夹

（1）通过SQL确定位置

在这里插入图片描述

SHOW VARIABLES LIKE 'datadir';

（2）通过MYSQL配置文件确定位置

MySQL 8 的配置文件my.ini通常存储在以下位置：

`C:\ProgramData\MySQL\MySQL Server 8.0\my.ini`  #windows默认
/etc/my.inf  #Linux服务器通常在

需要注意的是，ProgramData文件夹默认是隐藏的，需要通过文件资源管理器的“查看”选项卡中的“隐藏项目”选项将其显示出来。

另外，如果是使用 ZIP 压缩包安装的 MySQL 8，那么my.ini文件通常位于 MySQL 解压后的根目录下。

my.ini是 MySQL 的主要配置文件，用于配置数据库实例的各种参数。以下是一个my.ini文件的常见配置示例，其中包含了一些常用的参数及其说明：

[mysqld]
# 设置 3306 端口
port=3306
# 设置 MySQL 的安装目录
basedir=安装目录的实际路径
# 设置 MySQL 存储数据文件的目录
datadir=数据文件目录的实际路径
# 允许最大连接数
max_connections=200
# 允许连接失败的次数，防止有人试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集，默认为 UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
# 关闭 ssl
skip_ssl
# 配置时区
default-time_zone='+8:00'

[mysql]
# 设置 MySQL 客户端默认字符集
default-character-set=utf8

[client]
# 设置 MySQL 客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

在实际配置时，需要将上述示例中的“安装目录的实际路径”和“数据文件目录的实际路径”替换为你 MySQL 实际的安装目录和数据文件存储目录。修改my.ini文件后，需要重启 MySQL 服务才能使配置生效。

配置文件中的
datadir=数据文件目录的实际路径
定位

在这里插入图片描述

三、问题定位

mysql主库短时间内产生大量binlog引起硬盘爆满、
但是这个binlog 只保留特定的天数配置配置了，但是没有真正的起效！！

在这里插入图片描述
在 MySQL 8 中，您可以通过以下配置来设置 binlog 只保留特定的天数：

在 MySQL 的配置文件（如 my.cnf 或 my.ini）中，添加以下参数：

expire_logs_days = N

其中，N 表示要保留的天数。例如，如果您希望 binlog 只保留 7 天，可以设置为：

expire_logs_days = 7

这样，MySQL 会自动删除超过 N 天的 binlog 文件。

需要注意的是，修改配置文件后，需要重启 MySQL 服务才能使配置生效。

这个参数配置了1，按理来说这里配置1，MYSQL服务应该会自动删除过期的binlog文件，为啥会失效！没有起效！！傻眼了！先把生产恢复先，这个原因我再最后揭晓！

四、紧急解决方法步骤

操作步骤1

先删除一点没有用的日志，腾出来点空间，让mysql能正常运行一下！

操作步骤2

关闭mysql的主库binlog配置，将其关闭！
（1）查看binlog配置是否启用，这个肯定启用的，为了下个步骤确认是否真正关闭用到！
在这里插入图片描述
在 MySQL 8中，您可以通过以下几种方式查看 binlog 是否启用：

使用 SHOW VARIABLES LIKE 'log_bin'; 命令。如果 log_bin 的值为 ON，则表示 binlog 已启用；如果为 OFF，则表示未启用。

SHOW VARIABLES LIKE 'log_bin';

查看 MySQL 配置文件（如 my.cnf 或 my.ini）中是否设置了 log_bin 选项。如果在配置文件中有 log_bin = ON 或类似的设置，则表示 binlog 已启用。

例如，如果您的配置文件中有以下内容：

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log

则表示 binlog 已启用，并指定了 binlog 文件的存储位置。

通过以上方法，您可以确定 MySQL 8 中 binlog 是否启用。

（2）操作配置文件

将配置文件里面
在 MySQL 的配置文件（如 my.cnf 或 my.ini）中，添加以下参数：
加上

skip-log-bin

再把

log_bin 注释 改成#log_bin = /var/log/mysql/mysql-bin.log 或者删除这个配置都行

这2个步骤是把binlog重置，如果不按我这种操作，你先删除了binlog文件释放硬盘，下次重启MYSQL服务，就会服务报错起不来，找不到那个binlog文件，一大堆binlog错误！

（3）重启mysql服务

在 Linux 系统中，要重启 MySQL 8 服务，可以使用以下命令：

使用 systemctl 命令（适用于使用 Systemd 管理服务的系统）：
```
sudo systemctl restart mysqld
```
使用 service 命令（较旧的方式，某些系统可能不支持）：
```
sudo service mysqld restart
```

如果重启操作，等待许久没有反应，kill -9 mysql服务进程，再重启！

（4）重启成功之后确认mysql服务binlog已经关了

在这里插入图片描述
OFF代表关了，关了才能删除那些binlog文件！

操作步骤3 删除binlog文件释放硬盘空间，恢复生产

现在删除操作,现在可以大胆的删除binlog操作，因为上面的步骤已经重置binlog！
在这里插入图片描述

rm -rf mysql-bin.00*

删除之后，服务器的硬盘空间瞬间释放处理，先把业务配置都改成主库，让业务恢复正常！
但是主从集群暂时关闭，等晚上周末的时候重新配置主从同步！

五、事故原因总结并重新主从集群并更换关键的超时自动删除参数

在 MySQL 8 中，expire_logs_days 参数已被废弃，取而代之的是 binlog_expire_logs_seconds 参数，用于控制 binlog 文件的过期时间（以秒为单位）。

要设置 binlog 文件的过期时间，可以使用以下 SQL 语句：

SET GLOBAL binlog_expire_logs_seconds = 你期望的过期时间（以秒计）;

例如，要设置 binlog 文件在 7 天后过期（7 天 = 604800 秒），可以执行：

SET GLOBAL binlog_expire_logs_seconds = 604800;

binlog 自动清理的触发时机为 MySQL 启动时和 binlog 刷新时。binlog 刷新又分两种情况：

当前的 binlog 大小达到 max_binlog_size，写完当前事务的日志后，就会生成一个新的 binlog。
手工执行了 flush binary logs 命令后，也会生成一个新的 binlog。

请注意，修改全局参数后，仅在当前会话中生效。如果希望重启 MySQL 后仍然生效，可以将该参数添加到 MySQL 的配置文件（如 my.cnf 或 my.ini）中。

另外，不要轻易手动删除 binlog 文件，这可能会导致 binlog.index 和真实存在的 binlog 不匹配，从而使自动删除失效。如果遇到 binlog 没有自动删除的问题，还可以检查一下是否存在其他因素影响，例如是否使用了物理热备工具，以及是否受到备份锁的影响等。

同时，从 MySQL 8.0.29 开始，可以通过将 binlog_expire_logs_auto_purge 系统变量设置为 OFF 来禁用二进制日志的自动清除，这优先于 binlog_expire_logs_seconds 的设置。在 MySQL 8.0.28 及更早的版本中，要禁用自动清除二进制日志，需要显式地为 binlog_expire_logs_seconds 指定值 0，并且不要为 expire_logs_days 指定值。为了与早期版本兼容，如果显式地为 expire_logs_days 指定值为 0，而不为 binlog_expire_logs_seconds 指定值，自动清除也将被禁用，并且不应用 binlog_expire_logs_seconds 的默认值。

若要手动删除二进制日志文件，可以使用 PURGE BINARY LOGS 语句。例如：

清除某个日志：PURGE BINARY LOGS TO 'binlog.000011';
清除某日前的日志：PURGE BINARY LOGS BEFORE '2024-07-01';

我总结一下，因为mysql升级到8.0.33（大于MySQL 8.0.29）之后，expire_logs_days 参数直接弃用了，导致binlog的超时自动删除的操作失效了！

在这里插入图片描述

生产环境从MySQL 8.0.26升级至8.0.33，之前配置的expire_logs_days是好使的，但是升级了高版本之后这个配置失效了，需要配置binlog_expire_logs_seconds的值取代，坑不吭！我们重新配置主从的时候，需要配置binlog_expire_logs_seconds的值，问题就解决了！！因为从低版本升级到高版本过程中，并不知道这个参数更改了！

重新配置步骤主备集群关键步骤：

（1）新增binlog_expire_logs_seconds关键参数

1小时有60分钟，1分钟有60秒，所以1小时有 $60\times60 = 3600$ 秒。

那么24小时的秒数为： $24\times3600 = 86400$ 秒

在 MySQL 8 中，您可以通过以下配置来设置 binlog 只保留特定的天数：

在 MySQL 的配置文件（如 my.cnf 或 my.ini）中，添加以下参数：

binlog_expire_logs_seconds= 86400  #配置个24小时超时时间

（2）删除或注释skip-log-bin配置并更换新的binlog文件名
因为重新打开主从，因为上次爆满的binlog已经清空完了！

   #skip-log-bin  #注释
   log_bin = /var/log/mysql/mysql-bin-new.log  #更换新的binlog文件名

(3)重启mysql服务
sudo systemctl restart mysqld

在这里插入图片描述
确认一下参数是否配置正常！

SHOW VARIABLES LIKE '%binlog_expire_logs_seconds';
SHOW VARIABLES LIKE 'log_bin';

（4）重新配置主从集群，恢复之前的读写分离架构
恢复之前的生产架构，问题最终解决！

六、总结

Mysql升级版本某些参数有巨大变化，导致原本的自动清理binlog操作失效，导致重大生产事故！
随着mysql不断升级，漏洞修补的过程中，会重置一些关键参数！这个只能说大家注意引以为戒吧！笔者用到这个问题，所有的mysql升级大版本都会遇到一样的问题，希望能帮助到大家，提前预警，解决的快一点！减少事故的影响，保证数据的安全！

七、笔者简介

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

在这里插入图片描述

笔者想说几句额外的：
某程删库事故的巨大影响值得警醒！！当然笔者在文章中惊提醒某些企业高管资本家，如果公司对一线的运维、DBA不够尊重、重视！如果裁员涉及到DBA或者高级运维，那将会是灾难噩梦的开始，如果因为裁员裁到大动脉，核心数据丢失事故给企业、产品、社会的影响巨大损失无法估量！某程删库的事故损失情况，可以去网上查查！核心数据丢失了，老板、企业家们高管很可能赔满全部身价！所以笔者倡导作为企业家、高管对于一线的运维、DBA要给予更多的爱与尊重与认可！愿社会更美好，愿每一个一线工程师都获得尊重与认可！