如何更换MySQL默认存储引擎

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

  1. 查看MySQL默认存储引擎

  2. 登录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。

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

  4. 在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)
    

    (完)


除非注明,科威网文章均为原创。转载请以链接形式标明本文地址。
本文地址:http://quenywell.com/how-to-change-mysql-default-database-engine/

Leave a Comment

电子邮件地址不会被公开。 必填项已用*标注