mysql - 查询时出现错误 'Unknown table engine ' InnoDB''。重启mysql后

Question

我在服务器 S1(mysql 版本 5.1.41-3ubuntu12.7-log)上有 mysql 数据库，我在服务器 S2(mysql 版本 5.1.54-1ubuntu4-log)上为此数据库创建了主从。
S1 上的数据库正在使用一个数据文件 (ibdata)。将数据库转储到 S2 后，我设置了 innodb_file_per_table=1。这使得每个表都有自己的 ibd 文件。现在一切顺利。
但是在 S2 上重新启动 mysql 后，我遇到了这个错误的问题:
查询时出现“未知表引擎‘InnoDB’”错误。默认数据库:MyDB当我尝试展示引擎时

show engines;+------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |+------------+---------+----------------------------------------------------------------+--------------+------+------------+| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         || MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         || MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       || ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |+------------+---------+----------------------------------------------------------------+--------------+------+------------+

innodb 未列出。
在错误日志中我可以看到这个:

InnoDB: Database physically writes the file full: wait...InnoDB: Cannot initialize created log files becauseInnoDB: data files are corrupt, or new data files wereInnoDB: created when the database was started previousInnoDB: time but the database was not shut downInnoDB: normally after that.111016  8:24:11 [ERROR] Plugin 'InnoDB' init function returned error.111016  8:24:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.111016  8:24:11 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=S2-relay-bin' to avoid this problem.

我曾尝试删除 ib_logfiles，但效果不佳。
以前有人遇到过这样的问题吗？？任何想法都非常感谢
谢谢

Answer 1

您可以删除 mysql 数据目录中名为 ib_logfile0 和 ib_logfile1 的 InnoDB 日志文件。不过不要删除 InnoDB 数据文件 (ibdata1)。

之后，InnoDB 会在你重启 mysqld 后尝试恢复。

查看主日志文件:

120413 17:34:47  InnoDB: Initializing buffer pool, size = 64.0M
120413 17:34:47  InnoDB: Completed initialization of buffer pool
120413 17:34:47  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 32 MB
InnoDB: Database physically writes the file full: wait...
120413 17:34:48  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 32 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120413 17:34:49  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.