问题1:在Linux服务器上创建数据库表的时候,明明设置表的引擎是InnoDB,将.sql导入数据库后总是自动变为MyISAM。
问题2:修改MYSQL数据库默认存储引擎
① [root@hyserver /]# vi /etc/my.cnf
在[mysqld]下面添加 default-storage-engine=INNODB 保存后退出。
② [root@hyserver /]# service mysqld restart
报错误:Starting MySQL. ERROR! Manager of pid-file quit without updating file.
③ 查找MYSQL启动错误日志如下:
mysql> show variables like "log%";
Connection id: 60
Current database: spice
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /www/wdlinux/mysql-5.1.69/var/hyserver.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
+---------------------------------+--------------------------------------------+
从中可以查到mysql的错误日志的存放目录,退出MYSQL环境,进入错误日志目录查看具体原因如下:10 rows in set (0.00 sec)
140819 10:21:30 [ERROR] Unknown/unsupported table type: INNODB
由此可以得知出现上述错误的原因是MySQL不支持InnoDB引擎。
问题解决:使MYSQL支持InnoDB数据库引擎
①查看MySQL是否支持InnoDB方法:
mysql> show variables like "ha%";+-------------------------+----------+| Variable_name | Value |+-------------------------+----------+| have_community_features | YES || have_compress | YES || have_crypt | YES || have_csv | YES || have_dynamic_loading | YES || have_geometry | YES || have_innodb | DISABLED || have_ndbcluster | NO || have_openssl | DISABLED || have_partitioning | NO || have_query_cache | YES || have_rtree_keys | YES || have_ssl | DISABLED || have_symlink | YES |+-------------------------+----------+14 rows in set (0.00 sec)
由上可知“have_innodb”是DISABLED,表示未启用,不支持InnoDB引擎
②确认是否已经安装InnoDB插件:
mysql> show plugins;
Connection id: 64Current database: spice+------------+--------+----------------+--------------+---------+| 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 |+------------+--------+----------------+--------------+---------+
③查看是否支持动态加载插件
mysql> show variables like "have_dynamic%";Connection id: 9Current database: *** NONE ***+----------------------+-------+| Variable_name | Value |+----------------------+-------+| have_dynamic_loading | YES |+----------------------+-------+
表示可以动态加载插件
④ 放入插件文件,找到mysql存放插件的路径
mysql> show variables like "plugin_dir";Connection id: 67Current database: spice+---------------+--------------------------------------------+| Variable_name | Value |+---------------+--------------------------------------------+| plugin_dir | /www/wdlinux/mysql-5.1.69/lib/mysql/plugin |+---------------+--------------------------------------------+
由此可知InnoDB插件的存放目录,cd切换到“/www/wdlinux/mysql-5.1.69/lib/mysql/plugin ”目录下,查看该目录下是否有
ha_innodb.so和ha_innodb_plugin.so这两个文件。(#若没有可以去网上下载与所安装mysql对应的版本,或者直接去mysql源码包中storage/innobase/.libs/ha_innodb.so 和storage/innodb_plugin/.libs/ha_innodb_plugin.so 复制到mysql的plugin目录中)
⑤动态安装InnoDB
mysql> INSTALL PLUGIN InnoDB SONAME 'ha_innodb.so';
⑥查看现在是否已经能够支持InnoDB:命令如下:
mysql> show plugins;
InnoDB | ACTIVE | STORAGE ENGINE | ha_innodb.so | GPL
mysql> show engines;
InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES
附:删除InnoDB引擎
文章参考: http://www.linuxidc.com/Linux/2014-01/94776.htmmysql> UNINSTALL PLUGIN innodb;