MySQL中各类型文件详解

目录

一、文件的类型

二、数据data目录下结构

三、参数文件

四、日志文件

1. 错误日志文件（error log）

2. 慢查询日志文件（slow query log）

3. 查询日志文件（general log）

4. 二进制日志文件（binary log）

五、套接字文件

六、PID文件

七、表结构文件

八、存储引擎文件

1. 表空间文件

2. 重做日志文件（redo log）

九、参考资料

---

一、文件的类型

二、数据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]# 

        关于慢查询的相关参数，如下表所示：

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设置值是否合理。

五、套接字文件

        连接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，保证每当事务提交时，确保事务都已经写入重做日志文件。

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

九、参考资料

mysql关于innodb_data_file_path设置_Demonson的博客-CSDN博客_innodb_data_file_path

MySQL5.7: my.cnf参数速查小册子_琦彦的博客-CSDN博客

MySQL数据目录下文件说明 - MySQL教程 - 博客园