一文搞懂MySQL的各类型文件

郑全

目录

一、文件的类型
二、数据data目录下结构
三、参数文件
四、日志文件
     1.错误日志文件（error log）
     2.慢查询日志文件（slow query log）
     3.查询日志文件（general log）
     4.二进制日志文件（binary log）

五、套接字文件
六、PID文件
七、表结构文件
八、存储引擎文件
      1. 表空间文件
      2. 重做日志文件（redo log）


一、文件的类型
类型        描述
参数文件        
1. 定义MySQL初始化参数；

2. MySQL实例启动从哪里可以找到数据库文件等

日志文件        
记录MySQL实例在某种条件作出响应时写入的文件，如：

（1）错误日志文件（error log）：错误信息、警告信息、正常信息

（2）慢查询日志文件（slow query log）：大于超时阈值时，记录SQL到该文件

（3）查询日志文件（general log）：记录对MySQL数据库所有请求的信息

（4）二进制日志文件（binary log）：记录对MySQL数据库更改的操作日志

套接字文件        连接MySQL实例使用套接字方式
pid文件        MySQL实例的进程ID文件
表结构文件        
1. 存放表结构的文件；

2. 以.frm结尾的文件

存储引擎文件        
1. 不同的存储引擎会各自保存自己的各种数据（记录和索引）；

2. InnoDB存储引擎文件：表空间文件（共享或独立）、重做日志文件；

3. InnoDB的独立表空间文件是以.ibd结尾的文件

二、数据data目录下结构
        通过命令show variables like 'datadir'查询MySQL实例的数据目录，如下所示：

mysql> show variables like 'datadir';
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| datadir       | /home/MySQL5.7/mysql-5.7.35/data/ |
+---------------+-----------------------------------+
1 row in set (0.04 sec)
        以下显示MySQL的数据目录的一级树形文件，即：data目录的树形文件。

tree -L 1

[root@488c1daa7967 data]#
[root@488c1daa7967 data]# pwd
/home/MySQL5.7/mysql-5.7.35/data
[root@488c1daa7967 data]#
[root@488c1daa7967 data]# tree -L 1
.
├── 488c1daa7967.err           # 错误日志文件
├── 488c1daa7967.log           # 查询日志文件，默认关闭OFF，ON开启时才有该文件
├── 488c1daa7967.pid           # PID文件
├── 488c1daa7967-slow.log      # 慢查询日志文件，默认关闭OFF，ON开启时才有该文件
├── auto.cnf                   # 实例启动时，自动生成的配置文件
├── ca-key.pem                 # CA证书的key
├── ca.pem                     # CA证书
├── client-cert.pem            # MySQL客户端证书
├── client-key.pem             # MySQL客户端密钥
├── ib_buffer_pool             # 缓冲池
├── ibdata1                    # InnoDB存储引擎文件：共享表空间文件
├── ib_logfile0                # InnoDB存储引擎文件：重做日志文件（redo log）
├── ib_logfile1                # InnoDB存储引擎文件：重做日志文件（redo log）
├── ibtmp1                     # 临时表空间
├── my.cnf                     # 自定义配置，启动时指定该文件
├── mysql                      # 系统mysql库
├── mysql-bin.000001           # 二进制日志文件
├── mysql-bin.index            # 二进制日志索引文件
├── performance_schema         # 系统performance_schema库
├── private_key.pem            # 不使用SSL时，安全传输的密钥
├── public_key.pem             # 不使用SSL时，安全传输的公钥
├── server-cert.pem            # MySQL服务端证书
├── server-key.pem             # MySQL服务端密钥
├── sys                        # 系统sys库
└── test_mysql                 # 用户test_mysql库
三、参数文件
MySQL5.7 参数详解_爱我所爱0505的博客-CSDN博客

四、日志文件
1. 错误日志文件（error log）
        错误日志文件对MySQL实例的启动、运行、关闭过程进行了记录，该文件不仅记录所有的错误信息，也记录警告和正常的信息。通过命令show variables like 'log_error'查询错误日志文件的路径，如下所示。./路径是指MySQL数据目录。

mysql> show variables like 'log_error';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| log_error     | ./488c1daa7967.err |
+---------------+--------------------+
1 row in set (0.04 sec)
2. 慢查询日志文件（slow query log）
        慢查询日志文件记录执行SQL时间大于long_query_time参数阈值的SQL语句。默认情况下，MySQL数据库不启动慢查询日志，通过参数slow_query_log来设置，默认关闭OFF，开启ON。

        注意：只有大于long_query_time（默认10s）时，才能记录SQL到慢查询日志文件中。等于该阈值时，不会记录。

