MySQL
都知道 MySQL 是基于磁盘存储的数据库，因此其配置及数据肯定是存在磁盘中的。但 MySQL 到底有哪些相关的磁盘文件，它们的作用又是什么呢？相信不少人还不是很了解，来介绍一下 MySQL 文件体系的六大文件。
MySQL 相关的配置及数据文件主要有如下 6 个，分别是：

  1. 参数配置文件
  2. 日志文件
  3. socket 文件
  4. pid 文件
  5. MySQL 表结构文件
  6. 存储引擎文件

    参数配置文件

    参数配置文件即 MySQL 实例启动时，数据库会去读取的一个配置参数文件，用来寻找数据库的各种文件所在位置及初始化参数，一般这个文件名称为 my.cnf。
    MySQL 数据库中的参数分为两类，分别是：动态参数和静态参数。 动态参数意味着可以在 MySQL 实例运行过程中进行修改，静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读的。

    查看参数值

    参数文件里面都是一个个的 key-value 键值对，可以通过 SHOW VARIABLES 命令查看对应参数的值，SHOW VARIABLES 命令的语法如下：
    1. SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE] '';
    默认情况下查看的是 SESSION 级别的变量，例如下面的例子查看 global 级别 innodb_buffer_pool_size 参数的值。
    1. mysql> show global variables like 'innodb_buffer_pool_size%';
    2. +-------------------------+-----------+
    3. | Variable_name           | Value     |
    4. +-------------------------+-----------+
    5. | innodb_buffer_pool_size | 134217728 |
    6. +-------------------------+-----------+
    7. 1 row in set (0.00 sec)

    设置参数值

    可以通过 SET 命令对动态参数进行修改，SET 命令的语法如下：
    1. SET [global | session] system_var_name = expr
    global 和 session 关键字，表示该参数的修改影响范围是当前会话还是整个实例的生命周期。但要注意的是有些参数只能在会话中修改，例如 autocommit。而有些参数既可以在会话中修改又可以在整个实例的生命周期内生效，如 read_buffer_size。
    提示：想知道 MySQL 所有动态变量的可修改范围，可以参考 MySQL 官方手册的 Dynamic System Variables 的相关内容。
    例如下面例子修改 read_buffer_size 变量的全局值为 111000 字节，可以看到全局级别的变量确实变了，但是会话级别的变量没有变。
    1. // 设置全局 read_buffer
    2. mysql> set global read_buffer_size=111000;
    3. Query OK, 0 rows affected, 1 warning (0.00 sec)
    4. // 查看全局变量
    5. mysql> show global variables like 'read_buffer_size';
    6. +------------------+--------+
    7. | Variable_name    | Value  |
    8. +------------------+--------+
    9. | read_buffer_size | 110592 |
    10. +------------------+--------+
    11. 1 row in set (0.00 sec)
    12. // 查看会话变量
    13. mysql> show session variables like 'read_buffer_size';
    14. +------------------+--------+
    15. | Variable_name    | Value  |
    16. +------------------+--------+
    17. | read_buffer_size | 118784 |
    18. +------------------+--------+
    19. 1 row in set (0.00 sec)

    日志文件

    日志文件记录了影响 MySQL 数据库的各种类型活动。MySQL 数据库中常见的日志文件有：
  • 错误日志
  • 二进制日志
  • 慢查询日志
  • 查询日志

这些日志文件可以帮助我们对 MySQL 数据库的运行状态进行诊断，从而更好地进行数据库层面的优化。

错误日志

错误日志文件对 MySQL 的启动、运行、关闭过程做了记录，遇到问题时应该首先查看这个文件以便定位问题。这个文件不仅记录了所有的错误信息，也记录了一些警告信息和正确的信息。我们可以通过查看 log_error 参数的值来定位该文件，例如：

  1. mysql> show variables like 'log_error'\G;
  2. *************************** 1. row ***************************
  3. Variable_name: log_error
  4.         Value: /var/log/mysqld.log
  5. 1 row in set (0.00 sec)

默认情况下错误文件的文件名为服务器的主机名，我这里在配置文件指定了错误日志文件的地址，因此这里显示的是 mysqld.log。

慢查询日志

慢查询日志可以帮助我们定位存在问题的 SQL 语句，从而进行 SQL 语句优化。与慢查询有两个相关的参数：

记录慢查询

