MySQL 体系结构-主要文件

接下来我们介绍一些 MySQL 的主要文件，文件分类如下：

• 配置文件
• 日志文件
• 表结构定义文件 & 数据文件

除了上面的文件，MySQL 的物理文件还包括 pid 文件、socket 文件，下面会逐一介绍。

配置文件

Linux 系统中，MySQL 默认的配置文件名是 my.cnf，一般存放在 /etc/my.cnf，/etc/mysql/my.cnf 目录中，MySQL 的常用的配置信息都存放在该文件中。

加载顺序

启动 MySQL 时，如果没有指定配置文件地址，则会按照默认顺序加载配置文件。通过如下命令查看 MySQL 默认加载配置文件的顺序。

mysql --help | grep my.cnf
#输出
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

默认加载顺序：/etc/my.cnf -> /etc/mysql/my.cnf -> /usr/local/etc/my.cnf -> ~/.my.cnf，如果多个配置文件中存在相同的参数，则后面配置文件中的参数会覆盖前面配置文件中的参数。

分组

MySQL 配置文件提供分组的功能，比如在 my.cnf 文件中经常可以看到 [client]、[mysqld] 等分组项。

[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=128M
[mysqldump]
quick

由于 MySQL 不同版本之间的配置参数存在一定的差异，如果我们要安装多个 MySQL 版本，就需要提供不同版本的 MySQL 配置文件，如何才能只使用一套配置文件呢？

MySQL 官方提供了如下方式来实现共用配置文件：https://dev.mysql.com/doc/refman/5.7/en/option-files.html

[mysqld-5.6]
......
[mysqld-5.7]
......

在定义 [mysqld] 分组时，加上 MySQL 的版本号即可，MySQL 启动时会根据版本号加载配置项。

引用

MySQL 配置文件还可以通过 !include /home/mydir/myopt.cnf 命令引用其他配置文件中的参数。

!include /home/mydir/myopt.cnf
......

通过命令设置变量

在不修改配置文件的情况下，MySQL 还可以通过命令来设置变量。

比如可以使用 GLOBAL 关键字设置全局变量，SET GLOBAL slow_query_log = 1;，设置慢查询开启，需要注意的是该设置仅对于新开启的会话才有效，对已经开启的会话不生效。

比如可以使用 SESSION 关键字设置会话变量，SET SESSION slow_query_log = 1;，设置当前会话慢查询开启，需要注意的是该设置只对当前会话有效，对其他会话无效。

设置好的变量，我们可以通过 SHOW 命令查看：SHOW VARIABLES LIKE '%slow%';

日志文件

MySQL 日志文件的官方介绍：https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

错误日志（Error log）

MySQL 错误日志记录 MySQL 运行过程中较为严重的警告和错误信息，以及 MySQL 每次启动和关闭的详细信息。MySQL 错误日志默认是开启的。

MySQL 错误日志在 my.cnf 配置文件中的参数是 log_error，默认名是：机器名.err，建议统一修改成一个固定的名称，例如 mysql.err。

[mysqld]
log_error = mysql.err

通过如下命令查看错误日志的配置信息。

show variables like '%log_err%';

另外，我们还可以将 MySQL 的错误日志配置到系统日志文件中。这种配置方式意义不大，了解一下吧。

[mysqld_safe]
syslog
# 加一个tag，日志文件以mysqld_开头
syslog_tag = stock#mysqld_stock

慢查询日志（Slow query log）

将运行超过某个时间阈值的 SQL 语句记录到文件，默认文件名是：机器名-slow.log。默认情况下，MySQL 是不开启慢查询日志的。

官方文档：https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

• MySQL 5.1 开始可以以毫秒为单位记录运行的 SQL 语句。
• MySQL 5.5 开始可以将慢查询保存到表。
• MySQL 5.6 开始可以更细粒度的记录慢查询。
• MySQL 5.7 将时区信息写入到慢查询日志。

慢查询日志的参数：
**

• long_query_time：假设我们设置为1秒钟，正好执行时间是1秒的 SQL 会被记录为慢查询么？不会被记录，MySQL 记录的是执行时间大于 long_query_time 的SQL。
• log_queries_not_using_indexes：将没有使用索引的 SQL 也记录到慢查询日志，防止一些新增的表前期数据量比较小，SQL 查询没有超过阈值，后面随着数据量增加后，SQL 执行变慢，防患于未来。
• log_throttle_queries_not_using_indexes：如果应用中有些 SQL 没有使用索引，那么这些查询会不断的记录到慢查询日志中，慢查询日志会变得越来越大，通过该参数可以限制每分钟记录没有使用索引 SQL 语句的个数。
• min_examined_row_limit：有些表只是一些字典表，数据量也不大，不需要使用索引，如果没有设置这个值，那么根据 log_queries_not_using_indexes 参数，会不断的把该条 SQL 记录到慢查询日志中，所以需要设置 min_examined_row_limit 参数，进一步控制慢查询日志记录的内容。
• log_slow_admin_statements：会把一些执行很长时间的 DDL 操作记录到慢查询日志中，如果没有设置该值，就没有办法根据慢查询日志定位一些执行很长时间的 DDL 操作。比如某一天发现数据库执行时间突然变慢了，一些平时执行正常的 SQL 也进入到慢查询日志中了，这个时候就要注意是否存在 DDL 操作了，这个操作是会锁表的。
• log_output：可以设置慢查询日志存放到表中，比如这里设置为 TABLE，如果要使用表来存储慢查询的话，需要注意的是，这个慢查询表是 CSV 存储引擎，需要手动将表改成 MyISAM 存储引擎（ALTER TABLE slow_log ENGINE = MYISAM;），改之前需要先将 MySQL 的慢查询日志功能关闭（SET GLOBAL slow_query_log = 0;）。

生产环境中 slow.log 的量是比较大的，通过 Linux 命令 vi 查看该日志文件是非常不方便的，MySQL 提供了 mysqldumpslow 的工具格式化显示 slow.log 日志内容。

通用查询日志（General query log）

可以记录数据库所有相关操作，比如客户端连接信息，执行语句的信息等，默认文件名是：机器名.log，因为通用查询日志功能是同步记录的，开启后性能明显下降。

那么，通过查询日志一般用来做什么呢？通用查询日志一般用来做审计用的，用于记录 MySQL 的所有操作，可以追踪用户的登录信息、操作信息等。比如公司有一个财务工资库，只有一个人有权限，这个时候就需要使用审计功能了。

开启通用查询日志后，MySQL 性能损耗太大，MySQL 官方企业版提供了 MySQL Enterprise Audit 插件，这个是需要收费的。Audit 插件是异步记录日志的，所以性能比较高，不会像通用查询日志这样性能损耗比较大。

MariaDB 官方提供了 Audit 插件，是免费的，可以兼容 MySQL。参考地址：

• https://mariadb.com/kb/en/mariadb-audit-plugin-log-settings/
• http://www.geoffmontee.com/using-the-mariadb-audit-plugin-with-mysql/

二进制日志（Binary log）

MySQL 的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的 MySQL 语句。二进制日志（binary log）中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。二进制日志（binary log）主要用于数据库恢复和主从复制。

/*删除所有二进制文件：*/
reset master
/*删除部分二进制文件：*/
purge master logs
/*查看是否启用二进制日志：*/
show variables like '%log_bin%';
/*查看所有的二进制参数*/
show variables like '%binlog%';
/*查看文件的位置*/
show variables like '%datadir%';
/*查看当前服务器所有的二进制日志文件*/
show binary logs;
show master logs;

表结构定义文件 & 数据文件

MySQL 的配置文件 my.cnf 中有一个参数 datadir，配置了 MySQL 数据文件的存放位置，如下图所示。

比如我们要看 engine 数据库的文件内容，如下图所示。

ibdata1 文件是系统表空间（数据文件）undo 段，文件存放在 datadir 目录下。

ib_logfile0、ib_logfile1 文件是 redlog 文件，文件存放在 datadir 目录下。

表结构定义文件

以 frm 结尾的文件就是表结构定义文件，每张表对应一个表结构定义文件，表结构定义文件是二进制文件。

数据文件

db.opt 文件记录这个库的默认使用的字符集和校验规，文件存放在所属数据库的目录下。

MyISAM

MYD 文件是 MyISAM 存储引擎专用，MYD 文件存放 MyISAM 表的数据（data)。每一张 MyISAM 表都会有一个 .MYD 文件，文件存放在所属数据库的目录下。

MYI 文件也是 MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息。每一张 MyISAM 表对应一个 .MYI 文件，文件存放在所属数据库的目录下。

InnoDB

IBD 文件和 IBDATA 文件存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。

• 独享表空间：使用 .ibd 文件来存放数据，且每一张 InnoDB 表对应一个 .ibd 文件，文件存放在所属数据库的目录下。
• 共享表空间：使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。

其他

pid 文件

pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id。

socket 文件

socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过 TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

参考：

MySQL体系结构

【MySQL】漫谈MySQL体系结构

Chapter 15 Alternative Storage Engines

MySQL5.7 四种日志文件

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/miwe1h 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。