MySQL存储引擎的配置

MySQL存储引擎的配置

问题

本案例要求MySQL数据存储引擎的使用，完成以下任务操作：
可用的存储引擎类型
查看默认存储类型
更改表的存储引擎

步骤

实现此案例需要按照如下步骤进行。
步骤一：查看存储引擎信息
登入MySQL服务器，查看当前支持哪些存储引擎。
使用mysql命令连接，以root用户登入：

[root@dbsvr1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

执行SHOW ENGINES\G指令可列表查看，MySQL 5.6可用的存储引擎有9种（除最后的FEDERATED以外，其他8种都支持），其中默认采用的存储引擎为InnoDB：

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT  							//此存储引擎为默认
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO  								//此引擎不被支持
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

或者直接查看系统变量default_storage_engine 的值，也可确认默认采用的存储引擎是InnoDB：

mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

步骤二：修改默认存储引擎
在 mysql> 环境中，可以直接通过SET指令更改默认的存储引擎（只在本次连接会话过程中有效，退出重进即失效） 。比如临时修改为MyISAM，可执行下列操作：

mysql> SET default_storage_engine=MyISAM;  			//改用MyISAM引擎
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'default_storage_engine';  		//确认结果
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)

若希望直接修改MySQL服务程序所采用的默认存储引擎，应将相关设置写入配置文件/etc/my.cnf，并重启服务后生效。比如：

[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
default_storage_engine=MEMORY  							//改用MEMORY引擎

[root@dbsvr1 ~]# service mysql restart  				//重启服务
Shutting down MySQL.. 							[确定]
Starting MySQL.. 								[确定]

重新登入 mysql> 确认修改结果：

[root@dbsvr1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MEMORY |  				//默认引擎已修改
+------------------------+--------+
1 row in set (0.00 sec)

mysql> exit
Bye

为了避免后续实验障碍，测试完后记得恢复原状——移除默认引擎设置，或者将其修改为InnoDB即可：

[root@dbsvr1 ~]# vim /etc/my.cnf
[mysqld]
.. ..
#default_storage_engine=InnoDB
[root@dbsvr1 ~]# service mysql restart
Shutting down MySQL.. 								[确定]
Starting MySQL..

确认恢复结果（选项 -e 可调用指定的SQL操作后返回Shell命令行）：

[root@dbsvr1 ~]# mysql -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';"
Enter password:
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+