与记录慢查询相关的两个参数分别是：**slow_query_log** 和 **long_query_time**。
slow_query_log 表示是否记录慢查询。默认情况下，MySQL 并不启动慢查询日志。如果需要记录慢查询日志，我们需要手工将这个值设置为 ON。
long_query_time 设置慢查询时长阈值。当 slowquery_log 设置为 ON 之后，如果 SQL 运行时间大于 long_query_time，那么该 SQL 就会被记录下来，这个值默认是 10 秒。从 MySQL 5.1 开始，long_query_time 开始以微妙为单位记录 SQL 语句运行的时间，之前用的是秒作为单位记录。
slow_query_log_file 记录了慢查询文件的位置。
提示：`_slow_query_log_ 为 MySQL 8.0 的参数名，在之前的版本中可能是 _log_slow_queries` 参数。

记录未使用索引的查询

除了记录普通的慢查询之外，MySQL 还提供了两个参数来记录未使用索引的查询，它们分别是：**log_queries_not_using_indexes** 和 **log_throttle_queries_not_using_indexes**。
log_queries_not_using_indexes 表示是否记录未使用索引的慢查询。如果运行的 SQL 语句没有使用索引，那么 MySQL 会将其记录到慢查询日志文件中。
log_throttle_queries_not_using_indexes 则是在 MySQL 5.6.5 版本新增的一个参数，表示每分钟允许记录到 slow log 的且未使用索引的 SQL 语句次数。该值默认为 0，表示没有限制。
在生产环境下，如果没有使用索引，那么此类 SQL 语句会频繁地被记录到 slow log，从而导致 slow log 文件大小不断增加，可以通过调整此参数进行配置。

慢查询表

MySQL 5.1 开始可以将慢查询的日志记录到 slow_log 表中，方便我们的查询。MySQL log_output 参数设置了慢查询的存储方式，默认情况下是 FILE，即使用文件存储。如果要让 MySQL 将慢查询记录到 slow_log 表中，只需将 log_output 参数设置为 TABLE 即可。

  1. // 默认为 FILE 格式
  2. mysql> show variables like 'log_output';
  3. +---------------+-------+
  4. | Variable_name | Value |
  5. +---------------+-------+
  6. | log_output    | FILE  |
  7. +---------------+-------+
  8. 1 row in set (0.01 sec)
  9. // 设置为 TABLE 格式
  10. mysql> set global log_output='TABLE';
  11. Query OK, 0 rows affected (0.01 sec)
  12. mysql> show variables like 'log_output';
  13. +---------------+-------+
  14. | Variable_name | Value |
  15. +---------------+-------+
  16. | log_output    | TABLE |
  17. +---------------+-------+
  18. 1 row in set (0.00 sec)

下面的例子模拟了一个慢查询，并成功在 slow_log 慢查询表看到了其记录。

  1. // 睡眠 10 秒
  2. mysql> select sleep(10);
  3. +-----------+
  4. | sleep(10) |
  5. +-----------+
  6. |         0 |
  7. +-----------+
  8. // 查询慢查询表
  9. mysql> select * from mysql.slow_log\G;
  10. *************************** 1. row ***************************
  11.     start_time: 2021-07-31 18:07:05.777780
  12.      user_host: root[root] @ localhost []
  13.     query_time: 00:00:10.000251
  14.      lock_time: 00:00:00.000000
  15.      rows_sent: 1
  16.  rows_examined: 1
  17.             db:
  18. last_insert_id: 0
  19.      insert_id: 0
  20.      server_id: 1
  21.       sql_text: 0x73656C65637420736C65657028313029
  22.      thread_id: 18
  23. 1 row in set (0.00 sec)

上面的 sqltext 参数记录了对应的 SQL 语句，其显示的是 16 进制存储的文本，转义过来就是 select sleep(10)。
提示：16 进制转换文本工具：16 进制转换，16 进制转换文本字符串，在线 16 进制转换 | 在线工具。_

查询日志

查询日志记录了所有对 MySQL 数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log，可以通过 general_log_file 参数查看查询日志文件的地址。

  1. mysql> show variables like 'general_log_file';
  2. +------------------+--------------------------------------------+
  3. | Variable_name    | Value                                      |
  4. +------------------+--------------------------------------------+
  5. | general_log_file | /var/lib/mysql/iZwz9ahgpvp1o991rmqnzsZ.log |
  6. +------------------+--------------------------------------------+
  7. 1 row in set (0.01 sec)

