At work, I will inevitably encounter mysql compiled and installed by my predecessors. I suddenly found that mysql does not support the innodb Storage engine. Let's take a look.
1. Check whether mysql supports the innodb Storage engine.
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: The value is DISABLED, indicating that it is not enabled. The value is no, indicating that the innodb Storage engine is not supported.
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 |
+ ----------------------- + ---------- + ---------------- + --------- +
Currently, mysql does not support the innodb Storage engine.
2. Check whether the plug-in can be dynamically loaded.
Mysql> show variables like 'have _ dynamic % ';
+ ---------------------- + ------- +
| Variable_name | Value |
+ ---------------------- + ------- +
| Have_dynamic_loading | YES |
+ ---------------------- + ------- +
1 row in set (0.00 sec)
Have_dynamic_loading: The value is yes, indicating that the mysql plug-in is dynamically loaded.
You cannot use the-with-mysqld-ldflags = all-static option when compiling and installing with source code.
3. Put the plug-in file and find the path for mysql to store the plug-in.
Mysql> show variables like 'in in _ dir ';
+ --------------- + ------------------------------ +
| Variable_name | Value |
+ --------------- + ------------------------------ +
| Plugin_dir |/usr/local/mysql/lib/plugin/|
+ --------------- + ------------------------------ +
# Check whether the ha_innodb.so and ha_innodb_plugin.so files exist in this directory.
[Root @ zhu2 mysql-5.1.39] # ll/usr/local/mysql/lib/plugin/ha_innodb.so
Lrwxrwxrwx 1 mysql 18 08-22/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 25 08-22/opt/mysql/lib/mysql/plugin/ha_innodb_plugin.so-> ha_innodb_plugin.so.0.0.0
# If not, you can download the version corresponding to the installed mysql online, or directly go to storage/innobase/. libs/ha_innodb.so In the mysql source package.
Copy storage/innodb_plugin/. libs/ha_innodb_plugin.so to the mysql plugin directory.
4. Add dynamic installation and Loading
Mysql> install plugin InnoDB SONAME 'Ha _ innodb. so ';
Query OK, 0 rows affected (0.61 sec)
5. Check whether innodb is supported.
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 |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES |
| MEMORY | YES | Hash based, stored inmemory, useful fortemporary tables | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO |
+ ------------ + --------- + ---------------------------------------------------------- + -------------- + ------ + ------------ +
5 rows inset (0.01 sec)
Ii. append Compilation
1. Delete innodb support. Check it.
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 |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO |
| MEMORY | YES | Hash based, stored inmemory, useful fortemporary tables | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO |
+ ------------ + --------- + --------------------------------------------------------- + -------------- + ------ + ------------ +
4 rows inset (0.00 sec)
2. recompile
Note: Pay attention to the mysql compilation and installation methods,
2. Check that mysql does not support or does not enable the innodb Storage engine.
Recommended reading:
Startup, shutdown, and restoration of the InnoDB Storage Engine
MySQL InnoDB independent tablespace Configuration
Architecture of MySQL Server layer and InnoDB Engine Layer
InnoDB deadlock Case Analysis
MySQL Innodb independent tablespace Configuration