Unknown table engine ‘InnoDB’的解决方案

数据库迁移后,连接数据库突然报错:

ERROR 1286 (42000): Unknown table engine ‘InnoDB’

于是查看数据库引擎。

show engines;

发现竟然没有了Innodb引擎

+————+———+—————————————————————-+————–+——+————+
| 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数据库引擎应该是好的,应该是日志错误导致的,于是删掉数据库目录下的ib_logfile*文件(ibdata不要删,千万不要删错了!),然后重启数据库,一切恢复正常。

`

`

MySQL cannot allocate memory 无法启动解决方案

最近的mysql老是当掉,于是去看了下日志,发现有如下内容

160819 10:17:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins

160819 10:17:50 InnoDB: Compressed tables use zlib 1.2.3

160819 10:17:50 InnoDB: Using Linux native AIO

160819 10:17:50 InnoDB: Initializing buffer pool, size = 128.0M

160819 10:17:50 InnoDB: Completed initialization of buffer pool

160819 10:17:50 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

160819 10:17:50  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

根据日志的提示innodb buffer pool 的内存不够了。

mysql 默认的配置是128m

为了不让mysql 挂掉,我们可以调小这个值

在my.cnf 增加

innodb_buffer_pool_size = 64M

重启mysql 即可。

当然有这个提示,你也应该考虑增加物理内存了,

我使用的阿里云的机器,竟然发现没有swap分区,可以使用下面的方法进行创建

MySQL提示Unknown table engine ‘InnoDB’

上次由于磁盘空间不足,导致mysql无法启动,释放空间后,重启启动,发现程序连接的时候提示:

Unknown table engine ‘InnoDB’

什么情况,InnoDB未知

于是,先是查看了变量

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)

根本看不到了InnoDB了,然后又去看了下log日志

160125 11:40:04  InnoDB: Unable to open the first data file

InnoDB: Error in opening ./ibdata1

160125 11:40:04  InnoDB: Operating system error number 11 in a file operation.

InnoDB: Error number 11 means ‘Resource temporarily unavailable’.

InnoDB: Some operating system error numbers are described at

InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html

InnoDB: Could not open or create data files.

InnoDB: If you tried to add new data files, and it failed here,

InnoDB: you should now edit innodb_data_file_path in my.cnf back

InnoDB: to what it was, and remove the new ibdata files InnoDB created

InnoDB: in this failed attempt. InnoDB only wrote those files full of

InnoDB: zeros, but did not yet use them in any way. But be careful: do not

InnoDB: remove old data files which contain your precious data!

160125 11:40:04 [ERROR] Plugin ‘InnoDB’ init function returned error.

160125 11:40:04 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

/usr/libexec/mysqld: Disk is full writing ‘./mysql-bin.~rec~’ (Errcode: 28). Waiting for someone to free space… (Expect up to 60 secs delay for server to continue after freeing disk space)

发现了确实innodb日志出现了问题,于是按到以下操作步骤解决

1、关闭mysql进程

为了确保mysql进程全部结束掉,使用ps命令进行查看,如果存在,kill掉

2、进入mysql目录,然手删除ib_logdata的文件

3、重新开启mysqld进程