MySQL 查询时出现“未知表引擎‘InnoDB’”错误。重启mysql后

Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

提问by Alaa

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: MyDBand when i try to show engines

我在服务器 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 后，我遇到了出现此错误的问题：
Error 'Unknown table engine 'InnoDB'' on query. Default database: 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 但这并没有奏效。
以前有人遇到过这样的问题吗？？任何想法都非常感谢
谢谢

回答by Nikl

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.

回答by Sych

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 服务，从 中删除日志文件 ( ib_logfile*) /var/lib/mysql，然后重新启动 MySQL 服务。只需在删除日志文件时 100% 确定 MySQL 实际上已关闭。

回答by Pathic

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

我认为如果你设置了 innodb 的 mysql 配置innodb_buffer_pool_size = 2G，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。

回答by user314104

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

回答by Martin

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: 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...