当前位置：首页 > 资讯 > 技术文档

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

时间：2020-11-06 23:10 编辑：来源：阅读：
扫一扫，手机访问

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

一般情况下，mysql会默认提供多种存储引擎,可以通过下面的查看: [b]1）查看mysql是否安装了innodb插件。 [/b] 通过下面的命令结果可知，已经安装了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'; ---------------------------------------------------------------------- [b]2）查看mysql现在已提供什么存储引擎:[/b]

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)

[b]3）查看mysql当前默认的存储引擎:[/b]

mysql> show variables like '%storage_engine%'; 
+----------------+--------+ 
| Variable_name | Value | 
+----------------+--------+ 
| storage_engine | MyISAM | 
+----------------+--------+ 
1 row in set (0.00 sec)

[b]4）看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): [/b] 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)

[b]5）如何将MyISAM库导成INNODB引擎格式的: [/b] 在备份出的xxx.sql文件中把ENGINE=MyISAM全换成ENGINE=INNODB 再次导入就可以了。 [b]6）转换表的命令: [/b] mysql> alter table 表名 engine=innodb; 有上面可以查到看，本机mysql使用的存储引擎是默认的MyISAN，由于业务需要，先要将其存储引擎改为Innodb。操作记录如下： [b]1）以安全模式关闭mysql [/b] [root@dev mysql5.1.57]# mysqladmin -uroot -p shutdown Enter password: [root@dev mysql5.1.57]# ps -ef|grep mysql [b]2）备份my.cnf [/b] [root@dev mysql5.1.57]# cp my.cnf my.cnf.old [b]3）修改my.cnf配置文件 [/b] [root@dev mysql5.1.57]# vim my.cnf ..... [mysqld] //在这个配置区域添加下面一行，指定存储引擎为innodb default-storage-engine = InnoDB [b]4）删除/mysql/data目录下的ib_logfile0，ib_logfile1。删除或剪切到别处都行。 [/b] [root@dev var]# mv ib_logfile0 ib_logfile1 /tmp/back/ [b]5）启动mysql，登陆mysql验证存储引擎是否已切换 [/b] [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的操作记录总结就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持编程素材网。

全部评论(0)

上一篇：mysql字符集和校对规则(Mysql校对集)
下一篇：mysqlsla慢查询分析工具使用笔记

资讯排行榜
更多>>