一般情况下，mysql会默认提供多种存储引擎,可以通过下面的查看:
1）查看mysql是否安装了innodb插件。
通过下面的命令结果可知，已经安装了innodb插件。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> show plugins;
+
------------+--------+----------------+---------+---------+
|
Name
| Status | Type | Library | License |
+
------------+--------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| partition | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| CSV | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| MEMORY | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| InnoDB | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| MyISAM | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE |
NULL
| GPL |
+
------------+--------+----------------+---------+---------+
7
rows
in
set
(0.00 sec)
|
----------------------------------------------------------------------
如果发现没有安装innodb插件,可以执行下面语句进行安装：
mysql> install plugin innodb soname 'ha_innodb.so';
----------------------------------------------------------------------
2）查看mysql现在已提供什么存储引擎:
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show engines;
+
------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+
------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection
of
identical MyISAM tables |
NO
|
NO
|
NO
|
| CSV | YES | CSV storage engine |
NO
|
NO
|
NO
|
| MyISAM |
DEFAULT
|
Default
engine
as
of
MySQL 3.23
with
great performance |
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
|
+
------------+---------+------------------------------------------------------------+--------------+------+------------+
5
rows
in
set
(0.00 sec)
|
3）查看mysql当前默认的存储引擎:
|
1
2
3
4
5
6
7
|
mysql> show variables
like
'%storage_engine%'
;
+
----------------+--------+
| Variable_name | Value |
+
----------------+--------+
| storage_engine | MyISAM |
+
----------------+--------+
1 row
in
set
(0.00 sec)
|
4）看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> show
create
table
wx_share_log;
+
--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wx_share_log |
CREATE
TABLE
`wx_share_log` (
`id`
int
(11)
NOT
NULL
AUTO_INCREMENT COMMENT
'微信分享日志自增ID'
,
`reference_id`
int
(11)
NOT
NULL
COMMENT
'推荐的经纪人id'
,
`create_time` datetime
NOT
NULL
COMMENT
'创建时间'
,
PRIMARY
KEY
(`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13
DEFAULT
CHARSET=utf8 |
+
--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
5）如何将MyISAM库导成INNODB引擎格式的:
在备份出的xxx.sql文件中把ENGINE=MyISAM全换成ENGINE=INNODB
再次导入就可以了。
6）转换表的命令:
mysql> alter table 表名 engine=innodb;
有上面可以查到看，本机mysql使用的存储引擎是默认的MyISAN，由于业务需要，先要将其存储引擎改为Innodb。
操作记录如下：
1）以安全模式关闭mysql
[root@dev mysql5.1.57]# mysqladmin -uroot -p shutdown
Enter password:
[root@dev mysql5.1.57]# ps -ef|grep mysql
2）备份my.cnf
[root@dev mysql5.1.57]# cp my.cnf my.cnf.old
3）修改my.cnf配置文件
[root@dev mysql5.1.57]# vim my.cnf
.....
[mysqld] //在这个配置区域添加下面一行，指定存储引擎为innodb
default-storage-engine = InnoDB
4）删除/mysql/data目录下的ib_logfile0，ib_logfile1。删除或剪切到别处都行。
[root@dev var]# mv ib_logfile0 ib_logfile1 /tmp/back/
5）启动mysql，登陆mysql验证存储引擎是否已切换
[root@dev var]# /Data/app/mysql5.1.57/bin/mysqld_safe --defaults-file=/Data/app/mysql5.1.57/my.cnf &
|
1
2
3
4
5
6
7
|
mysql> show variables
like
'%storage_engine%'
;
+
----------------+--------+
| Variable_name | Value |
+
----------------+--------+
| storage_engine | InnoDB |
+
----------------+--------+
1 row
in
set
(0.00 sec)
|
