MySQL数据引擎InnoDB与MyISAM的互相转换

2017/02/07技术

MySQL（或者社区开源fork的MariaDB）5.5以上支持InnoDB引擎，并将其作为默认数据库引擎。InnoDB带来很多改进，但是对系统资源占用明显增加。
之前附中校友回访的官网被人DDOS搞挂过一次数据库，后来又因为一个小小的插件导致数据库再次挂掉，查询日志后发现是内存占用过高，InnoDB分配不到内存了于是导致整个数据库shutdown。
在暂时不打算升级服务器且访问量并没有大到非要InnoDB不可的情况下，决定把全站数据库切回老当益壮的MyISAM。

PS. 如果内存没有1G不要玩MySQL5.6+，内存512M或以下只是挂个WordPress的老老实实用MyISAM。内存正好1G但是又跑了好几个东西的且偶尔会有点流量的（比如我）也乖乖用MyISAM吧。

下面说说具体方法。

方法一：敲命令（网上找的）
进入MySQL命令行模式，然后执行以下指令（以数据库wordpress为例）：

1
2
3
4

USE wordpress;
SHOW TABLES;
ALTER TABLE wp_commentmeta ENGINE=MYISAM;
ALTER TABLE wp_comments ENGINE=MYISAM;

SHOW TABLES可以显示出wordpress下全部表的名字，接下来两行将把wp_commentmeta和wp_comments这两个表转换成MyISAM引擎，剩下的几个表也请用同样的方式转换完毕。引擎转换过程中将把数据库锁定，小的数据库转换很快，但是大数据库转换可能花费时间比较长，因此尽量选择访问低峰时间段进行。

方法二：PHP
上面这个敲命令的方法得一个个的表敲命令，我是不愿意的，于是有了下面这个PHP脚本。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<?php
//连接数据库
$host='localhost';
$user='admin'; //数据库用户名
$passwd='admin'; //密码
$db='admin'; //数据库名称
$link= mysql_connect($host,$user,$passwd);
if (!$link) {
die('Could not connect: '.mysql_error() );
}

mysql_select_db( $db,$link ) or die ('can\'t use'. $db . mysql_error());
$result=mysql_query("show tables ") or die( mysql_error());
while($row=mysql_fetch_row($result)){
#echo $row[0];
#var_dump($row);
echo "$row[0] \n";
mysql_query("alter table $row[0] ENGINE=MYISAM") or die (mysql_error());
}
#var_dump($result);
mysql_close($link);
?>

执行并等待处理完毕即可。
PS.若将其中18行的MYISAM更改为INNODB则是把数据引擎从MyISAM切换到InnoDB。

进行完表的转换之后，可以把InnoDB引擎关闭并将MyISAM设为今后MySQL的默认引擎，编辑/etc/my.cnf（或其他路径）文件，在[mysqld]区段下加入下面两行：