背景與問(wèn)題

運(yùn)維工程師經(jīng)常會(huì)遇到這樣的場(chǎng)景：MySQL 服務(wù)器的磁盤空間告警，但查看數(shù)據(jù)目錄時(shí)發(fā)現(xiàn)數(shù)據(jù)庫(kù)本身并不大。大量磁盤空間被未知文件消耗。通過(guò)排查發(fā)現(xiàn)，二進(jìn)制日志（Binary Log）是主要的磁盤空間消耗者。

二進(jìn)制日志是 MySQL 復(fù)制和數(shù)據(jù)恢復(fù)的核心組件，但如果管理不當(dāng)，它會(huì)迅速占滿磁盤空間。本文系統(tǒng)講解二進(jìn)制日志的工作原理、磁盤空間問(wèn)題的成因、排查方法以及最佳實(shí)踐。

本文基于 MySQL 8.0.36 版本，操作系統(tǒng)為 Ubuntu 24.04 LTS。所有命令和配置均經(jīng)過(guò)實(shí)際環(huán)境驗(yàn)證。

一、二進(jìn)制日志基礎(chǔ)

1.1 二進(jìn)制日志的作用

二進(jìn)制日志記錄了所有修改數(shù)據(jù)庫(kù)數(shù)據(jù)的操作，包括：

數(shù)據(jù)修改語(yǔ)句：INSERT、UPDATE、DELETE。

數(shù)據(jù)定義語(yǔ)句：CREATE、ALTER、DROP（可配置）。

服務(wù)器運(yùn)行時(shí)事件：如主從復(fù)制中的心跳事件。

二進(jìn)制日志的主要用途：

主從復(fù)制：從服務(wù)器通過(guò)讀取主服務(wù)器的二進(jìn)制日志來(lái)同步數(shù)據(jù)。

數(shù)據(jù)恢復(fù)：使用 mysqlbinlog 工具可以恢復(fù)指定時(shí)間點(diǎn)的數(shù)據(jù)。

增量備份：結(jié)合全量備份和二進(jìn)制日志實(shí)現(xiàn)增量備份。

審計(jì)：可以用于審計(jì)數(shù)據(jù)庫(kù)的變更歷史。

1.2 二進(jìn)制日志的文件結(jié)構(gòu)

二進(jìn)制日志文件名格式：mysql-bin.000001、mysql-bin.000002等。

# 查看二進(jìn)制日志目錄
ls -lh /var/log/mysql/

# 二進(jìn)制日志文件列表
mysql -u root -p -e"SHOW BINARY LOGS;"

# 當(dāng)前使用的二進(jìn)制日志
mysql -u root -p -e"SHOW MASTER STATUS;"

每個(gè)二進(jìn)制日志文件包含多個(gè)日志事件（Event），日志事件分為幾種類型：

Format_description：格式描述事件，記錄 MySQL 版本信息。

Table_map：表映射事件，記錄操作涉及的表。

Write_rows/Update_rows/Delete_rows：行事件，記錄實(shí)際的數(shù)據(jù)變更。

Xid：事務(wù)提交事件。

1.3 二進(jìn)制日志格式

MySQL 8.0 支持三種二進(jìn)制日志格式：

ROW：記錄行的變更，完整且安全，但日志量較大。

STATEMENT：記錄 SQL 語(yǔ)句，節(jié)省空間，但可能有不確定結(jié)果。

MIXED：混合模式，默認(rèn)使用 STATEMENT，在不確定結(jié)果時(shí)切換到 ROW。

-- 查看當(dāng)前二進(jìn)制日志格式
SHOWVARIABLESLIKE'binlog_format';
-- +---------------+-------+
-- | Variable_name | Value |
-- +---------------+-------+
-- | binlog_format | ROW  |
-- +---------------+-------+

-- 設(shè)置二進(jìn)制日志格式（臨時(shí)生效）
SETGLOBALbinlog_format ='ROW';

