博客 / 詳情

返回

記一次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天。 

SELECT
  variable_name,
  variable_value
FROM performance_schema.global_variables
WHERE variable_name IN (
  'log_bin',
  'binlog_format',
  'max_binlog_size',
  'binlog_expire_logs_seconds',
  'binlog_expire_logs_auto_purge',
  'binlog_row_image',
  'binlog_row_metadata'
);


|variable_name                      |variable_value |
| binlog_expire_logs_auto_purge     | ON |
| binlog_expire_logs_seconds        | 2592000 |
| binlog_format                     | ROW |
| binlog_row_image                  | FULL |
| binlog_row_metadata               | MINIMAL |
| log_bin                           | ON |
| max_binlog_size                   | 1073741824 |

binlog文件查看

上面的參數可以知道，binlog自動清理選項打開了，那麼就直接分析已有binlog的內容，這裏先查看binlog文件信息，以及活動binlog中記錄到的操作類型。

SHOW BINARY LOGS;
Log_name;File_size;Encrypted
……
binlog.000019;1073742747;No
binlog.000020;1073742747;No
binlog.000021;1073742747;No
binlog.000022;1073742747;No
binlog.000023;1073743263;No
binlog.000024;189129008;No


SHOW BINLOG EVENTS IN 'binlog.000024' LIMIT 10000;

從binlog中的Event類型可以看到，對於數據庫中的某一張表，有大量的insert操作（write_rows）和delete操作（delete_rows）

image

show binlog events只能粗略看到binlog中的操作的表以及對應的操作類型，無法查看其詳細的操作信息或者説對應的SQL語句，因此只能通過mysqlbinlog命令來解析出來binlog來查看其具體的SQL語句信息。

mysqlbinlog工具的使用

mysqlbinlog 是 MySQL 官方自帶的二進制日誌（binlog）解析與回放工具，主要用於查看、分析、恢復、重放 MySQL 的 binlog。
一句話總結：它可以把MySQL的二進制日誌翻譯成人能看懂的文本格式，用以分析二進制日誌的內容；也能生成可執行的SQL用於回放二進制日誌；還可以直接將binlog的內容直接重放，用以恢復數據庫。
mysqlbinlog典型的用法用下： 

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語句
image

2，mysqlbinlog --no-defaults --base64-output=decode-rows -v binlog.000022 >binlog.000022.sql
該場景下，通過--base64-output=decode-rows篩選掉二進制內容，內容更簡潔，能看到具體的SQL語句，但是導出後的sql文件不可用於數據恢復操作。

image

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佔用的空間遠遠超出數據庫自身的空間。

image

很多時候，可能會潛在一個誤區，明明數據庫並不大，為什麼會產生大量的binlog，其實數據庫本身記錄的是存量數據，而binlog記錄的是增刪改操作本身，如果頻繁第寫入和刪除，即便是存量數據並不大，但也會生成大量的binlog。

 

補充：利用binlog恢復數據庫

附帶利用binlog恢復數據庫的兩種可選方案，強烈建議使用方式1，也就是人工確認binlog的具體內容符合預期之後再恢復，尤其是生產環境。

基於binlog的數據恢復

###方案1：
1，完整備份恢復
2，在完整備份恢復的基礎上，利用將binlog導出為sql文件，人工確認數據範圍是否符合預期，然後再進行恢復
-- binlog 導出sql
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

--登錄MySQL後，用source命令，從sql文件中恢復
mysql -u root -p -h 127.0.0.1 -P 3307
source /usr/local/binlog.000022.sql



###方案2：
1，完整備份恢復
2，在完整備份恢復的基礎上，利用binlog進行回覆
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

 

數據庫
user avatar
0 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.