与慢查询日志一样，默认情况下查询日志也是关闭的。需要将 general_log 参数设置为 ON 来开启查询日志，这样 MySQL 才会记录查询日志。
与慢查询日志一样，MySQL 从 5.1 版本开始，可以通过 mysql.general_log 查询到查询日志。

  1. mysql> select * from mysql.general_log limit 1\G;
  2. *************************** 1. row ***************************
  3.   event_time: 2021-07-31 18:23:35.900988
  4.    user_host: root[root] @ localhost []
  5.    thread_id: 32
  6.    server_id: 1
  7. command_type: Query
  8.     argument: show variables like 'general_log'

二进制日志（binlog）

binlog，即二进制日志（binary log）的意思，其记录了 MySQL 执行的原始语句。
二进制日志（binary log）记录了对 MySQL 数据库执行更改的所有操作，但是不包括 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改。但是某些没有导致数据库发生变化的操作，也有可能会写入二进制日志。例如某条 UPDATE 语句没有找到符合的记录，但是其还是会被记录到 binlog 文件中。
MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。
sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。
我想你肯定会问，为什么会有两份日志呢？
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统 —— 也就是 redo log 来实现 crash-safe 能力。

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是 “在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 “给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写” 是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

    二进制日志参数

    二进制日志文件默认情况下并没有启动，需要手动指定参数来启动。与二进制日志相关的配置文件参数为：
  • log-bin：是否启动二进制日志。
  • log_bin_basename：二进制日志所在文件夹。
  • log_bin_index：二进制的索引文件，用于存储过往产生的二进制日志序号。
  • max_binlog_size：指定单个二进制日志文件的最大值。如果超过该值，则产生新的二进制日志文件，后缀名 + 1，并记录到 .index 文件。从 MySQL 5.0 开始的默认值为 1G，之前版本默认为 1.1G。
  • binlog_cache_size：binlog 缓冲区大小。当使用事务时，所有未提交的二进制日志会记录到一块缓存中去，等事务提交时将缓存中的二进制日志写入二进制日志文件，而这个缓冲区大小由 binlog_cache_size 决定，默认大小为 32K。

当一个事务的记录大于设定的 binlog_cache_size 时，MySQL 会把缓冲中的日志写入一个临时文件中，这样会降低效率，因此该值不能设置得太小。通过 SHOW GLOBAL STATUS 命令可以查看 binlog_cache_use、binlog_cache_dis_use 的状态，可以判断当前 binlog_cache_size 的设置是否合适。

  1. mysql> show variables like 'binlog_cache_size';
  2. +-------------------+-------+
  3. | Variable_name     | Value |
  4. +-------------------+-------+
  5. | binlog_cache_size | 32768 |
  6. +-------------------+-------+
  7. 1 row in set (0.00 sec)
  9. mysql> show global status like 'binlog_cache%';
  10. +-----------------------+-------+
  11. | Variable_name         | Value |
  12. +-----------------------+-------+
  13. | Binlog_cache_disk_use | 7     |
  14. | Binlog_cache_use      | 8     |
  15. +-----------------------+-------+
  16. 2 rows in set (0.00 sec)

二进制文件格式

从 MySQL 5.1 版本开始引入了 binlog_format 参数，其有三个取值，分别是：STATEMENT、ROW、MIXED。
STATEMENT 格式和之前的 MySQL 版本一样，二进制日志文件记录的是日志的逻辑 SQL 语句。ROW 格式下，二进制日志记录的是标的行更改情况。在 MIXED 格式下，MySQL 默认采用 STATEMENT 格式进行二进制日志文件的记录，但是在一些情况下会使用 ROW 格式。一般情况下，我们将参数 binlog_format 设置为 ROW，这可以为数据库的恢复和复制带来更好的可靠性。但 ROW 格式的坏处时，其会导致二进制日志文件大小较大。

查看二进制日志文件

要查看二进制日志文件，需要通过 MySQL 提供的工具 mysqlbinlog。mysqlbinlog 是一个查看 mysql 二进制日志的工具，可以把 mysql 上面的所有操作记录从日志里导出，这个工具默认的安装路径为：/usr/local/mysql/bin/mysqlbinlog。
一般的 statement 格式的二进制文件，用下面命令就可以

  1. mysqlbinlog mysql-bin.000001

