MySQL 更换MyISAM存储引擎为Innodb的操作记录 - 散尽浮华 - 博客园
首页
新闻
博问
专区
闪存
班级
我的博客
我的园子
账号设置
简洁模式 ...
退出登录
注册
登录
散尽浮华
重剑无锋，大巧不工！
博客园
首页
新随笔
联系
管理
订阅
MySQL 更换MyISAM存储引擎为Innodb的操作记录
一般情况下，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 shutdownEnter 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] //在这个配置区域添加下面一行，指定存储引擎为innodbdefault-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)
*************** 当你发现自己的才华撑不起野心时，就请安静下来学习吧！***************
posted @
2016-12-28 18:27
散尽浮华
阅读(14952)
评论(1)
编辑
收藏
举报
刷新评论刷新页面返回顶部
Copyright 2022 散尽浮华
Powered by .NET 7.0 on Kubernetes