Mysql更换MyISAM存储引擎为Innodb的操作记录总结

时间:2021-05-24

一般情况下,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的操作记录总结就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。

声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

相关文章