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)

----------------------------------------------------------------------
如果發(fā)現沒有安裝innodb插件,可以執(zhí)行下面語句進行安裝：

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 '創(chuàng)建時間', 
 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，由于業(yè)務需要，先要將其存儲引擎改為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]                                      
  //在這個配置區(qū)域添加下面一行，指定存儲引擎為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中文網其它相關文章！