mysql启用innodb

发表于 更新于 分类于

mysql 5.1默认只启用MyISAM, 使用INNODB要做一下修改:

1
2
3
4
5
=== InnoDB Storage Engine ===
Plugin Name: innobase
Description: Transactional Tables using InnoDB
Supports build: static and dynamic
Configurations: max, max-no-ndb

mysql 5.1版本之后,要使用innodb有两种方法:

(1).静态编译

编译的时候加入–with-plugin-innobase

(2).动态扩展

启动mysqld后:

1
2
mysql> INSTALL PLUGIN innodb SONAME 'ha_innodb.so'
mysql> show plugin

就可以看见了,动态的话plugin_dir要指定正确:

1
2
[mysqld]
plugin_dir=/path/to/plugin/directory

最后,通过动态方式,解决了这个转换问题. 查看mysql 5.1 是否安装了innodb插件(mysql 5.0使用: show variables like ‘have_%’;):

1
2
3
4
5
6
7
8
9
10
11
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.00 sec)

发现没有安装 INNODB,执行下面语句:

1
mysql> install plugin innodb soname 'ha_innodb.so';

再次查看:

1
2
3
4
5
6
7
8
9
10
11
12
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.00 sec)

查看表的类型:

1
2
mysql> use web_db;
mysql. show tablse;

随便找个表看类型:

1
mysql. show create table sys_user;

查看是否有以下字段:

1
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

如何将MyISAM库 导成 INNODB: 在备份出的 xxx.bak文件中 把 ENGINE=MyISAM 全换成 ENGINE=INNODB 再次导入就可以了. 转换表的命令:

1
alter table POD engine=innodb;