您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 磁碟空間耗盡 (Errcode 28) 自動處理機制與運維應急指南

編輯：MySQL 資料庫相關知識

錯誤現象與內建行為

當資料目錄或臨時目錄磁碟剩餘空間為 0 時，MySQL 觸發 「磁碟溢出」保護模式：

每 60 秒檢查一次 可用空間是否足以寫入當前行
每 6 分鐘 向錯誤日誌寫入警告：[Warning] Disk is full writing './db/tbl.MYD' (Errcode: 28 - No space left on device). Waiting for someone to free space...
阻塞線程進入 State: Waiting for disk space，不佔用 CPU、不釋放鎖
若空間釋放，**自動繼續** 寫入，無需重啟服務

三種運維應急策略

策略一：釋放空間讓其自動恢復 (推薦，零停機)

# 1. 定位佔用空間最大的非必要檔案
du -h /var/lib/mysql | sort -hr | head -20
# 常見可清理：舊備份、舊 Binlog、大臨時表、Core dump、Docker 映像

# 2. 安全清理 Binlog (需確認從庫同步位置)
mysql> SHOW SLAVE STATUS\G  -- 記下 Relay_Master_Log_File, Exec_Master_Log_Pos
mysql> PURGE BINARY LOGS TO 'mysql-bin.000123';  -- 或 PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00'

# 3. 清理系統層暫存檔
find /tmp -type f -mtime +1 -delete
find /var/tmp -type f -mtime +1 -delete
journalctl --vacuum-time=7d  -- 系統日誌

# 4. 觀察錯誤日誌確認恢復
tail -f /var/log/mysql/error.log
# 出現 "Disk space available again. Continuing." 即恢復

策略二：殺死阻塞線程釋放鎖 (次選，影響業務)

# 1. 識別阻塞源頭線程 (通常是大寫入/大排序/ALTER TABLE)
SHOW PROCESSLIST;
-- 關注 Time 最大、State 含 "Waiting for disk space"、"Writing to tmp table"、"copy to tmp table"

# 2. 評估影響：該線程持有什麼鎖？是否阻塞其他核心業務？
SHOW ENGINE INNODB STATUS \G  -- LOCK WAIT 區塊

# 3. 強制殺死
KILL ;  -- 或 KILL QUERY  僅終止當前語句

# 4. 後續：被殺線程回滾釋放鎖，其他等待線程繼續執行
# 注意：大事務回滾可能耗時更長、產生更多臨時空間

策略三：緊急擴容/掛載新磁碟 (根治，需停機或熱插拔)

# 雲端環境 (AWS EBS / Azure Disk / GCP Persistent Disk) - 熱擴容
# 1. 控制台/CLI 擴容磁碟 (如 100GB → 500GB)
# 2. 作業系統層擴展分區與檔案系統
lsblk  # 確認裝置名 /dev/nvme1n1
growpart /dev/nvme1n1 1  # 擴展分區 (需 cloud-guest-utils)
resize2fs /dev/nvme1n1p1  # ext4 擴展檔案系統
# 或 xfs_growfs /mnt/mysql_data  # XFS 擴展

# 物理機熱插拔 (需 RAID 卡/OS 支援)
# 1. 新增磁碟 → 建立分區 → 格式化 → 掛載至新 tmpdir 路徑
# 2. 修改 my.cnf tmpdir 新增路徑 → 重啟 MySQL (或 PERSIST 變數 8.0+)
SET PERSIST tmpdir = '/mnt/mysql_tmp1:/mnt/new_disk/mysql_tmp2';

連鎖阻塞分析與破解

磁碟溢出常引發 **鎖傳播鏈**：

Thread A (大 INSERT) → 磁碟滿 → 阻塞持有 Table X 意向鎖
    ↓
Thread B (SELECT ... FROM X) → 等待 Table X 共享鎖 → 阻塞
    ↓
Thread C (UPDATE X ...) → 等待 Thread B 釋放 → 阻塞
    ↓
連線池耗盡 → 應用報 "Too many connections" / "Lock wait timeout"

破解關鍵：殺死源頭寫入線程 (Thread A)

只殺死下游 SELECT/UPDATE 無效，因鎖仍被 Thread A 持有
殺死 Thread A 觸發回滾 → 釋放所有鎖 → 鏈條全線恢復
回滾期間臨時空間可能再增長，**預留 20% 緩衝空間**

預防體系：三層防線

層級	措施	工具/指標	頻率
基礎設施	資料目錄獨立掛載點、預留 20% 空間、LVM 精簡配置/自動擴容	node_filesystem_avail_bytes、df -h	持續監控、告警 < 15%
資料庫配置	tmpdir 多磁碟、innodb_tmpdir 獨立、max_binlog_size、binlog_expire_logs_seconds	SHOW VARIABLES、SHOW GLOBAL STATUS	部署驗證、變更審計
應用與運維	大操作分批 (pt-archiver)、避免大事務、定期 PURGE BINLOG、備份異地	慢查詢日誌、Schema 變更流程、備份校驗	每日/每週巡檢

關鍵配置參數速查

[mysqld]
# 二進位日誌自動清理 (8.0+ 推薦秒數制)
binlog_expire_logs_seconds = 604800  # 7 天
max_binlog_size = 1G

# 臨時目錄分流
tmpdir = /mnt/tmp1:/mnt/tmp2:/mnt/tmp3
innodb_tmpdir = /mnt/innodb_tmp  # 8.0+ 專用 Innodb 臨時表目錄

# 限制單表/單查詢臨時空間 (8.0+)
internal_tmp_mem_storage_engine = TempTable
temptable_max_ram = 1G
temptable_max_mmap = 10G  # 超過記憶體用 mmap 臨時檔案

# 錯誤日誌記錄磁碟溢出
log_error = /var/log/mysql/error.log
log_error_verbosity = 2  # 包含警告