MySQL tmpdir 參數修改實戰：磁盤空間不足臨時遷移、唯讀變量限制與生產無重啟變更方案

MySQL tmpdir 參數修改實戰：磁盤空間不足臨時遷移、唯讀變量限制與生產無重啟變更方案

異常 SQL 導致臨時表佔滿 /tmp 觸發磁盤告警是常見故障。tmpdir 為唯讀變量，無法動態 SET GLOBAL，必須修改配置文件重啟生效。本文記錄排查流程、修改步驟，並探討生產環境無法重啟時的緩解方案。

故障現象

# 告警：/ 磁盤使用率 > 90%
df -h /tmp
# 發現大量 #sql_* 臨時文件佔用空間

mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+

mysql> SET GLOBAL tmpdir = '/data/mysql/tmp';
ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable

標準修改流程（需重啟）

1. 創建新目錄並授權：

   mkdir -p /data/mysql/tmp
   chown mysql:mysql /data/mysql/tmp
   chmod 1777 /data/mysql/tmp  # 防止被其他用戶誤刪

2. 編輯 /etc/my.cnf：

   [mysqld]
   tmpdir = /data/mysql/tmp

3. 重啟服務生效：

     方案操作優點缺點/風險
     符號鏈接欺騙mv /tmp /tmp.bak; ln -s /data/mysql/tmp /tmp無需重啟 MySQL，立即生效影響系統其他進程使用 /tmp；重啟 OS 後失效；需謹慎評估
     清理現有臨時文件lsof /tmp | grep mysqld 確認無活躍句柄後 rm -f /tmp/#sql_*快速釋放空間治標不治本，異常 SQL 繼續跑會再滿
     Kill 異常 SQL 線程SHOW PROCESSLIST; 找到長時間 Creating tmp table / Copying to tmp table 的線程 KILL id;釋放臨時表空間，緩解壓力業務中斷，需溝通
     調大 tmpfs 掛載mount -o remount,size=4G /tmp (若 /tmp 為 tmpfs)無需重啟，擴容即時佔用內存，OOM 風險；非 tmpfs 無效
     滾動重啟集群主從/集群架構下，逐個節點修改配置重啟服務不中斷需架構支援，操作複雜
   
   
   根因治理：防止臨時表爆盤
   
     
   優化異常 SQL：EXPLAIN 分析 Using temporary; Using filesort，加索引/改寫避免臨時表。
     
   限制臨時表大小：tmp_table_size=64M, max_heap_table_size=64M，超限自動轉磁盤 MyISAM，但受 tmpdir 空間限制。
     
   監控告警：Prometheus 監控 Created_tmp_disk_tables、Created_tmp_tables、磁盤使用率，閾值告警。
     
   定期清理：cron 任務清理超 1 小時的 /tmp/#sql_*（確認無進程持有）。
   
   
   MySQL 8.0 新特性：innodb_temp_tablespaces_dir
   8.0 將內部臨時表（InnoDB 存儲引擎）與外部臨時表（MyISAM/Memory）分離：
   
     
   tmpdir：仍控制外部臨時表目錄（CREATE TEMPORARY TABLE ... ENGINE=MyISAM、ALTER TABLE ... ENGINE=InnoDB 重建過程）。
     
   innodb_temp_tablespaces_dir：控制內部臨時表表空間目錄（默認 DATADIR/#innodb_temp），支援動態修改 SET GLOBAL innodb_temp_tablespaces_dir='/new/path';（需重啟生效）。
   
   臨時表空間管理是穩定性關鍵，預留充足磁盤、優化慢查詢、監控到位，三管齊下杜絕爆盤。