查看 MySQL 支持的存储引擎可以使用命令：SHOW ENGINES
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)
mysql>
重点关注 InnoDB、MyISAM、MEMORY这三种。
MySQL物理文件体系结构
- binlog 二进制日志文件
通过以下命令查看当前 binlog 文件列表：
mysql> show master logs;
ERROR 1381 (HY000): You are not using binary logging
mysql>
binlog 分为 statement 和 raw 格式。
- redo log
binlog 与 redo log 的分工不同，binlog主要做数据归档，redo log是奔溃恢复。 - innodb 共享表空间(系统表空间)和独立表空间，相关参数
innodb_file_per_table
查看是否开启了这个独立表空间选项：
mysql> show variables like 'innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
- undo log 回滚日志，如果事务回滚，需要依赖 undo 日志进行回滚操作。为避免
ibdata1共享表空间暴涨，建议将 undo log 单独存放。可以使用选项:innodb_undo_directory，例如：
mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | .\ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+--------------------------+------------+
5 rows in set, 1 warning (0.00 sec)
- 临时表空间，可以通过参数
innodb_temp_data_file_path查看，例如：
mysql> show variables like '%innodb_temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set, 1 warning (0.00 sec)
- errorlog 错误日志
mysql> show variables like 'log_error';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| log_error | .\KRAIT.err |
+---------------+-------------+
1 row in set, 1 warning (0.00 sec)
- slow.log, 如果配置了 MySQL 的慢查询日志， MySQL 就会将运行过程中的慢查询日志记录到 show_log 文件中。慢查询指的是执行时长超过
long_query_time值的 SQL，默认为 10s，参数如下：
mysql> show variables like '%slow_query_log%';
+---------------------+----------------+
| Variable_name | Value |
+---------------------+----------------+
| slow_query_log | ON |
| slow_query_log_file | KRAIT-slow.log |
+---------------------+----------------+
2 rows in set, 1 warning (0.00 sec)
还有
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
- general_log 通用查询日志，记录client 连接和运行的语句。
mysql> show variables like '%general%';
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| general_log | OFF |
| general_log_file | KRAIT.log |
+------------------+-----------+
2 rows in set, 1 warning (0.00 sec)
9.数据库路径，即系统数据库和用户数据库。
