记一次MySQL binlog日志导致磁盘空间占满的问题

 
背景

某开发人员反馈，一个MySQL测试环境的数据库服务器，磁盘空间被占满，并且明确告知MySQL数据库并不大，但是其binlog日志占用数百GB的空间，远远超出预期的大小，要协助检查为什么binlog会占用如此大的空间。
简言之就是：数据量较小，binlog的日志量很大。
binlog相关的配置信息

查看MySQL binlog相关的参数，
1，binlog_expire_logs_auto_purge是打开的，MySQL会自动清理过期的日志，这一点没有问题。
2，对于max_binlog_size为1G，binlog_expire_logs_seconds为30天，也就是单个binlog最大为1G，binlog保留时间为30天。

1. SELECT
2.   variable_name,
3.   variable_value
4. FROM performance_schema.global_variables
5. WHERE variable_name IN (
6.   'log_bin',
7.   'binlog_format',
8.   'max_binlog_size',
9.   'binlog_expire_logs_seconds',
10.   'binlog_expire_logs_auto_purge',
11.   'binlog_row_image',
12.   'binlog_row_metadata'
13. );
16. |variable_name                      |variable_value |
17. | binlog_expire_logs_auto_purge     | ON |
18. | binlog_expire_logs_seconds        | 2592000 |
19. | binlog_format                     | ROW |
20. | binlog_row_image                  | FULL |
21. | binlog_row_metadata               | MINIMAL |
22. | log_bin                           | ON |
23. | max_binlog_size                   | 1073741824 |

复制代码

binlog文件查看

上面的参数可以知道，binlog自动清理选项打开了，那么就直接分析已有binlog的内容，这里先查看binlog文件信息，以及活动binlog中记录到的操作类型。

1. SHOW BINARY LOGS;
2. Log_name;File_size;Encrypted
3. ……
4. binlog.000019;1073742747;No
5. binlog.000020;1073742747;No
6. binlog.000021;1073742747;No
7. binlog.000022;1073742747;No
8. binlog.000023;1073743263;No
9. binlog.000024;189129008;No
12. SHOW BINLOG EVENTS IN 'binlog.000024' LIMIT 10000;

复制代码

从binlog中的Event类型可以看到，对于数据库中的某一张表，有大量的insert操作（write_rows）和delete操作（delete_rows）

show binlog events只能粗略看到binlog中的操作的表以及对应的操作类型，无法查看其详细的操作信息或者说对应的SQL语句，因此只能通过mysqlbinlog命令来解析出来binlog来查看其具体的SQL语句信息。
mysqlbinlog工具的使用

mysqlbinlog 是 MySQL 官方自带的二进制日志（binlog）解析与回放工具，主要用于查看、分析、恢复、重放 MySQL 的 binlog。
一句话总结：它可以把MySQL的二进制日志翻译成人能看懂的文本格式，用以分析二进制日志的内容；也能生成可执行的SQL用于回放二进制日志；还可以直接将binlog的内容直接重放，用以恢复数据库。
mysqlbinlog典型的用法用下：

1. mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000022 >binlog.000022.sql

复制代码

--no-defaults
忽略所有默认配置文件，只使用命令行参数解析命令行中的binlog；或者实现离线解析当前的binlog
--database=xxx
数据库过滤，示例如下，增加database参数之后会给出一个警告，
mysqlbinlog --no-defaults --database=xxx--base64-output=decode-rows -v binlog.000024 >binlog.000024.sql
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.

