Mysql更改MyISAM存储引擎为Innodb设置记录的示例代码分享

一般情况下，mysql会默认提供多种存储引擎,可以通过下面的查看:

1）查看mysql是否安装了innodb插件。

通过下面的命令结果可知，已经安装了innodb插件。

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现在已提供什么存储引擎:

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当前默认的存储引擎:

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 表名;

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 &

mysql> show variables like '%storage_engine%'; 
+----------------+--------+ 
  Variable_name   Value   
+----------------+--------+ 
  storage_engine   InnoDB   
+----------------+--------+ 
1 row in set (0.00 sec)

以上就是Mysql更换MyISAM存储引擎为Innodb操作记录的示例代码分享的详细内容，更多请关注php中文网其它相关文章！