导入sql出现如下问题:
Syntax error or access violation: 1286 Unknown table engine 'InnoDB'
原来是我的mysql里面,根本没有innodb存储引擎。我们可以用 show engines;或者show plugins;来查看
1.mysql> show plugins;
2.+------------+--------+----------------+---------+---------+
3.| Name | Status | Type | Library | License |
4.+------------+--------+----------------+---------+---------+
5.| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
6.| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
7.| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
8.| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
9.| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
10.+------------+--------+----------------+---------+---------+
11.5 rows in set (0.00 sec)
mysql> show plugins;
+------------+--------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+--------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
+------------+--------+----------------+---------+---------+
5 rows in set (0.00 sec)
解决方法1(推荐使用):在configure的时候加上--with-plugins=innobase 如果要添多个插件,请用半角逗号隔开
例如:
./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 plugins;
+------------+--------+----------------+--------------+---------+
| Name | Status | Type | Library | License |
+------------+--------+----------------+--------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | ha_innodb.so | GPL |
+------------+--------+----------------+--------------+---------+
6 rows in set (0.02 sec)
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 |
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
测试导入成功