我在服务器S1上有mysql DB(mysql版本5.1.41-3ubuntu12.7-log).

我在服务器S2上创建了这个DB的主从(mysql版本5.1.54-1ubuntu4-log).

S1上的DB使用一个数据文件(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 because
InnoDB: data files are corrupt, or new data files were
InnoDB: created when the database was started previous
InnoDB: time but the database was not shut down
InnoDB: 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,但这不起作用.
如果我删除ib_logfiles和ibdata文件,innodb将正常返回,但我无法访问我的innodb表,即.删除ibdata1并重启mysql后

desc article;
ERROR 1146 (42S02): Table 'MyDb.article' doesn't exist

 

我在my.cnf中的innodb配置如下：

innodb_file_per_table=1  
innodb_flush_method=O_DIRECT  
innodb_log_file_size=1G  
innodb_buffer_pool_size=4G  
innodb_data_file_path=ibdata1:10M:autoextend  
innodb_buffer_pool_size = 384M  
innodb_log_file_size=5M  
innodb_lock_wait_timeout = 18000

虽然桌子在那里!!

以前有人遇到过这样的问题吗？

任何想法都非常感谢

谢谢

解决方法:

我有个坏消息.

您不应该删除ibdata1文件.原因如下：

ibdata1包含四种类型的信息：

>表元数据
> MVCC数据
>数据页面(启用了innodb_file_per_table)
>索引页面(启用了innodb_file_per_table)

创建的每个InnoDB表都通过一些自动增量元数据功能为每个ibd文件分配了一个numercial id.内部表空间标识(ITSID)嵌入在.ibd文件中.根据维护的ITSID列表检查该数字,猜测在哪里,… ibdata1.

我也有一些好消息和一些坏消息.

可以重建ibdata1以获得正确的ITSID,但需要这样做才能完成.虽然我个人没有独自完成程序,但我在我雇主的网络托管服务中帮助客户完成此操作.我们一起考虑到了这一点,但是由于客户端给ibdata1打了个招呼,我让他做了大部分的工作(30个InnoDB表).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

切入追逐,here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

对不起,除了使用ITSID玩游戏之外,没有快速和简单的方法来恢复.ibd文件.

更新2011-10-17 06:19美国东部时间

以下是您问题中的原始innodb配置：

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000 

请注意innodb_log_file_size有两次.仔细看…

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000 

innodb_log_file_size的最后一个设置优先. MySQL期望启动日志文件为5M.当你试图启动mysqld时,你的ib_logfile0和ib_logfile1是1G.它看到了一个规模冲突,并采取了阻力最小的路径,这是禁用InnoDB.这就是InnoDB从show引擎中丢失的原因;谜团已揭开 ！！！

更新2011-10-17 11:07美国东部时间

该错误消息具有欺骗性,因为innodb_log_file_size小于日志文件(ib_logfile0和ib_logfile1),当时为1G.有趣的是：报告了腐败,因为文件预计为5M,文件更大.如果情况发生逆转且innodb日志文件小于my.cnf中声明的大小,您应该在错误日志中得到类似的内容：

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

在此示例中,日志文件已经存在为5M,innodb_log_file_size的设置更大(在本例中为32M).

对于这个特殊的问题,我责怪MySQL(eh Oracle [仍然讨厌说])因为不一致的错误消息协议.