MySQL tmpdir 參數修改實戰：解決 /tmp 磁碟空間不足導致的查詢失敗與服務中斷

生產環境突發告警：/tmp 分區 100% 滿，大量複雜查詢因無法創建臨時表而失敗。根因：tmpdir 預設指向系統 /tmp (通常較小)，大排序、分組、連接查詢產生的臨時表撐爆空間。本文記錄線上修改 tmpdir 至大容量數據盤的完整流程、熱修嘗試與生產環境最佳實踐。

現場排查

-- 確認當前 tmpdir
SHOW VARIABLES LIKE 'tmpdir';
-- 輸出: /tmp

-- 查看佔用空間大的臨時文件
ls -lh /tmp/#sql* /tmp/ib* /tmp/mysql* 2>/dev/null | head -20

-- 當前運行的大查詢
SHOW FULL PROCESSLIST WHERE Command='Query' AND Time > 10;

方案對比

生產環境標準修改流程 (my.cnf + 滾動重啟)

1. 規劃目標目錄

# 選擇大容量、高 IOPS 數據盤
mkdir -p /data/mysql/tmp
chown mysql:mysql /data/mysql/tmp
chmod 1777 /data/mysql/tmp  # 粘滯位，防止誤刪

2. 修改配置文件

[mysqld]
tmpdir=/data/mysql/tmp
# 建議同時優化相關參數
tmp_table_size=256M
max_heap_table_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=4M

3. 滾動重啟 (主從架構)

1. 從庫先重啟：systemctl restart mysqld
2. 驗證從庫 SHOW VARIABLES LIKE 'tmpdir' 生效
3. 主庫切換 (若有 MHA/Orchestrator) 或低峰重啟主庫

4. 驗證生效

SHOW VARIABLES LIKE 'tmpdir';
-- 輸出: /data/mysql/tmp

-- 模擬大查詢驗證寫入
SELECT * FROM large_table ORDER BY non_indexed_col LIMIT 10;
-- 觀察 /data/mysql/tmp 下生成 #sql* 臨時文件

緊急臨時方案：符號鏈接 (不重啟)

# 停止寫入 /tmp 的查詢 (殺掉大查詢進程)
# 移動現有 /tmp 內容
mv /tmp /tmp.bak
# 創建符號鏈接
ln -s /data/mysql/tmp /tmp
# 重新啟動受影響服務 (如有獨立 MySQL 實例)
# 注意：影響所有使用 /tmp 的系統服務，謹慎使用！

tmpfs 內存盤方案 (高性能臨時表)

# /etc/fstab
tmpfs /data/mysql/tmp tmpfs defaults,size=4G,mode=1777,uid=mysql,gid=mysql 0 0

# 掛載
mount -a

# 修改 my.cnf
tmpdir=/data/mysql/tmp

適用場景：大量小臨時表、排序緩衝、內存充足 (建議 ≤ 總內存 10%)

風險：重啟丟失、OOM Killer 觸發、大臨時表直接落盤更穩健

監控與告警

# 納入 Prometheus node_exporter
# 告警規則
- alert: MySQLTmpdirDiskFull
  expr: (node_filesystem_avail_bytes{mountpoint="/data/mysql/tmp"} / node_filesystem_size_bytes{mountpoint="/data/mysql/tmp"}) < 0.15
  for: 5m
  labels:
    severity: critical
  annotations:
    summary: "MySQL tmpdir 剩餘空間 < 15%"
    description: "請清理臨時文件或擴容 /data/mysql/tmp"

避坑指南

坑 1：修改 tmpdir 後舊查詢仍報錯

舊查詢已在舊目錄創建臨時文件，需殺掉重跑

坑 2：多實例共用 tmpdir 導致衝突

每實例獨立子目錄：tmpdir=/data/mysql/tmp/3306

坑 3：SELinux 阻止寫入

chcon -R -t mysqld_db_t /data/mysql/tmp 或 setsebool -P mysql_connect_any 1

坑 4：tmp_table_size 過小導致臨時表頻繁落盤

配合調大至 256M-1G，但防止單查詢佔用過多內存