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

散盡浮華發表於2016-12-28
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)

相關文章