程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> Mysql更換MyISAM存儲引擎為Innodb的操作記錄總結

Mysql更換MyISAM存儲引擎為Innodb的操作記錄總結

編輯：關於MYSQL數據庫

一般情況下，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的操作記錄總結就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持。