MySQL 臨時檔案與隱藏空間管理：TMPDIR、磁碟溢出防護與複寫安全

臨時檔案產生場景與空間估算

TMPDIR 配置最佳實踐

1. 環境變數優先級

# 1. 啟動腳本/服務檔案設定 (最高優先級)
Environment="TMPDIR=/mnt/mysql_tmp"

# 2. my.cnf 參數 (次優先)
[mysqld]
tmpdir = /mnt/mysql_tmp:/mnt/mysql_tmp2:/mnt/mysql_tmp3  # 多路徑循環分佈 (5.1+)

# 3. 系統預設 (最低)
/tmp, /var/tmp, /usr/tmp

2. 多磁碟循環分佈 (關鍵優化)

• 路徑必須位於 **不同物理磁碟** (NVMe / SSD / HDD)，而非同磁碟不同分區
• 格式：Linux 用冒號 `:` 分隔，Windows 用分號 `;`
• 效果：並行排序/臨時表寫入自動輪詢分佈，提升 IOPS 吞吐、降低單盤壓力

3. 檔案系統選型建議

複寫從庫 tmpdir 禁忌與安全配置

❌ 絕對禁止

• 將 tmpdir 指向 tmpfs (如 /dev/shm、/run/mysqld)
• 指向掛載 noexec、nosuid 或啟動腳本會清空的目錄 (如 /tmp 被 systemd-tmpfiles 清理)

✅ 必須確保

• 臨時檔案在 **mysqld 重啟後依然存在** (複寫從庫需保留 LOAD DATA INFILE 檔案、臨時表以便斷點續傳)
• 目錄擁有者 mysql:mysql，權限 1777 (sticky bit 防止誤刪)
• 磁碟空間 ≥ **最大單事務 Binlog 大小 × 2** + **最大排序臨時空間估算**

安全配置範例

[mysqld]
# 主庫：可用 tmpfs 加速小排序 (需足夠記憶體)
tmpdir = /dev/shm/mysql_tmp:/mnt/nvme1/mysql_tmp:/mnt/nvme2/mysql_tmp

# 從庫：嚴禁 tmpfs，使用持久化高效能磁碟陣列
tmpdir = /data/mysql_tmp1:/data/mysql_tmp2:/data/mysql_tmp3

# 目錄建立腳本 (啟動前執行)
mkdir -p /data/mysql_tmp{1,2,3}
chown mysql:mysql /data/mysql_tmp*
chmod 1777 /data/mysql_tmp*
# /etc/fstab 持久化掛載 (若為獨立磁碟)
/dev/nvme1n1 /data/mysql_tmp1 xfs defaults,noatime 0 2

磁碟溢出 (Disk Full) 自動處理機制

MySQL 內建行為

1. 每 **60 秒** 檢查一次可用空間
2. 每 **6 分鐘** 向錯誤日誌寫入警告：Disk is full writing '/mnt/tmp/MYxxxx' (Errcode: 28 - No space left on device)
3. 若空間釋放，自動繼續寫入；否則線程持續阻塞

運維介入指令

# 1. 確認阻塞線程
SHOW PROCESSLIST;
-- 尋找 State: "Waiting for disk space" 或 "Writing to tmp table"

# 2. 釋放空間 (優先順序)
# 刪除舊備份、舊 Binlog (PURGE BINARY LOGS BEFORE '2024-01-01')
# 清理 /tmp、/var/tmp 無用檔案
# 擴容磁碟 / 掛載新磁碟至 tmpdir 路徑

# 3. 強制殺死阻塞線程 (最後手段)
KILL ;  -- 1 分鐘內生效

# 4. 觀察其他等待同表鎖的線程是否恢復
SHOW ENGINE INNODB STATUS \G  -- 關注 LOCK WAIT 區塊

監控與告警體系

# Prometheus mysqld_exporter 關鍵指標
mysql_global_status_created_tmp_tables        # 記憶體臨時表計數
mysql_global_status_created_tmp_disk_tables   # 磁碟臨時表計數 (關鍵！)
mysql_global_status_created_tmp_files         # 臨時檔案計數
mysql_global_status_sort_merge_passes         # 磁碟歸併排序次數

# 告警規則 (PromQL)
- alert: MySQLHighDiskTempTables
  expr: rate(mysql_global_status_created_tmp_disk_tables[5m]) > 10
  labels:
    severity: warning
  annotations:
    summary: "MySQL 磁碟臨時表建立率過高"
    description: "建議檢查索引、增加 sort_buffer_size、優化 SQL"

- alert: MySQLTmpDirDiskFull
  expr: (node_filesystem_avail_bytes{mountpoint=~"/mnt/mysql_tmp.*"} / node_filesystem_size_bytes{mountpoint=~"/mnt/mysql_tmp.*"}) < 0.15
  labels:
    severity: critical
  annotations:
    summary: "MySQL tmpdir 磁碟剩餘空間 < 15%"
    description: "立即清理或擴容，否則將導致查詢阻塞/失敗"

隱含臨時檔案可見性問題

MySQL 以 unlink 開啟檔案 (建立後立即刪除目錄項)，導致 ls -la 無法看到佔用空間的大臨時檔案。

定位佔用空間的隱含檔案

# 方法 1: lsof 查看 mysqld 開啟的已刪除檔案
lsof -p $(pidof mysqld) | grep deleted

# 方法 2: /proc 探索
ls -l /proc/$(pidof mysqld)/fd/ | grep -E 'tmp|MY'

# 方法 3: 計算實際佔用
du -sh /mnt/mysql_tmp*  # 僅顯示目錄項存在的檔案
# 真實佔用 = df -h /mnt/mysql_tmp1 觀察 Used 差異

1. 上一篇文章： SQL Manager 2010 for MySQL 破解版安裝與替換破解教程
2. 下一篇文章： MySQL 磁碟空間耗盡 (Errcode 28) 自動處理機制與運維應急指南

MySQL 資料庫相關知識

MySQL 進階主題：結果集計算、資料編碼、圖片存取與常見錯誤

6.8.1 利用結果集中繼資料進行統計計算 範例函式 sum

MySQL 多表關聯刪除：單條 DELETE 語句刪除多表關聯數據的正確姿勢

早年在百度知道上看到一個關於 MySQL 多表刪除的問題，當

Windows 下 MySQL 5.7 ZIP 免安裝版完整部署教程（含免密登入與密碼重置）

MySQL 5.7 ZIP 免安裝版（no-install）

MySQL 主從複制、讀寫分離、備份恢復企業級實戰全紀錄

一、主從複制部署 (GTID 模式推薦) 1. 規劃與前置