如何更换MySQL默认存储引擎

Posted: 14. 11. 2013 | Author: QuenyWell | Category: Database

MySQL 5.5版本之前的默认存储引擎是MyISAM，优点是速度快，缺点是不支持事务性和外键，而且故障恢复的时间跟资料量的多少成正比。另一种MySQL存储引擎InnoDB开始逐渐取代MyISAM，它比MyISAM稳定，支持事务性和外链，故障恢复时间也相对稳定。
本文将介绍如何将MySQL默认存储引擎由MyISAM更改为InnoDB。

1. 查看MySQL默认存储引擎

登录MySQL服务器，运行show engines命令：

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)

可以看到MySQL支持多种存储引擎，其中默认(Default)引擎是MyISAM。

2. 更改默认存储引擎为InnoDB

在MySQL的配置文件/etc/my.cnf中[mysqld]字段后加入：

default-storage-engine = InnoDB

重启MySQL服务器：

# /etc/init.d/mysqld restart

登入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     | YES     | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | DEFAULT | 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)

（完）