错误'未知表引擎'InnoDB''查询。重启mysql后

I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log), i have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log).
the DB on S1 was using one data file (ibdata). after dumping the DB to S2 i set innodb_file_per_table=1. this made every table to have its own ibd file. now everything went fine and smoothly.
but after restarting mysql on S2, i faced a problem with getting this error:
Error 'Unknown table engine 'InnoDB'' on query. Default database: MyDB and when i try to show engines

我在服务器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 is not listed.
in error log i can see this:

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.

I have tried to delete ib_logfiles but this didn't work as well.
anybody faced such issue before?? any idea is highly appreciated
Thanks

我试图删除ib_logfiles，但这不起作用。有谁在面对这样的问题？任何想法都非常感谢谢谢

5 个解决方案

#1

You can delete the InnoDB log files in your mysql data directory called ib_logfile0 and ib_logfile1. Don't delete the InnoDB data file (ibdata1) though.

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

After that, InnoDB will try to recover after you restart mysqld.

之后，InnoDB将在您重启mysqld后尝试恢复。

look at main log file:

看看主日志文件：

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.

#2

Had similar issue after server hanging and restart.

服务器挂起并重启后有类似的问题。

Data is okay - the error message is very misleading.

数据没问题 - 错误信息非常误导。

Stop MySQL service, delete the log files (ib_logfile*) from /var/lib/mysql, and start MySQL service back. Just make 100% sure that MySQL is actually down when deleting log files.

停止MySQL服务，从/ var / lib / mysql中删除日志文件（ib_logfile *），然后启动MySQL服务。只需确保在删除日志文件时MySQL实际上已关闭。

#3

I think configuration of mysql for innodb if you set innodb_buffer_pool_size = 2G, innodb will not work.

我认为如果你设置innodb_buffer_pool_size = 2G的innodb的mysql配置，innodb将无法正常工作。

Usually it gives an error

通常它会出错

"Unknown table engine 'InnoDB".

if you select table with innodb system try innodb_buffer_pool_size = 1G.

如果你用innodb系统选择表尝试innodb_buffer_pool_size = 1G。

#4

Did you compile mysql with innodb? If you did, you should have gobs of lines referring to it when you run this:

你用innodb编译mysql了吗？如果你这样做了，你运行它时应该有大量的行引用它：

strings `which mysqld` | grep innodb

#5

I encountered the same issue. Nikl's answer helped me find the issue in the main log file. InnoDB was unable to allocate the required memory at startup. After cleaning some other processes everything started properly.

我遇到了同样的问题。 Nikl的回答帮助我在主日志文件中找到了问题。 InnoDB无法在启动时分配所需的内存。清理完一些其他过程后，一切正常。

Logs showed the following:

日志显示以下内容：

160219 9:20:23 InnoDB: Error: cannot allocate 12884918272 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 49601872 bytes. Operating system errno: 12 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. InnoDB: We keep retrying the allocation for 60 seconds...

160219 9:20:23 InnoDB：错误：无法分配12884918272字节的InnoDB：带malloc的内存！总分配内存InnoDB：由InnoDB 49601872字节组成。操作系统错误：12 InnoDB：检查是否应增加交换文件或InnoDB：操作系统的ulimits。 InnoDB：在FreeBSD上检查你是否用InnoDB编译了操作系统：一个足够大的最大进程大小。 InnoDB：请注意，在大多数32位计算机中，InnoDB进程：内存空间限制为2 GB或4 GB。 InnoDB：我们一直在重试分配60秒......

#1