Unknown table engine 'InnoDB'（mysql innodb 安装,配置）

最新推荐文章于 2023-02-08 11:19:15 发布

原创最新推荐文章于 2023-02-08 11:19:15 发布 · 238 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

本文介绍了解决MySQL环境中缺失InnoDB存储引擎的方法。包括通过配置文件启用InnoDB及手动安装InnoDB插件的过程。

导入sql出现如下问题：
Syntax error or access violation: 1286 Unknown table engine 'InnoDB'

原来是我的mysql里面，根本没有innodb存储引擎。我们可以用 show engines;或者show plugins;来查看

例如：
./configure --prefix=/usr/local/mysql/ --with-plugins=innobase,innodb_plugin
make && make install

如果何配置呢，其实将copy到/usr/local/mysql/my.cnf配置文件中[mysqld]下面有关innodb配置前面的#去掉就可以了。这是默认配置，可以根据个人需要进行修改

解决方法2：
如果你装的时候忘记了添加innodb，又不想重新编辑mysql来添加，这样也没有关系，innodb就是一个插件，安装好mysql后也是可以添加的。

1，查看一下，mysql配置是不是支持动态添加插件

查看复制打印
1.mysql> show variables like "have_%";
2.+----------------------+-------+
3.| Variable_name        | Value |
4.+----------------------+-------+
5.| have_compress        | YES   |
6.| have_crypt           | YES   |
7.| have_csv             | YES   |
8.| have_dynamic_loading | YES   |    //在这里是YES表示是支持的

2，添加插件

1.mysql> INSTALL PLUGIN INNODB SONAME 'ha_innodb.so'; //提示打不开文件，没有权限
2.ERROR 1126 (HY000): Can't open shared library '/usr/local/mysql/lib/mysql/plugin/ha_innodb.so' (errno: 13 cannot restore segment prot after reloc: Permission denied)

查看复制打印
# find . -type d -print |grep -i plugin //查看一下插件目录对不对
./lib/mysql/plugin

[root@localhost mysql]# find /usr/local/mysql -type d -print |grep -i plugin //查看一下插件目录
./lib/mysql/plugin

[root@localhost bin]# find / -name "ha_innodb.so"
/usr/local/mysql-5.1.24-rc/storage/innobase/.libs/ha_innodb.so
/usr/local/mysql/lib/mysql/ha_innodb.so
/usr/local/mysql/lib/mysql/plugin/ha_innodb.so
[root@localhost put_file]# cp /usr/local/mysql/lib/mysql/ha_innodb.* /usr/local/mysql/lib/mysql/plugin/

关闭selinux 执行 mysql> install plugin INNODB soname "ha_innodb.so";

若失败加载然再执行
[root@localhost huanglifang]# chcon -t texrel_shlib_t /usr/local/mysql/lib/mysql/plugin/ha_innodb.so （开启selinux 才能执行成功）
mysql> install plugin INNODB soname "ha_innodb.so";
开启selinux 才能执行成功

1.mysql> install plugin INNODB soname "ha_innodb.so";   （本例中一条即可）
2.mysql> install plugin INNODB_TRX soname "ha_innodb.so";
3.mysql> install plugin INNODB_LOCKS soname "ha_innodb.so";
4.mysql> install plugin INNODB_LOCK_WAITS soname "ha_innodb.so";
5.mysql> install plugin INNODB_CMP soname "ha_innodb.so";
6.mysql> install plugin INNODB_CMP_RESET soname "ha_innodb.so";
7.mysql> install plugin INNODB_CMPMEM soname "ha_innodb.so";
8.mysql> install plugin INNODB_CMPMEM_RESET soname "ha_innodb.so"
mysql> install plugin INNODB soname "ha_innodb.so";
mysql> install plugin INNODB_TRX soname "ha_innodb.so";
mysql> install plugin INNODB_LOCKS soname "ha_innodb.so";
mysql> install plugin INNODB_LOCK_WAITS soname "ha_innodb.so";
mysql> install plugin INNODB_CMP soname "ha_innodb.so";
mysql> install plugin INNODB_CMP_RESET soname "ha_innodb.so";
mysql> install plugin INNODB_CMPMEM soname "ha_innodb.so";
mysql> install plugin INNODB_CMPMEM_RESET soname "ha_innodb.so"安装好后，在用 show engines;或者show plugins;来查看

mysql> show engines;

+------------+---------+------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                    | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
| CSV        | YES     | CSV storage engine                                         | NO           | NO | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO | NO         |
| 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         |
+------------+---------+------------------------------------------------------------+--------------+-----+------------+

测试导入成功