-- 永久生效需要在配置文件中設(shè)置
-- binlog_format = ROW

二、二進(jìn)制日志磁盤空間問(wèn)題

2.1 問(wèn)題場(chǎng)景描述

某生產(chǎn)環(huán)境 MySQL 服務(wù)器磁盤空間告警，/var/lib/mysql 目錄占用 500GB，但實(shí)際數(shù)據(jù)只有 80GB。

排查后發(fā)現(xiàn)，二進(jìn)制日志占用超過(guò) 400GB，這些日志包含了過(guò)去半年的所有數(shù)據(jù)變更記錄。

這是一個(gè)典型案例：沒有配置二進(jìn)制日志清理策略，導(dǎo)致日志無(wú)限增長(zhǎng)。

2.2 二進(jìn)制日志增長(zhǎng)速度分析

二進(jìn)制日志的增長(zhǎng)速度取決于以下因素：

業(yè)務(wù)負(fù)載：寫操作越頻繁，日志增長(zhǎng)越快。

日志格式：ROW 格式比 STATEMENT 格式日志量更大。

表的數(shù)據(jù)量：大表的小更新在 ROW 格式下也會(huì)產(chǎn)生大量日志。

-- 查看當(dāng)前二進(jìn)制日志總大小
SELECT
 SUM(FILE_SIZE) /1024/1024/1024AStotal_size_gb
FROMinformation_schema.FILES
WHEREFILE_TYPE ='BINARY LOG';

-- 查看每個(gè)二進(jìn)制日志文件的大小
SELECT
  LOG_NAMEASfile_name,
  FILE_SIZE /1024/1024ASsize_mb
FROMinformation_schema.FILES
WHEREFILE_TYPE ='BINARY LOG'
ORDERBYLOG_NAME;

# 也可以直接查看文件大小
ls -lh /var/log/mysql/mysql-bin.* | tail -20
du -sh /var/log/mysql/

2.3 二進(jìn)制日志清理機(jī)制

MySQL 提供兩種二進(jìn)制日志清理機(jī)制：

自動(dòng)清理：通過(guò) expire_logs_days 參數(shù)設(shè)置。

手動(dòng)清理：通過(guò) PURGE BINARY LOGS 命令刪除。

-- 查看自動(dòng)清理配置
SHOWVARIABLESLIKE'expire_logs_days';
-- 默認(rèn)值是 0，表示不自動(dòng)清理

-- 設(shè)置自動(dòng)清理（保留最近 7 天）
SETGLOBALexpire_logs_days =7;

-- 永久生效需要在配置文件中設(shè)置
-- expire_logs_days = 7

2.4 手動(dòng)清理二進(jìn)制日志

-- 刪除指定時(shí)間之前的日志
PURGEBINARYLOGSBEFORE'2026-01-01 0000';

-- 刪除指定日志文件之前的所有日志
PURGEBINARYLOGSTO'mysql-bin.000100';

-- 查看當(dāng)前日志文件
SHOWMASTERSTATUS;

清理時(shí)需要注意：

不要?jiǎng)h除還沒被從服務(wù)器讀取的日志，否則從服務(wù)器會(huì)中斷復(fù)制。

先在主服務(wù)器上執(zhí)行 SHOW SLAVE STATUS，確認(rèn)從服務(wù)器已經(jīng)讀取到要?jiǎng)h除的位置。

-- 檢查從服務(wù)器狀態(tài)
SHOWSLAVESTATUSG
-- 查看 Master_Log_File 和 Relay_Master_Log_File 字段
-- 確保主服務(wù)器上要?jiǎng)h除的日志文件名在這些字段之后

三、磁盤空間問(wèn)題排查步驟

3.1 初步診斷

# 查看磁盤使用情況
df -h

