下面小編就為大家?guī)硪黄?a href="http://www.babyishan.com/?golink=aHR0cDovL3d3dy5waHAuY24vd2lraS8xMTYwLmh0bWw=" target="_blank" rel="noopener">mysql更換myisam存儲引擎為innodb的操作記錄總結。小編覺得挺不錯的，現在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
一般情況下，mysql會默認提供多種存儲引擎,可以通過下面的查看:
1）查看mysql是否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關閉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.cnfmysql
[root@dev?mysql5.1.57]#?vim?my.cnf ..... [mysqld]?????????????????????????????????????? ??//在這個配置區(qū)域添加下面一行，指定存儲引擎為innodb default-storage-engine?=?InnoDB
4）mysql/mysql/data目錄下的ib_logmysql0，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)
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
