Mysql之新增innodb支援

在對mysql進行編譯安裝時，當安裝完成後有時會發現不支援innodb儲存引擎，這是因為編譯安裝時缺少支援innodb的引數：

--with-plugins=PLUGIN[,PLUGIN..]
Plugins to include in mysqld. (default is: none)
Must be a configuration name or a comma separated
list of plugins.
Available configurations are: none max max-no-ndb  all.
Available plugins are: partition archive blackhole
csv example federated heap ibmdb2i innobase
innodb_plugin myisam myisammrg ndbcluster.
--with-plugins=innobase 或者--with-plugins=all #這是在5.5版本前
-DWITH_INNOBASE_STORAGE_ENGINE=1 #這是在5.5以後版本，用cmake編譯時支援innodb所用的引數

然而，那些引數都是在編譯時應選的，對現在的問題也於事無補；下面介紹如何新增innodb支援。 一.動態載入innodb 檢視現在mysql到底是否支援innodb

mysql> show variables like "have_%";
+-------------------------+----------+
| 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             | NO       |
| 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)

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     |
+------------+--------+----------------+---------+---------+
5 rows in set (0.01 sec)
#可見現在的mysql確實不支援innodb儲存引擎

2.檢視是否支援動態載入外掛

mysql> show variables like "have_dynamic%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| have_dynamic_loading | YES   |
+----------------------+-------+
1 row in set (0.00 sec)
#當現實為yes時表示支援動態載入mysql外掛，該值一般為yes，當使用原始碼編譯安裝時不能使用–with-mysqld-ldflags=-all-static選項，以靜態方式編譯庫，這樣預設就會是yes。

3.放入外掛檔案

找到mysql存放外掛的目錄

mysql>  show variables like `plugin_dir`;
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| plugin_dir    | /opt/mysql/lib/mysql/plugin |
+---------------+-----------------------------+
1 row in set (0.00 sec)
#在該目錄中檢視是否已有ha_innodb.so和ha_innodb_plugin.so兩個檔案
[root@zhu2 mysql-5.1.39]# ll /opt/mysql/lib/mysql/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 /opt/mysql/lib/mysql/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 in set (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 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         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (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 in memory, useful for temporary tables | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)

2.重新編譯安裝 。。。。。。