欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-22 12:59:22
一般情况下,mysql会默认提供多种存储引擎,可以通过下面的查看: 1)查看mysql是否安装了innodb插件。 通过下面的命令结果可知,已经安装了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 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的操作记录总结就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。