# 设置参数

SET @@global.slow_query_log=ON;

mysql> SET @@global.slow_query_log=ON;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.06 sec)
        通过执行 select sleep(11);的SQL语句，来查看慢查询日志。也可以通过设置log_output参数决定慢查询日志的输出格式，默认FILE。设置为TABLE时，通过mysql.slow_log表查看。

[root@488c1daa7967 data]# cat 488c1daa7967-slow.log
/home/MySQL5.7/mysql-5.7.35/bin/mysqld, Version: 5.7.35 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2022-07-06T09:41:31.714129Z
# User@Host: root[root] @ gateway [172.17.0.1]  Id:     5
# Query_time: 11.003121  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test_mysql;
SET timestamp=1657100491;
select sleep(11);
[root@488c1daa7967 data]#
        关于慢查询的相关参数，如下表所示：

参数        描述
slow_query_log

是否开启慢查询日志，默认关闭OFF，开启ON
long_query_time

大于时间阈值时则记录，默认10s（>10s时记录）
slow_query_log_file

慢查询日志文件路径，默认./（数据库数据目录，即：data下）
log_output

慢查询/通用查询日志输出格式，默认FILE

若TABLE时，则mysql.slow_log查看

log_queries_not_using_indexes        
SQL没有使用索引时，是否记录到慢查询日志中

（默认关闭OFF，ON开启）

log_throttle_queries_not_using_indexes        
SQL没有使用索引时，每分钟记录到慢查询日志中的个数

（默认0，即：没有限制）

3. 查询日志文件（general log）
        查询日志文件记录所有对MySQL数据库请求的信息，无论是否正确执行。参数general_log控制是否开启，默认关闭OFF，原因是日志量非常庞大。参数general_log_file查询日志文件的路径，默认data目录。

        通过设置log_output参数决定查询日志的输出格式，默认FILE。设置为TABLE时，通过mysql.general_log表查看

[root@488c1daa7967 data]# tail -f 488c1daa7967.log
/home/MySQL5.7/mysql-5.7.35/bin/mysqld, Version: 5.7.35 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2022-07-07T01:56:54.299087Z            8 Query        show variables like 'general_log'
2022-07-07T02:01:43.813587Z            8 Query        show variables like 'general_log'
2022-07-07T02:01:58.306156Z            9 Connect        root@gateway on  using TCP/IP
2022-07-07T02:01:58.308760Z            9 Query        SET NAMES utf8mb4
2022-07-07T02:01:58.310713Z            9 Init DB        mysql
2022-07-07T02:01:58.312276Z            9 Init DB        test_mysql
2022-07-07T02:01:58.317903Z            9 Query        BEGIN
2022-07-07T02:01:59.674504Z            9 Query        COMMIT
4. 二进制日志文件（binary log）
        二进制日志文件记录所有对MySQL数据库执行更改的操作，但不包括SELECT和SHOW命令。注意，若更新语句本身并没有导致数据变化时，该操作也会写入二进制日志文件。

        关于二进制日志的相关参数，如下表所示。通过show global status like 'binlog_cache%'查询二进制日志缓冲的使用情况。Binlog_cache_disk_use表示使用临时文件写二进制日志的次数，Binlog_cache_use表示使用缓冲写二进制日志的次数，两者的比较可以看出binlog_cache_size设置值是否合理。

参数        描述
log-bin

1. 是否开启二进制日志文件，log-bin=[name]；

2. 默认路径是datadir指定，文件名称name指定

max_binlog_size

1. 单个二进制日志文件的最大值，默认1073741824 = 1G；

2. 超过该值后，则生成一个新文件，后缀名+1，并记录到index

binlog_cache_size

1. 事务开始时，所有未提交的二进制日志记录到一个缓存中，等待事务提交时，直接将缓存写入二进制日志文件；

2. 当线程开启一个事务时，会自动创建一个缓存；

3. 默认缓存大小32768 = 32KB，超出时，则写入临时文件

sync_binlog        
1. sync_binlog=[N]，表示每写缓冲N次就触发同步到磁盘；

2. sync_binlog=1表示同步写磁盘，即：不使用缓存直接写入磁盘

innodb_support_xa

1. 是否支持XA事务，默认开启ON；

2. sync_binlog=1时，事务已提交，而写磁盘时宕机，通过该参数完成二进制日志与数据文件的同步

binlog_format

1. 二进制日志文件输出格式，默认ROW；

2. 格式类型：Statement（内容SQL语句，但是Mysql函数不会写入），Row（更新的记录），Mixed（SQL+Row）

