MySQL 從 MyISAM 轉換 InnoDB 完整遷移實戰與錯誤排查

遷移背景

原配置 Non-Transactional Database Only (僅 MyISAM)，安裝 WordPress 需 InnoDB，執行配置精靈切換引擎時遭遇啟動失敗循環。

核心錯誤鏈與解決

1. ib_logfile size mismatch

InnoDB: Error: log file .\ib_logfile0 is of different size 0 52428800 bytes
InnoDB: than specified in the .cnf file 0 10485760 bytes!

解決：刪除 ib_logfile0、ib_logfile1，**重啟作業系統** (Windows 服務鎖定檔案需重開機釋放)。

2. No valid checkpoint found

InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database...

解決：刪除**所有** ibdata* 與 ib_logfile*，重啟服務自動重建系統表空間。

3. 最終成功日誌特徵

InnoDB: The first specified data file ... ibdata1 did not exist: a new database to be created!
InnoDB: Setting file ... ibdata1 size to 10 MB
InnoDB: Log file ... ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ... ib_logfile0 size to 109 MB
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Foreign key constraint system tables created
InnoDB: Started; log sequence number 0 0
[Note] mysqld: ready for connections.

標準遷移最佳實踐 (避免上述坑)

1. 備份：mysqldump --single-transaction --routines --triggers --all-databases > full.sql
2. 修改配置：設定 default-storage-engine=InnoDB、innodb_buffer_pool_size、innodb_log_file_size 等。
3. 停止服務：systemctl stop mysqld
4. 清理舊檔：移動 ibdata*, ib_logfile* 至備份目錄 (非刪除)。
5. 啟動服務：自動建立新系統表空間與 Redo Log。
6. 轉換表引擎：

   SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB;')
   FROM information_schema.TABLES
   WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
   INTO OUTFILE '/tmp/alter_engine.sql';
   SOURCE /tmp/alter_engine.sql;

7. 驗證：SHOW TABLE STATUS 確認 Engine=InnoDB，應用測試。

Windows 環境特別注意

• 服務鎖定 ibdata1/ib_logfile*，**必須重啟作業系統** 釋放句柄。
• 配置精靈修改會產生 my.ini.bak 依時間命名，可快速回滾。
• 錯誤日誌位置：datadir\主機名.err，善用它定位問題。

教訓總結

若初始規劃直接啟用 InnoDB 並配置合理參數，可避免完整遷移的風險與停機時間。生產環境遷移必須有完整備份、回滾預案、低峰期操作。