如果是 row 格式，加上 -v 或者 -vv 参数就行，如

  1. mysqlbinlog -vv mysql-bin.000001

套接字文件

在 UNIX 系统下本地连接 MySQL 可以采用 UNIX 域套接字方式，这种方式需要一个套接字（socket）文件。套接字文件可由参数 socket 控制，如下图所示。

  1. mysql> show variables like 'socket'\G;
  2. *************************** 1. row ***************************
  3. Variable_name: socket
  4.         Value: /var/lib/mysql/mysql.sock
  5. 1 row in set (0.00 sec)
  6. // 查看 socket 文件属性
  7. [root@xx ~]# ll /var/lib/mysql/mysql.sock
  8. srwxrwxrwx 1 mysql mysql 0 Jul 31 17:31 /var/lib/mysql/mysql.sock

pid 文件

在 MySQL 实例启动时，会将自己的进程 ID 写入一个文件中 —— 该文件即 pid 文件。该文件可由参数 pid_file 控制，默认位于数据库目录下，文件名为 主机名.pid。

  1. // 查看 pid 文件地址
  2. mysql> show variables like 'pid_file'\G;
  3. *************************** 1. row ***************************
  4. Variable_name: pid_file
  5.         Value: /var/run/mysqld/mysqld.pid
  6. 1 row in set (0.00 sec)
  7. // 查看 pid 文件内容
  8. [root@iZwz9ahgpvp1o991rmqnzsZ ~]# cat /var/run/mysqld/mysqld.pid
  9. 20699

表结构定义文件

MySQL 数据存储是根据表进行的，每个表都会有与之对应的文件。无论采用哪种存储引擎，MySQL 都会有一个以 frm 为后缀名的文件，这个文件记录了该表的表结构定义。from 还用来存放视图的定义，如用户创建了一个 va 视图，那么对应地会产生一个 v_a.frm 文件。
注：MySQL 8.0 取消了 frm 文件，表结构一起存入 ibd 文件中。_

存储引擎文件

存储引擎文件存储的是具体的 MySQL 数据，我们插入 MySQL 的所有数据，最终会存储到这里。与 InnoDB 存储引擎密切相关的文件包括两个，分别是：表空间文件、重做日志文件。

表空间文件

表空间文件存储的是最终的 MySQL 数据文件，根据配置的不同可以实现不同数据表存到不同的表空间文件中。
InnoDB 采用将存储的数据按照表空间（tablespace）进行存放的设计。在默认配置下会有一个初始化大小为 10MB，名为 ibdata1 的文件。该文件就是默认的表空间文件（tablespace file），用户可以通过参数 innodb_data_file_path 对齐进行设置，格式如下：

  1. innodb_data_file_path=datafile_spec1[;datafile_spec2]...

用户可以将多个文件组成一个表空间，同时制定文件的属性，如：

  1. [mysqld]
  2. innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

这里将 /db/ibdata1 和 /dr2/db/ibdata2 两个文件用来组成表空间。同时文件的文件名都跟了属性，表示文件 idbdata1 的大小为 2000MB，文件 ibdata2 的大小为 2000MB，如果用完了这 2000MB，该文件可以自动增长（autoextend）。
设置了 innodb_data_file_path 参数后，所有基于 InnoDB 存储引擎的表的数据都会记录到该共享表空间。若设置了 innodb_file_per_table，则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立表空间。独立表空间的命名规则为：表名.ibd。通过这样的方式，用户不用讲所有数据都存放于默认的表空间中。下面这台 MySQL 数据库服务器设置了 innodb_file_per_table，可以看到其目录下的 idb 文件。

  1. // 查看 innodb_file_per_table 属性
  2. mysql> show variables like 'innodb_file_per_table'\G;
  3. *************************** 1. row ***************************
  4. Variable_name: innodb_file_per_table
  5.         Value: ON
  6. 1 row in set (0.00 sec)
  7. // 查看对应的 idb 文件
  8. [root@xxx ~]# ls -lh /var/lib/mysql/demodb/*
  9. -rw-r----- 1 mysql mysql 192K Mar  3 17:19 /var/lib/mysql/demodb/attachments.ibd
  10. -rw-r----- 1 mysql mysql 160K Jun 18 00:41 /var/lib/mysql/demodb/categories.ibd
  11. -rw-r----- 1 mysql mysql 112K Nov 20  2020 /var/lib/mysql/demodb/xx_black_list.ibd
  12. -rw-r----- 1 mysql mysql 160K Jul 29 20:11 /var/lib/mysql/demodb/xx.ibd
  13. -rw-r----- 1 mysql mysql 128K Nov 20  2020 /var/lib/mysql/demodb/xx_schema_history.ibd
  14. -rw-r----- 1 mysql mysql 112K Nov 20  2020 /var/lib/mysql/demodb/journals.ibd