--base64-output=decode-rows
目的是过滤掉二进制数据，如果不加--base64-output=decode-rows，则翻译为类似于 BINLOG '459AaRMBAAAAPgAAAAgHAAAAAFMAAAAAAAEAA3R0ZAAHbG9nX2RwZQAEAw/8EgSHAAIAAAEBAAIB'的文本。这部分文本信息只是用以恢复数据库，并不适合于用户的查看，所以进查看日志的时候，可以过--base64-output=decode-rows过滤掉这部分信息。
需要注意的是：
如果只是想查看binlog中的SQL语句，可以加上--base64-output=decode-rows，
如果是想利用到处的sql文件恢复数据，那么一定不能指定--base64-output=decode-rows，因为加上--base64-output=decode-rows之后，不会解析出真正用于恢复数据的。
-vv（verbose）
显示具体的SQL语句，通常是-vv或者-vvv，-vvv是更加详细的SQL语句，绝大多数情况下用-vv就足够了。

position
用于指定具体的binlog位点信息来筛选部分binlog，除非binlog非常大，或者非常清楚相关数据的位点，增加此参数来过滤binlog的导出，一般不用该参数做筛选操作
--start-position=POS
--stop-position=POS

datetime
用于指定具体的binlog事务时间点信息来筛选部分binlog，除非binlog非常大，或者非常清楚相关数据的位点，增加此参数来过滤binlog的导出，一般不用该参数做筛选操作
--start-datetime="YYYY-MM-DD HH:MM:SS"
--stop-datetime="YYYY-MM-DD HH:MM:SS"

mysqlbinlog常用导出方式

1，mysqlbinlog --no-defaults  -vv binlog.000024 >binlog.000024.sql
该场景下，导出的sql文件格式参考如下，既不会破坏binlog的可恢复性，也能看到具体的SQL语句

2，mysqlbinlog --no-defaults --base64-output=decode-rows -v binlog.000022 >binlog.000022.sql
该场景下，通过--base64-output=decode-rows筛选掉二进制内容，内容更简洁，能看到具体的SQL语句，但是导出后的sql文件不可用于数据恢复操作。

mysqlbinlog分析binlog

由于只是分析binlog中的内容，而不是用以恢复数据库，因此才使用上述第二种方式导出相关的binlog成sql文件。

mysqlbinlog --no-defaults --base64-output=decode-rows -v binlog.000022 >binlog.000022.sql
结果上述show binlog event中显示的内容，发现有一个日志表，会频繁写入数据，每秒钟数百行，每天可达千万行，同事会在凌晨某个时间点通过MySQL的Event定时任务来删除最早的日志。
表面上看，整个数据库并不大，但是应用程序在写入数据的时候，会生成binlog，定时任务在删除数据的时候同样会生成大量的binlog，同时binlog的保留期限为30天，这样就会造成服务器上挤压大量的binlog，使得binlog占用的空间远远超出数据库自身的空间。

很多时候，可能会潜在一个误区，明明数据库并不大，为什么会产生大量的binlog，其实数据库本身记录的是存量数据，而binlog记录的是增删改操作本身，如果频繁第写入和删除，即便是存量数据并不大，但也会生成大量的binlog。
 
补充：利用binlog恢复数据库

附带利用binlog恢复数据库的两种可选方案，强烈建议使用方式1，也就是人工确认binlog的具体内容符合预期之后再恢复，尤其是生产环境。

1. 基于binlog的数据恢复
3. ###方案1：
4. 1，完整备份恢复
5. 2，在完整备份恢复的基础上，利用将binlog导出为sql文件，人工确认数据范围是否符合预期，然后再进行恢复
6. -- binlog 导出sql
7. mysqlbinlog --no-defaults -vv --start-datetime="2025-12-15 00:00:00"  --stop-datetime="2025-12-15 07:05:40" binlog.000022 >binlog.000022.sql
9. --登录MySQL后，用source命令，从sql文件中恢复
10. mysql -u root -p -h 127.0.0.1 -P 3307
11. source /usr/local/binlog.000022.sql
15. ###方案2：
16. 1，完整备份恢复
17. 2，在完整备份恢复的基础上，利用binlog进行回复
18. mysqlbinlog --no-defaults  --start-datetime="2025-12-15 00:00:00"  --stop-datetime="2025-12-15 07:05:40"    --skip-gtids --disable-log-bin binlog.000022 | mysql -uroot -p

复制代码

 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！