Mysql更換MyISAM存儲引擎為Innodb的操作記錄總結(jié)
一般情況下，mysql會默認(rèn)提供多種存儲引擎,可以通過下面的查看:
1）查看mysql是否安裝了innodb插件。
通過下面的命令結(jié)果可知，已經(jīng)安裝了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ā)現(xiàn)沒有安裝innodb插件,可以執(zhí)行下面語句進(jìn)行安裝：
mysql> install plugin innodb soname 'ha_innodb.so';
----------------------------------------------------------------------
2）查看mysql現(xiàn)在已提供什么存儲引擎:
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當(dāng)前默認(rèn)的存儲引擎:
mysql> show variables like '%storage_engine%'; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | storage_engine | MyISAM | +----------------+--------+ 1 row in set (0.00 sec)
4）看某個表用了什么引擎(在顯示結(jié)果里參數(shù)engine后面的就表示該表當(dāng)前用的存儲引擎):
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 '推薦的經(jīng)紀(jì)人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庫導(dǎo)成INNODB引擎格式的:
在備份出的xxx.sql文件中把ENGINE=MyISAM全換成ENGINE=INNODB
再次導(dǎo)入就可以了。
6）轉(zhuǎn)換表的命令:
mysql> alter table 表名 engine=innodb;
有上面可以查到看，本機mysql使用的存儲引擎是默認(rèn)的MyISAN，由于業(yè)務(wù)需要，先要將其存儲引擎改為Innodb。
操作記錄如下：
1）以安全模式關(guān)閉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的操作記錄總結(jié)就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持本站。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信