MySQL表碎片整理實戰：myisamchk修復錯誤127根因分析與三種安全操作模式

問題背景

大量刪除relationship表資料（2000萬行）後，磁碟空間未釋放。執行myisamchk -r relationship碎片整理，MySQL運行中操作，查詢報錯：

ERROR 1030 (00000): Got error 127 from table handler

根因分析：--skip-locking 與並發衝突

Linux預設啟用--skip-locking（即--skip-external-locking），MySQL不使用系統層檔案鎖。此模式下：

• myisamchk 讀取表檔案時，若 mysqld 同時寫入同一表 → 檔案不一致 → 錯誤 127
• 即使無實際寫入，快取不一致也可能觸發

官方文檔明確警告：--skip-locking 環境下，mysqld 使用表時不可靠執行 myisamchk。

三種安全操作模式

模式一：停止 MySQL 服務後操作 (最安全、停機時間長) ⭐

# 1. 正常關閉 MySQL
mysqladmin -u root -p shutdown

# 2. 執行碎片整理/修復
myisamchk -r /var/lib/mysql/dbname/relationship

# 3. 重啟 MySQL
/etc/init.d/mysql start

優點：零衝突風險。缺點：需停機維護窗口。

模式二：線上操作 + 重啟驗證 (折中方案)

# 1. 線上執行 myisamchk (風險自負)
myisamchk -r /var/lib/mysql/dbname/relationship

# 2. 立即重啟 MySQL 強制重新載入表定義
/etc/init.d/mysql restart

重啟可清除快取不一致，但操作期間仍有資料損壞風險。

模式三：FLUSH TABLES 後線上操作 (生產環境首選、無停機) ⭐

# 1. 刷新表快取，關閉所有開啟表，釋放鎖定
mysqladmin -u root -p flush-tables

# 2. 確認無長時間查詢運行 (SHOW PROCESSLIST)
# 3. 執行碎片整理
myisamchk -r /var/lib/mysql/dbname/relationship

# 4. 可選：再次 FLUSH TABLES 確保快取同步
mysqladmin -u root -p flush-tables

原理：FLUSH TABLES 關閉所有表檔案控制代碼，確保 myisamchk 獨佔存取。

進階：OPTIMIZE TABLE SQL 替代方案

# 在 MySQL 客戶端直接執行 (自動處理鎖定、重建)
OPTIMIZE TABLE relationship;

• ✅ 無需停機、自動鎖定協調
• ✅ 適用 MyISAM、InnoDB (InnoDB 實為 ALTER TABLE ... ENGINE=InnoDB 重建)
• ✅ 記錄於 Binlog、復制友善
• ⚠️ 大表耗時長、需足夠磁碟空間建立臨時表

關鍵檢查清單

1. 確認表引擎：SHOW TABLE STATUS LIKE 'relationship'\G → Engine: MyISAM
2. 確認 skip-external-locking 狀態：SHOW VARIABLES LIKE 'skip_external_locking'
3. 大表優先使用 OPTIMIZE TABLE 或停機 myisamchk
4. 操作前完整備份：mysqldump --single-transaction dbname relationship > backup.sql
5. 磁碟空間 ≥ 表大小 1.5倍 (臨時檔案需求)

InnoDB 表碎片整理

InnoDB 不使用 myisamchk：

# 線上重建 (5.6+ 支援 ONLINE DDL)
OPTIMIZE TABLE innodb_table;
# 或
ALTER TABLE innodb_table ENGINE=InnoDB;
# 線上重建不阻塞 DML (視版本與參數)