MySQL MyISAM 轉 InnoDB 完整操作指南：避免鎖表導致 502 錯誤

MyISAM 在高併發寫入場景下易發生表級鎖定，導致請求超時返回 502。轉換為支援行級鎖、事務的 InnoDB 是標準解決方案。本文提供零停機風險的分步遷移流程。

遷移核心原則

• 結構與資料分離遷移：先遷表結構（含引擎修改），再遷資料
• 驗證後再切換：新庫驗證無誤後，再修改應用連線配置
• 保留回滾方案：舊庫保留一段時間，確認穩定再下線

步驟 1：匯出原庫表結構 (僅結構，無資料)

mysqldump -d -u username -p database_name > table_structure.sql
# -d / --no-data：僅匯出結構

步驟 2：批次替換引擎關鍵字

# Linux/macOS 使用 sed 就地替換
sed -i 's/MyISAM/InnoDB/g' table_structure.sql
sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' table_structure.sql  # 兼容不同寫法

# Windows 可用 Notepad++ / PowerShell 替換
# PowerShell: (Get-Content table_structure.sql) -replace 'MyISAM','InnoDB' | Set-Content table_structure.sql

驗證： grep -n "InnoDB" table_structure.sql 確認無殘留 MyISAM。

步驟 3：建立新資料庫並匯入結構

mysql -u root -p -e "CREATE DATABASE database_new CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p database_new < table_structure.sql

驗證引擎：

mysql -u root -p database_new -e "SHOW TABLE STATUS;" | grep Engine

步驟 4：匯出原庫純資料 (無結構)

mysqldump -t -u username -p database_name > table_data.sql
# -t / --no-create-info：僅匯出資料

步驟 5：匯入資料至新庫

mysql -u root -p database_new < table_data.sql

大量資料建議：

• 分批匯入：split -l 10000 table_data.sql part_ 並行導入
• 關閉自動提交：匯入前 SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 導入後恢復
• 調大 max_allowed_packet、innodb_log_file_size

步驟 6：驗證與切換

1. 抽樣比對新舊庫行數、關鍵業務查詢結果
2. 壓力測試新庫寫入效能、鎖競爭情況
3. 維護窗口修改應用配置指向新庫，或重命名資料庫切換

替代方案：在線轉換 (ALGORITHM=INPLACE)

MySQL 5.6+ 支援在線 DDL，小表可直接轉換：

ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

限制： 大表執行時間長、佔用臨時空間、仍有短暫元數據鎖。核心業務大表建議採用上述「雙寫/遷移切換」方案。

事後優化配置建議

[mysqld]
innodb_buffer_pool_size = 物理記憶體 60-80%
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
max_connections = 2000