Linux环境下 使MYSQL数据库支持InnoDB引擎

问题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 |

+---------------------------------+--------------------------------------------+

10 rows in set (0.00 sec)

从中可以查到mysql的错误日志的存放目录，退出MYSQL环境，进入错误日志目录查看具体原因如下：

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

Current 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: 9

Current database: *** NONE ***

+----------------------+-------+

| Variable_name | Value |

+----------------------+-------+

| have_dynamic_loading | YES |

+----------------------+-------+

表示可以动态加载插件

④ 放入插件文件，找到mysql存放插件的路径

mysql> show variables like "plugin_dir";

Connection id: 67

Current 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引擎

mysql> UNINSTALL PLUGIN innodb;

文章参考：http://www.linuxidc.com/Linux/2014-01/94776.htm