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 separatedlist of plugins.Available configurations are: none max max-no-ndb  all.Available plugins are: partition archive blackholecsv example federated heap ibmdb2i innobaseinnodb_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兩個檔案[[email protected] mysql-5.1.39]# ll /opt/mysql/lib/mysql/plugin/ha_innodb.solrwxrwxrwx 1 mysql mysql 18 08-22 02:55 /opt/mysql/lib/mysql/plugin/ha_innodb.so -> ha_innodb.so.0.0.0[[email protected] mysql-5.1.39]# ll /opt/mysql/lib/mysql/plugin/ha_innodb_plugin.solrwxrwxrwx 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.sostorage/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.重新編譯安裝
。。。。。。

Mysql 之 添加innodb支援