Mysql

InnoDB 不再可用

  • January 23, 2019

最近更換了伺服器,從 CentOS 5.8 轉到了 CentOS 6.4。我在我的新伺服器上安裝了 MySQL 5.1.69,並手動交換了 /var/lib/mysql。我不確定我的舊伺服器上的 MySQL 版本是什麼,但希望它是相同的。

當我嘗試查詢表時,我得到以下資訊:

ERROR 1286 (42000): Unknown table engine 'InnoDB'

不使用 InnoDB 的表可以正常工作。看起來我沒有載入 InnoDB。我使用yum install mysql mysql-server. 為什麼 InnoDB 沒有載入,我應該如何載入它?

編輯:

也許我應該手動更改 ib_logfile0 以使文件大小相同?

mysql> SHOW ENGINES;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                   | Transactions | XA   | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                     | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                        | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)

mysql>

/var/log/mysqld.log:

130622 17:05:19 [Note] /usr/libexec/mysqld: Shutdown complete

130622 17:05:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130622 17:06:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130622 17:06:09  InnoDB: Initializing buffer pool, size = 8.0M
130622 17:06:09  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
130622 17:06:09 [ERROR] Plugin 'InnoDB' init function returned error.
130622 17:06:09 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130622 17:06:09 [Note] Event Scheduler: Loaded 0 events
130622 17:06:09 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.69'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

似乎是一個很好的答案。這基本上表明您應該ib_logfile0自己刪除/重建。只需將文件移動到安全的地方——而不是在尋找它的 MySQL 目錄中——然後重新啟動伺服器。

需要注意的是:你真的有需要 InnoDB 的數據庫嗎?如果是這樣,您可以手動對每個 DB 進行 mydump 以獲取備份,然後從 MySQL 數據庫中刪除 DB,關閉 MySQL,移動/刪除ib_logfile0——我還建議移動/刪除and——ibdata1然後ib_logfile1重新啟動 MySQL .

然後檢查您的日誌以查看 InnoDB 是否已啟動。我打賭它會的。那時重新創建您擺脫的數據庫並重新導入數據。新的ibdata1,文件將被創建,一切都應該很好。ib_logfile0``ib_logfile1

引用自:https://serverfault.com/questions/517836