mysql 不支持的引擎_MySQL不支持InnoDB存储引擎解决方法

工作中，不免会遇到前辈已经编译安装过的mysql，忽然发现mysql不支持innodb的存储引擎的问题，现在来看一下吧

一、先看mysql是否支持innodb存储引擎

mysql> show variables like 'ha%';

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

| Variable_name        | Value    |

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

| 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    | YES      |

| have_profiling      | YES      |

| have_query_cache    | YES      |

| have_rtree_keys      | YES      |

| have_ssl            | DISABLED |

| have_symlink        | YES      |

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

14 rows in set (0.00 sec)

have_innodb：值为DISABLED，表示未启用，值为no，表示不支持innodb存储引擎

mysql> show plugins;

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

| Name                  | Status  | Type          | Library | License |

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

| binlog                | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

| mysql_native_password | ACTIVE  | AUTHENTICATION | NULL    | GPL    |

| mysql_old_password    | ACTIVE  | AUTHENTICATION | NULL    | GPL    |

| MEMORY                | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

| MRG_MYISAM            | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

| CSV                  | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

| MyISAM                | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

| BLACKHOLE            | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

| FEDERATED            | DISABLED | STORAGE ENGINE | NULL    | GPL    |

| PERFORMANCE_SCHEMA    | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

| ARCHIVE              | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

| partition            | ACTIVE  | STORAGE ENGINE | NULL    | GPL    |

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

现在的mysql的确不支持innodb存储引擎

2.查看是否支持动态加载插件

mysql> show variables like 'have_dynamic%';

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

| Variable_name        | Value |

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

| have_dynamic_loading | YES  |

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

1 row in set (0.00 sec)

have_dynamic_loading：值为yes，表示动态加载mysql的插件

当使用源码编译安装时不能使用-with-mysqld-ldflags=all-static选项

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

mysql> show variables like 'plugin_dir';

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

| Variable_name | Value                        |

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

| plugin_dir    | /usr/local/mysql/lib/plugin/ |

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

#在该目录中查看是否已有ha_innodb.so和ha_innodb_plugin.so两个文件

[root@zhu2 mysql-5.1.39]# ll /usr/local/mysql/lib/plugin/ha_innodb.so

lrwxrwxrwx 1 mysql mysql 18 08-22 02:55 /opt/mysql/lib/mysql/plugin/ha_innodb.so -> ha_innodb.so.0.0.0

[root@zhu2 mysql-5.1.39]# ll /usr/local/mysql/lib/plugin/ha_innodb_plugin.so

lrwxrwxrwx 1 mysql mysql 25 08-22 02:55 /opt/mysql/lib/mysql/plugin/ha_innodb_plugin.so -> ha_innodb_plugin.so.0.0.0

#若没有可以去网上下载与所安装mysql对应的版本，或者直接去mysql源码包中storage/innobase/.libs/ha_innodb.so

storage/innodb_plugin/.libs/ha_innodb_plugin.so 复制到mysql的plugin目录中

4.添加动态安装加载

mysql>  INSTALL PLUGIN InnoDB SONAME 'ha_innodb.so';

Query OK, 0 rows affected (0.61 sec)

5.看现在是否支持innodb

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 inset(0.01 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 inmemory, useful fortemporary 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        |

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

5 rows inset(0.01 sec)

二、追加编译

1.删除innodb支持，应查看

mysql> UNINSTALL PLUGIN innodb;

Query OK, 0 rows affected (0.52 sec)

mysql> show engines;

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

| Engine    | Support | Comment                                                  | Transactions | XA  | Savepoints |

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

| CSV        | YES    | CSV storage engine                                        | NO          | NO  | NO        |

| MRG_MYISAM | YES    | Collection of identical MyISAM tables                    | NO          | NO  | NO        |

| MEMORY    | YES    | Hash based, stored inmemory, useful fortemporary tables | NO          | NO  | NO        |

| MyISAM    | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO          | NO  | NO        |

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

4 rows inset(0.00 sec)

2.重新编译

注：注意mysql的编译安装方式， 2.看mysql不支持或者未启用innodb存储引擎