Mysql更换MyISAM存储引擎为Innodb的操作记录总结
一般情况下，mysql会默认提供多种存储引擎,可以通过下面的查看:
1）查看mysql是否安装了innodb插件。
通过下面的命令结果可知，已经安装了innodb插件。
mysql>showplugins; +------------+--------+----------------+---------+---------+ |Name|Status|Type|Library|License| +------------+--------+----------------+---------+---------+ |binlog|ACTIVE|STORAGEENGINE|NULL|GPL| |partition|ACTIVE|STORAGEENGINE|NULL|GPL| |CSV|ACTIVE|STORAGEENGINE|NULL|GPL| |MEMORY|ACTIVE|STORAGEENGINE|NULL|GPL| |InnoDB|ACTIVE|STORAGEENGINE|NULL|GPL| |MyISAM|ACTIVE|STORAGEENGINE|NULL|GPL| |MRG_MYISAM|ACTIVE|STORAGEENGINE|NULL|GPL| +------------+--------+----------------+---------+---------+ 7rowsinset(0.00sec)
----------------------------------------------------------------------
如果发现没有安装innodb插件,可以执行下面语句进行安装：
mysql>installplugininnodbsoname'ha_innodb.so';
----------------------------------------------------------------------
2）查看mysql现在已提供什么存储引擎:
mysql>showengines; +------------+---------+------------------------------------------------------------+--------------+------+------------+ |Engine|Support|Comment|Transactions|XA|Savepoints| +------------+---------+------------------------------------------------------------+--------------+------+------------+ |MRG_MYISAM|YES|CollectionofidenticalMyISAMtables|NO|NO|NO| |CSV|YES|CSVstorageengine|NO|NO|NO| |MyISAM|DEFAULT|DefaultengineasofMySQL3.23withgreatperformance|NO|NO|NO| |InnoDB|YES|Supportstransactions,row-levellocking,andforeignkeys|YES|YES|YES| |MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables|NO|NO|NO| +------------+---------+------------------------------------------------------------+--------------+------+------------+ 5rowsinset(0.00sec)
3）查看mysql当前默认的存储引擎:
mysql>showvariableslike'%storage_engine%'; +----------------+--------+ |Variable_name|Value| +----------------+--------+ |storage_engine|MyISAM| +----------------+--------+ 1rowinset(0.00sec)
4）看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql>showcreatetable表名;
mysql>showcreatetablewx_share_log; +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |wx_share_log|CREATETABLE`wx_share_log`( `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'微信分享日志自增ID', `reference_id`int(11)NOTNULLCOMMENT'推荐的经纪人id', `create_time`datetimeNOTNULLCOMMENT'创建时间', PRIMARYKEY(`id`) )ENGINE=MyISAMAUTO_INCREMENT=13DEFAULTCHARSET=utf8| +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec)
5）如何将MyISAM库导成INNODB引擎格式的:
在备份出的xxx.sql文件中把ENGINE=MyISAM全换成ENGINE=INNODB
再次导入就可以了。
6）转换表的命令:
mysql>altertable表名engine=innodb;
有上面可以查到看，本机mysql使用的存储引擎是默认的MyISAN，由于业务需要，先要将其存储引擎改为Innodb。
操作记录如下：
1）以安全模式关闭mysql
[root@devmysql5.1.57]#mysqladmin-uroot-pshutdown
Enterpassword:
[root@devmysql5.1.57]#ps-ef|grepmysql
2）备份my.cnf
[root@devmysql5.1.57]#cpmy.cnfmy.cnf.old
3）修改my.cnf配置文件
[root@devmysql5.1.57]#vimmy.cnf
.....
[mysqld] //在这个配置区域添加下面一行，指定存储引擎为innodb
default-storage-engine=InnoDB
4）删除/mysql/data目录下的ib_logfile0，ib_logfile1。删除或剪切到别处都行。
[root@devvar]#mvib_logfile0ib_logfile1/tmp/back/
5）启动mysql，登陆mysql验证存储引擎是否已切换
[root@devvar]#/Data/app/mysql5.1.57/bin/mysqld_safe--defaults-file=/Data/app/mysql5.1.57/my.cnf&
mysql>showvariableslike'%storage_engine%'; +----------------+--------+ |Variable_name|Value| +----------------+--------+ |storage_engine|InnoDB| +----------------+--------+ 1rowinset(0.00sec)
以上这篇Mysql更换MyISAM存储引擎为Innodb的操作记录总结就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。