找回“消失”的InnoDB引擎

   夜裡重做主從複製，順便調整了下從庫的my.cnf。早上想起從庫忘了重啟，打完命令接著開啟slave，居然報錯

Last_SQL_Errno: 1286
Last_SQL_Error: Error executing row event: 'Unknown storage engine 'InnoDB''

奇怪，怎麼會報錯不知道儲存引擎InnoDB。查看下引擎：

mysql> show engines;+--------------------+---------+-----------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                   | Transactions | XA   | Savepoints |+--------------------+---------+-----------------------------------------------------------+--------------+------+------------+| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         || PERFORMANCE_SCHEMA | YES     | Performance Schema                                        | NO           | NO   | NO         || MyISAM             | DEFAULT | MyISAM storage engine                                     | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                        | NO           | NO   | NO         || MRG_MYISAM         | YES     | Collection of identical MyISAM tables                     | NO           | NO   | NO         |+--------------------+---------+-----------------------------------------------------------+--------------+------+------------+5 rows in set (0.01 sec)

沒了，InnoDB真的沒了。我的心一下慌了，從來沒聽說過過引擎會自己消失的啊！再看看錯誤記錄檔怎麼說

130829  8:42:30 [Note] Slave I/O thread: connected to master 'slave@192.168.0.153:3306',replication started in log 'mysql-bin.000023' at position 232546566130829  8:42:30 [ERROR] Slave SQL: Error executing row event: 'Unknown storage engine 'InnoDB'', Error_code: 1286130829  8:42:30 [Warning] Slave: Unknown storage engine 'InnoDB' Error_code: 1286130829  8:42:30 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000023' position 225856952

看來InnoDB確實消失了。

遇到問題要先靠自己，這樣才會提高自己。想想事件的過程：InnoDB消失了。為什麼會消失？為什麼是今天消失？我都有做過什麼?好好想想。。。。

設定檔，沒錯，我夜裡調整了my.cnf，然後早上重啟mysql，然後InnoDB不見了。OK，原因找到了。趕緊將調整前後的設定檔對比，看看咱都改了哪些啊，發現了最可疑的一個配置項：

innodb_log_files_in_group = 3

改回調整前的值2，重啟mysql，開啟slave，複製正常了，查看引擎，InnoDB也回來了！萬幸啊。InnoDB的消失其實是因為修改配置導致mysql產生新的ib_logfile日誌與舊的ib_logfile日誌混亂引起的。整理下修改此配置項的操作步驟

service mysqld stopcd到資料目錄下mv ib_logfile* 到其他目錄下修改innodb_log_files_in_groupservice mysqld start

本文出自 “擁抱開源世界分享開源技術” 部落格，請務必保留此出處http://sourceworld.blog.51cto.com/3392968/1284763