五、套接字文件
        连接MySQL实例使用套接字方式，则需要一个套接字文件，通过show variables like 'socket';查询套接字文件路径，默认在/tmp目录下。

mysql> show variables like 'socket';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| socket        | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.09 sec)
六、PID文件
        存放MySQL实例的进程ID文件，实例启动时，生成一个进程ID文件。通过show variables like 'pid_file'查询PID文件路径，默认在数据库目录下。

mysql> show variables like 'pid_file';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| pid_file      | 488c1daa7967.pid |
+---------------+------------------+
1 row in set (0.08 sec)
七、表结构文件
        MySQL无论采用哪种存储引擎的插件，每个表都对应一个*.frm结尾的文件，该文件存储了该表的表结构。注意，frm还可以存放视图的定义。

[root@488c1daa7967 test_mysql]# tree
.
├── db.opt
├── page_structure.frm              # 表结构文件
├── page_structure.ibd              # InnoDB存储引擎的独立表空间
├── t_order.frm
├── t_order.ibd
├── t_system_user_resource.frm
└── t_system_user_resource.ibd

0 directories, 7 files
        通过参数show create table 表名;来查询表的结构，如下所示。

mysql> show create table page_structure\G;
*************************** 1. row ***************************
       Table: page_structure
Create Table: CREATE TABLE `page_structure` (
  `a` int(255) NOT NULL AUTO_INCREMENT,
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
八、存储引擎文件
        表结构文件与存储引擎类型没有关系，而不同的存储引擎有着不同的存储引擎文件。本小节介绍InnoDB存储引擎有关的文件，如：表空间文件、重做日志文件等。

1. 表空间文件
        InnoDB采用将数据按表空间存放的设计，分为：共享表空间、独立表空间。默认配置下，会初始化大小10MB的共享（存放所有表）表空间文件，即：在数据目录下ibdata1文件。

        通过参数show variables like 'innodb_data_file_path'可以设置，也可以多个文件组成一个表空间，同时设置文件属性。该参数是静态参数，实例运行期间不能设置，只有启动前配置。

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.22 sec)
        参数show variables like 'innodb_file_per_table'控制是否开启为每个表建立独立表空间，默认开启ON，则表空间为：表名.ibd。注意：独立表空间存储：表数据、索引、插入缓冲的BITMAP信息，其他存储在默认共享表空间中。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.07 sec)
2. 重做日志文件（redo log）
        重做日志文件记录了事务日志，称为InnoDB存储引擎的日志文件。目的是当MySQL宕机时，使用重做日志文件可以恢复宕机之前的数据，实现数据库ACID中的持久性。

        默认情况下，每一个InnoDB存储引擎至少有一个重做日志文件组（group），每个组下默认2个重做日志文件，如：默认ib_logfile0和ib_logfile1文件。注意组下的每个重做日志文件大小相同，并且以循环写入的方式运行，如下图所示。



        写入重做日志文件不是直接写，而是先写入重做日志缓冲（redo log buffer），然后按照循环顺序写入重做日志文件。写入磁盘时，一次写入512字节，即：按一个扇区的大小写入，因为扇区是系统写时的最小单位，进而无需双次写doublewrite。参数innodb_log_buffer_size设置缓冲大小，默认8MB。下图所示是重做日志写入过程。



        参数innodb_flush_log_at_trx_commit控制事务提交时处理重做日志方式，默认1（事务提交时，redo log buffer写入磁盘）、0（等待master thread每秒刷新到磁盘）、2（异步写入磁盘，注意事务提交时不能确保写入重做日志中）。注意：0、2导致事务丢失，为了保证ACID特性中的持久性，则设置为1，保证每当事务提交时，确保事务都已经写入重做日志文件。

        关于重做日志的相关参数，如下表所示：

参数        描述
innodb_log_file_size        
1. 每个重做日志文件的大小；

2. 不能大于512G

innodb_log_files_in_group        
重做日志文件组中的文件数量，默认2

innodb_log_group_home_dir        
1. 重做日志文件所在的路径，默认文件：ib_logfile0、ib_logfile1；

2. 默认./，即：数据目录下

innodb_log_buffer_size        重做日志缓冲大小，默认8MB
innodb_flush_log_at_trx_commit        
1. 控制事务提交时处理重做日志方式；

2. 默认1：事务提交时，redo log buffer写入磁盘、

           0：等待master thread每秒刷新到磁盘、

           2：异步写入磁盘，注意事务提交时不能确保写入重做日志中

   注意：0、2导致事务丢失