# 查看 MySQL 數(shù)據(jù)目錄占用
du -sh /var/lib/mysql/*

# 查看 MySQL 日志目錄
du -sh /var/log/mysql/*

3.2 二進(jìn)制日志占用分析

-- 查看所有二進(jìn)制日志文件及大小
mysql -u root -p -e "
SELECT
  LOG_NAME,
  FILE_SIZE /1024/1024ASsize_mb,
  (SELECTCOUNT(*)FROMinformation_schema.FILES)AStotal_files
FROMinformation_schema.FILES
WHEREFILE_TYPE ='BINARY LOG'
ORDERBYLOG_NAME;
"

-- 統(tǒng)計(jì)總大小
mysql -u root -p -e "
SELECT
 COUNT(*)AStotal_files,
 SUM(FILE_SIZE) /1024/1024/1024AStotal_size_gb
FROMinformation_schema.FILES
WHEREFILE_TYPE ='BINARY LOG';
"

3.3 查找磁盤空間消耗源頭

如果 df 顯示磁盤占用很高但 du 顯示文件很小，可能是以下問(wèn)題：

打開的文件句柄未釋放

MySQL 臨時(shí)文件未刪除

系統(tǒng)日志文件過(guò)大

# 查看 MySQL 進(jìn)程打開的文件
sudo lsof -p $(pgrep mysqld) | grep -E"REG|DIR"| awk'{print $NF}'| sort | uniq | xargs -I {} ls -lh {} 2>/dev/null

# 查看 MySQL 臨時(shí)目錄
SHOW VARIABLES LIKE'tmpdir';

# 查看臨時(shí)表使用情況
SHOW STATUS LIKE'Created_tmp%';

四、復(fù)制環(huán)境中的特殊處理

4.1 主從復(fù)制架構(gòu)概述

在主從復(fù)制環(huán)境中，二進(jìn)制日志的管理需要格外謹(jǐn)慎：

主服務(wù)器：必須開啟二進(jìn)制日志，記錄所有變更。

從服務(wù)器：通過(guò) I/O 線程讀取主服務(wù)器的二進(jìn)制日志，寫入本地 relay log。

從服務(wù)器：通過(guò) SQL 線程執(zhí)行 relay log 中的事件，完成數(shù)據(jù)同步。

-- 主服務(wù)器上查看從服務(wù)器連接狀態(tài)
SHOWSLAVEHOSTS;

-- 從服務(wù)器上查看復(fù)制狀態(tài)
SHOWSLAVESTATUSG

4.2 從服務(wù)器需要開啟二進(jìn)制日志嗎

從服務(wù)器是否開啟二進(jìn)制日志取決于架構(gòu)設(shè)計(jì)：

如果從服務(wù)器同時(shí)作為其他從服務(wù)器的主服務(wù)器，必須開啟。

如果從服務(wù)器只是用于讀負(fù)載均衡，可以關(guān)閉。

如果需要使用從服務(wù)器進(jìn)行增量備份，必須開啟。

-- 查看從服務(wù)器是否開啟二進(jìn)制日志
SHOWVARIABLESLIKE'log_slave_updates';
-- log_slave_updates = ON 表示從服務(wù)器會(huì)將 relay log 執(zhí)行后記錄到自己的二進(jìn)制日志

4.3 復(fù)制環(huán)境下的日志清理策略

在復(fù)制環(huán)境中，清理二進(jìn)制日志需要考慮從服務(wù)器的讀取進(jìn)度：

-- 查看所有從服務(wù)器的狀態(tài)
SHOWSLAVEHOSTS;
-- +-----------+-------------+------+-----------+
-- | Server_id | Host    | Port | Master_id |
-- +-----------+-------------+------+-----------+
-- |     2 | slave-01  | 3306 |     1 |
-- |     3 | slave-02  | 3306 |     1 |
-- +-----------+-------------+------+-----------+

-- 查看每個(gè)從服務(wù)器讀取到哪個(gè)日志文件
SHOWSLAVESTATUSG
-- Relay_Master_Log_File: mysql-bin.000050
-- Exec_Master_Log_Pos: 12345678

安全清理策略：只刪除所有從服務(wù)器都已經(jīng)讀取完畢的日志。

-- 方式一：基于從服務(wù)器狀態(tài)自動(dòng)清理
-- 使用 mysqlrpladmin 工具
-- mysqlrpladmin --master=root:pass@master-host --discover-slaves-for=master prune

-- 方式二：手動(dòng)確認(rèn)后清理
-- 1. 在每個(gè)從服務(wù)器上執(zhí)行 SHOW SLAVE STATUS，記錄 Relay_Master_Log_File
-- 2. 在主服務(wù)器上找到最早的日志文件
-- 3. 使用 PURGE BINARY LOGS TO 'mysql-bin.xxxxxx' 刪除更早的日志

4.4 GTID 模式下的日志管理

MySQL 8.0 推薦使用 GTID（Global Transaction Identifier）模式，簡(jiǎn)化復(fù)制管理：

-- 查看 GTID 配置
SHOWVARIABLESLIKE'gtid_mode';
SHOWVARIABLESLIKE'enforce_gtid_consistency';

-- 查看當(dāng)前執(zhí)行的 GTID
SELECT@@GLOBAL.gtid_executed;
SELECT@@GLOBAL.gtid_purged;

在 GTID 模式下，日志清理更加安全：

-- 查看已執(zhí)行的 GTID 集合
SHOWGLOBALVARIABLESLIKE'gtid_executed';

-- 自動(dòng)清理會(huì)考慮 GTID 集合
-- PURGE BINARY LOGS 會(huì)自動(dòng)跳過(guò)包含已執(zhí)行事務(wù)的日志
PURGEBINARYLOGSTO'mysql-bin.000100';
-- 上面命令會(huì)報(bào)錯(cuò)，如果 mysql-bin.000100 包含未執(zhí)行的事務(wù)

五、二進(jìn)制日志配置優(yōu)化

5.1 基礎(chǔ)配置參數(shù)

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# 開啟二進(jìn)制日志
log_bin = /var/log/mysql/mysql-bin

# 二進(jìn)制日志格式
binlog_format = ROW

# 二進(jìn)制日志緩存大?。ɑ跁?huì)話）
binlog_cache_size = 4M

# 最大二進(jìn)制日志緩存大小
max_binlog_cache_size = 512M

# 單個(gè)二進(jìn)制日志文件大小
max_binlog_size = 1G

# 自動(dòng)清理天數(shù)
expire_logs_days = 7

# sync_binlog 控制何時(shí)將二進(jìn)制日志同步到磁盤
sync_binlog = 1 # 每次事務(wù)提交后同步，最安全但性能影響大
# sync_binlog = 0 # 依賴操作系統(tǒng)刷盤，性能好但可能有數(shù)據(jù)丟失

# binlog_row_image 控制 ROW 格式下記錄哪些鏡像
binlog_row_image = FULL # 記錄所有列
# binlog_row_image = MINIMAL # 只記錄主鍵和變更的列

5.2 性能與安全的權(quán)衡

sync_binlog 參數(shù)的選擇：

sync_binlog = 1：最安全，每次事務(wù)提交都同步日志到磁盤。MySQL 崩潰時(shí)最多丟失一個(gè)事務(wù)。性能影響最大。

sync_binlog = 0：性能最好，依賴操作系統(tǒng)刷盤。MySQL 崩潰時(shí)可能丟失多個(gè)事務(wù)。

sync_binlog = N：每 N 個(gè)事務(wù)同步一次。性能和數(shù)據(jù)安全的折中。

-- 查看當(dāng)前配置
SHOWVARIABLESLIKE'sync_binlog';
-- +---------------+-------+
-- | Variable_name | Value |
-- +---------------+-------+
-- | sync_binlog  | 1   |
-- +---------------+-------+

-- 設(shè)置同步策略
SETGLOBALsync_binlog =100; -- 每100個(gè)事務(wù)同步一次

5.3 二進(jìn)制日志緩存優(yōu)化

binlog_cache_size 用于緩存未提交事務(wù)的二進(jìn)制日志：

-- 查看二進(jìn)制日志緩存使用情況
SHOWSTATUSLIKE'Binlog_cache%';
-- +-----------------------+-------+
-- | Variable_name     | Value |
-- +-----------------------+-------+
-- | Binlog_cache_disk_use | 1234 | -- 內(nèi)存緩存不夠，使用了磁盤
-- | Binlog_cache_use   | 5678 | -- 使用內(nèi)存緩存的事務(wù)數(shù)
-- +-----------------------+-------+

-- 如果 Binlog_cache_disk_use 較大，增加 binlog_cache_size
SETGLOBALbinlog_cache_size =8*1024*1024; -- 8MB

5.4 行格式下的優(yōu)化

binlog_row_image 參數(shù)影響 ROW 格式下的日志大?。?/p>

-- FULL：記錄所有列的完整數(shù)據(jù)
-- MINIMAL：只記錄主鍵和實(shí)際變更的列
-- NOBLOB：對(duì)于沒有 BLOB 列的表，不記錄 BLOB 數(shù)據(jù)

SHOWVARIABLESLIKE'binlog_row_image';
-- +------------------+-------+
-- | Variable_name  | Value |
-- +------------------+-------+
-- | binlog_row_image | FULL |
-- +------------------+-------+

-- 如果表沒有 BLOB 列，可以設(shè)置為 MINIMAL 減少日志量
SETGLOBALbinlog_row_image = MINIMAL;

六、實(shí)戰(zhàn)案例分析

6.1 案例一：未配置自動(dòng)清理導(dǎo)致磁盤爆滿

6.1.1 問(wèn)題現(xiàn)象

監(jiān)控告警：/var 分區(qū)使用率超過(guò) 90%。

SSH 登錄后檢查：du -sh /var/lib/mysql 顯示只有 200GB。

但 df -h 顯示已使用 350GB。

6.1.2 排查過(guò)程

# 查看磁盤使用
df -h /var
# Filesystem   Size Used Avail Use% Mounted on
# /dev/sda1    400G 380G  20G 95% /var

# 查看 MySQL 目錄詳情
du -sh /var/lib/mysql/*
# 78G   /var/lib/mysql/data (實(shí)際數(shù)據(jù))
# 280G  /var/lib/mysql/binlog (二進(jìn)制日志)
# 12G   /var/lib/mysql/relaylog (從服務(wù)器 relay log)

# 確認(rèn)二進(jìn)制日志
ls -lh /var/log/mysql/mysql-bin.0* | tail -10

6.1.3 問(wèn)題根因

配置文件中未設(shè)置 expire_logs_days 參數(shù)，默認(rèn)為 0 表示不自動(dòng)清理。

業(yè)務(wù)上線時(shí)從全量備份恢復(fù)后，從未清理過(guò)二進(jìn)制日志。

沒有配置從服務(wù)器的 relay log 清理。

6.1.4 解決步驟

-- 1. 確認(rèn)從服務(wù)器狀態(tài)
SHOWSLAVESTATUSG
-- Relay_Master_Log_File: mysql-bin.00150
-- Exec_Master_Log_Pos: 1234567

-- 2. 設(shè)置自動(dòng)清理（保留最近 7 天）
SETGLOBALexpire_logs_days =7;

-- 3. 手動(dòng)清理超過(guò) 7 天的日志
PURGEBINARYLOGSTO'mysql-bin.00150';

-- 4. 修改配置文件永久生效
-- 在 [mysqld] 段添加或修改：
-- expire_logs_days = 7

-- 5. 從服務(wù)器也需要清理 relay log
SHOWVARIABLESLIKE'relay_log_purge';
SETGLOBALrelay_log_purge =ON;

6.1.5 預(yù)防措施

在 MySQL 配置文件中始終設(shè)置 expire_logs_days。

部署監(jiān)控告警，監(jiān)控二進(jìn)制日志目錄大小。

定期檢查二進(jìn)制日志使用情況。

6.2 案例二：大事務(wù)導(dǎo)致單個(gè)日志文件過(guò)大

6.2.1 問(wèn)題現(xiàn)象

某個(gè)表執(zhí)行了大表更新，導(dǎo)致二進(jìn)制日志瞬間增長(zhǎng) 50GB。

max_binlog_size 設(shè)置為 1GB，但單個(gè)日志文件超過(guò) 10GB。

6.2.2 排查過(guò)程

-- 查看最大的二進(jìn)制日志文件
SELECT
  LOG_NAME,
  FILE_SIZE /1024/1024ASsize_mb
FROMinformation_schema.FILES
WHEREFILE_TYPE ='BINARY LOG'
ORDERBYFILE_SIZEDESC
LIMIT10;

-- 查看是什么時(shí)間點(diǎn)開始變大
SHOWBINARYLOGS;

6.2.3 問(wèn)題分析

大表更新（如 UPDATE t SET col = 'xxx' 沒有 WHERE 條件）產(chǎn)生了大量 ROW 格式日志。

max_binlog_size 只是觸發(fā)切換的條件，不是硬性限制。

大事務(wù)必須完整寫入一個(gè)二進(jìn)制日志文件，不能分割。

6.2.4 解決建議

分批處理大事務(wù)：

-- 原來(lái)的一次性更新
-- UPDATE large_table SET status = 1;

-- 改為分批更新
DELIMITER //
CREATEPROCEDUREbatch_update()
BEGIN
 DECLAREbatch_sizeINTDEFAULT1000;
 DECLAREoffset_numINTDEFAULT0;
 DECLAREdoneINTDEFAULTFALSE;

  WHILE NOT doneDO
   UPDATElarge_table
   SETstatus=1
   WHEREidBETWEENoffset_numANDoffset_num + batch_size -1;

   SEToffset_num = offset_num + batch_size;

   -- 檢查是否還有未更新的記錄
    IF ROW_COUNT() < batch_size THEN
? ? ? ? ? ??SET?done =?TRUE;
? ? ? ??ENDIF;

? ? ? ??-- 提交當(dāng)前批次
? ? ? ??COMMIT;
? ??ENDWHILE;
END?//
DELIMITER ;

CALL?batch_update();

6.3 案例三：從服務(wù)器 relay log 占用大量空間

6.3.1 問(wèn)題現(xiàn)象

從服務(wù)器磁盤空間告警，但主服務(wù)器一切正常。

檢查發(fā)現(xiàn)從服務(wù)器的 relay-log 目錄占用 200GB。

6.3.2 排查過(guò)程

-- 在從服務(wù)器上查看 relay log 配置
SHOWVARIABLESLIKE'%relay%';
-- relay_log      = /var/lib/mysql/mysql-relay-bin
-- relay_log_purge   = ON
-- relay_log_space_limit = 0 (0 表示不限制)

-- 查看 relay log 文件
ls -lh /var/lib/mysql/mysql-relay-bin.* | head -20

6.3.3 問(wèn)題根因

雖然 relay_log_purge 默認(rèn)為 ON，但以下情況會(huì)導(dǎo)致 relay log 堆積：

復(fù)制中斷，從服務(wù)器無(wú)法執(zhí)行事件。

網(wǎng)絡(luò)問(wèn)題導(dǎo)致從服務(wù)器長(zhǎng)時(shí)間無(wú)法讀取主服務(wù)器的日志。

某些大事務(wù)導(dǎo)致單個(gè) relay log 文件過(guò)大。

6.3.4 解決方案

-- 確保 relay log 自動(dòng)清理開啟
SHOWVARIABLESLIKE'relay_log_purge';
SETGLOBALrelay_log_purge =ON;

-- 如果有復(fù)制中斷，先解決中斷問(wèn)題
SHOWSLAVESTATUSG
-- 查看 Last_Error 和 Last_IO_Error 字段

-- 手動(dòng)清理 relay log
STOPSLAVE;
PURGERELAYLOGS;
STARTSLAVE;

七、二進(jìn)制日志監(jiān)控

7.1 監(jiān)控指標(biāo)

-- 二進(jìn)制日志總大小
SELECT
 COUNT(*)AStotal_files,
 SUM(FILE_SIZE) /1024/1024AStotal_mb,
 MAX(FILE_SIZE) /1024/1024ASmax_file_mb
FROMinformation_schema.FILES
WHEREFILE_TYPE ='BINARY LOG';

-- 二進(jìn)制日志寫入統(tǒng)計(jì)
SHOWSTATUSLIKE'Binlog%';
-- +-----------------------+-------+
-- | Variable_name     | Value |
-- +-----------------------+-------+
-- | Binlog_cache_disk_use | 0   |
-- | Binlog_cache_use   | 1000 |
-- +-----------------------+-------+

-- 復(fù)制相關(guān)的二進(jìn)制日志統(tǒng)計(jì)
SHOWSTATUSLIKE'Slave%';

7.2 監(jiān)控腳本

#!/bin/bash
# 二進(jìn)制日志監(jiān)控腳本

ALERT_THRESHOLD_GB=100
LOG_DIR="/var/log/mysql"
MYSQL_USER="root"
MYSQL_PASS="YourPassword"

# 獲取二進(jìn)制日志總大小
TOTAL_SIZE=$(mysql -u${MYSQL_USER}-p${MYSQL_PASS}-N -e"
SELECT SUM(FILE_SIZE) / 1024 / 1024 / 1024
FROM information_schema.FILES
WHERE FILE_TYPE = 'BINARY LOG';
")

# 比較閾值
if["$(echo "$TOTAL_SIZE > $ALERT_THRESHOLD_GB" | bc)"-eq 1 ];then
 echo"ALERT: Binary log size (${TOTAL_SIZE}GB) exceeds threshold (${ALERT_THRESHOLD_GB}GB)"
 # 發(fā)送告警通知
 # 這里可以接入郵件、短信、釘釘?shù)雀婢? exit1
fi

# 獲取最舊的日志文件
OLDEST_LOG=$(mysql -u${MYSQL_USER}-p${MYSQL_PASS}-N -e"SHOW BINARY LOGS;"| head -1 | awk'{print $1}')

# 獲取最新日志文件
NEWEST_LOG=$(mysql -u${MYSQL_USER}-p${MYSQL_PASS}-N -e"SHOW BINARY LOGS;"| tail -1 | awk'{print $1}')

echo"Binary Log Report:"
echo"Total Size:${TOTAL_SIZE}GB"
echo"Files:${OLDEST_LOG}to${NEWEST_LOG}"
echo"Oldest file:${OLDEST_LOG}"

7.3 Prometheus 監(jiān)控配置

# mysql_exporter 二進(jìn)制日志監(jiān)控指標(biāo)
-job_name:'mysql'
static_configs:
-targets:['localhost:9104']
relabel_configs:
-source_labels:[__address__]
 target_label:instance
 regex:'(.+):d+'
 replacement:'${1}'

八、二進(jìn)制日志恢復(fù)實(shí)踐

8.1 基于時(shí)間點(diǎn)的恢復(fù)

# 假設(shè)需要恢復(fù)到 2026-01-15 1000 的狀態(tài)

# 1. 先找到對(duì)應(yīng)的日志文件
mysqlbinlog --no-defaults --stop-datetime="2026-01-15 0959"
 /var/log/mysql/mysql-bin.000123 > /tmp/full_recovery.sql

# 2. 如果需要跳過(guò)某些錯(cuò)誤
mysqlbinlog --no-defaults 
 --stop-datetime="2026-01-15 0959"
 --database=opsdb 
 /var/log/mysql/mysql-bin.000123 /var/log/mysql/mysql-bin.000124 
 > /tmp/partial_recovery.sql

# 3. 恢復(fù)數(shù)據(jù)
mysql -u root -p < /tmp/full_recovery.sql

8.2 基于位置點(diǎn)的恢復(fù)

# 找到需要恢復(fù)的位置點(diǎn)
mysqlbinlog --no-defaults --base64-output=decode-rows -v 
 /var/log/mysql/mysql-bin.000123 | grep -A 5"DROP TABLE"| head -30

# 找到位置點(diǎn)后
mysqlbinlog --no-defaults 
 --stop-position=12345678 
 /var/log/mysql/mysql-bin.000123 > /tmp/recovery.sql

mysql -u root -p < /tmp/recovery.sql

8.3 從從服務(wù)器恢復(fù)

如果主服務(wù)器的二進(jìn)制日志不可用，可以從從服務(wù)器恢復(fù)：

-- 在從服務(wù)器上執(zhí)行
SHOWSLAVESTATUSG
-- 記錄 Master_Log_File 和 Exec_Master_Log_Pos

# 從從服務(wù)器的 relay log 恢復(fù)
mysqlbinlog --no-defaults 
 --start-position=123 
 --stop-position=12345678 
 /var/lib/mysql/mysql-relay-bin.000050 > /tmp/slave_recovery.sql

mysql -u root -p < /tmp/slave_recovery.sql

九、最佳實(shí)踐總結(jié)

9.1 配置規(guī)范

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# 二進(jìn)制日志基礎(chǔ)配置
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
max_binlog_size = 1G
expire_logs_days = 7

# 性能優(yōu)化
sync_binlog = 1000 # 性能敏感場(chǎng)景
binlog_cache_size = 8M
max_binlog_cache_size = 512M

# 從服務(wù)器配置
log_slave_updates = ON
relay_log_purge = ON
relay_log_recovery = ON

9.2 運(yùn)維規(guī)范

每日檢查二進(jìn)制日志目錄大小。

設(shè)置合理的告警閾值（建議磁盤使用的 80%）。

重要數(shù)據(jù)變更前記錄當(dāng)前的二進(jìn)制日志位置。

定期備份二進(jìn)制日志到異地存儲(chǔ)。

9.3 恢復(fù)預(yù)案

每季度測(cè)試一次恢復(fù)流程。

維護(hù)最新的備份和日志位置文檔。

記錄所有大事務(wù)的日志位置，便于選擇性恢復(fù)。

總結(jié)

二進(jìn)制日志是 MySQL 運(yùn)維中最重要的組件之一，但也是最容易引發(fā)磁盤空間問(wèn)題的組件。

理解二進(jìn)制日志的工作原理是解決問(wèn)題的前提：它記錄所有數(shù)據(jù)變更，用于復(fù)制和恢復(fù)，在 ROW 格式下日志量可能很大。

磁盤空間暴漲的主要原因是未配置自動(dòng)清理策略。在生產(chǎn)環(huán)境中，必須設(shè)置 expire_logs_days 參數(shù)，并配置監(jiān)控告警。

復(fù)制環(huán)境下清理日志需要格外謹(jǐn)慎，必須確保所有從服務(wù)器已經(jīng)讀取完畢要?jiǎng)h除的日志文件。

大事務(wù)會(huì)產(chǎn)生大量日志，分批處理是最佳實(shí)踐。

日常監(jiān)控和定期檢查是預(yù)防問(wèn)題的關(guān)鍵。配置合理的告警閾值，建立日志增長(zhǎng)趨勢(shì)分析，才能在問(wèn)題發(fā)生前發(fā)現(xiàn)端倪。