重做日志文件

redo log 即重做日志，从字面意思来看，其表示可以将事情重做一遍的意思。而事实上，它确实是代表着这个意思。对于更新语句 update t set c=c+1 where id = 2，正常实现思路应该是：

  1. 找到 id 为 2 的记录，取出其 c 字段的值。
  2. 将 c 字段的值加一，之后将 id 为 2 的字段的 c 字段更新。

但实际上 MySQL 并不是这么做的，因为上述这种实现方式虽然能实现，但是每次都要去读取磁盘查找记录、写入磁盘更新记录，整个过程的磁盘 IO 成本很高。为了提高效率，MySQL 使用了一种叫做 WAL（Write-Ahead Logging）的技术，即写之前先记录变更日志（redo log），等待合适的时间再将其变更应用到数据库里。因为我们将操作记录下来了，所以我们可以复现这个操作，这就好像我们将事情重现了一样，因此叫 redo log。
使用 WAL 技术，上面这条更新语句的大致实现思路就变成了：

  1. 记录下更新操作日志：其要将 id 为 2 的记录的 c 字段加 1。
  2. 某个时刻，MySQL 数据库应用这个 redo log 日志，将数据库 id 为 2 的记录的 c 字段加 1。

注意：redo log 并不会应用于磁盘的表空间，而是在重启时应用于内存表空间缓存，用于实现 crash-safe。
可以看到，使用 WAL 技术的方式，可以不需要去读写磁盘，极大提高了执行效率。

重做日志文件格式

到 InnoDB 1.2.x 版本位置，总共定义了 51 种重做日志类型，它们都有着基本的格式。重做日志条目由 4 个部分组成：

  • redo_log_type 占用 1 字节，表示重做日志的类型。
  • space 表示表空间的 ID，但采用压缩的方式，因此占用的空间可能小于 4 字节。
  • page_no 表示页的偏移量，同样采用压缩的方式。

  • redo_log_body 表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析。

    重做日志文件参数

    每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0 和 ib_logfile1。InnoDB 存储引擎先写 ib_logfile0，当达到文件的最后时，会切换到 ib_logfile1，再当重做日志文件 ib_logfile1 写满时，会再切换到 ib_logfile0。1 个重做日志文件组所拥有的日志文件数量，默认为 2，可以通过 innodb_log_files_in_group 参数确定。
    此外，与重做日志文件相关的参数有：

  • innodb_log_group_home_dir：指定日志文件组所在路径，默认为 ./，表示在 MySQL 数据库的数据目录下。

  • innodb_log_file_size：指定每个重做日志文件的大小。

下面显示了一个关于重做日志组的配置：

  1. mysql> show variables like 'innodb%log%'\G;
  2. *************************** 7. row ***************************
  3. Variable_name: innodb_log_file_size
  4.         Value: 50331648
  5. *************************** 8. row ***************************
  6. Variable_name: innodb_log_files_in_group
  7.         Value: 2
  8. *************************** 9. row ***************************
  9. Variable_name: innodb_log_group_home_dir
  10.         Value: ./

重做日志文件不能设置得太大，如果设置得很大，在恢复时可能需要很长时间。另一方面又不能设置得太小，否则可能导致一个事务的日志需要多次切换重做日志文件。此外，重做日志文件太小会导致频繁发生 async checkpoint，导致性能抖动。

总结

介绍了 MySQL 文件体系的 6 个文件：

  • 参数配置文件：告诉 MySQL 实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数。
  • 日志文件，包括：错误日志文件、慢查询文件、查询日志文件、二进制日志文件。
  • socket 文件：当用 UNIX 域套接字方式进行连接时需要的文件。
  • pid 文件：MySQL 实例的进程 ID 文件，里面存储着 MySQL 实例的进程 ID 信息。
  • MySQL 表结构文件：用来存放 MySQL 表结构定义文件。
  • 存储引擎文件，包括：表空间文件、重做日志文件。

MySQL_文件体系.xmind