MySQL文件分为MySQL数据库文件和存储引擎相关的各种类型文件。

1 参数文件

在MySQL实例启动，用来寻找数据库的各种文件所在的位置，指定某些初始化参数。

• 定位文件命令

mysql --help|grep my.cnf

2 日志文件

日志文件记录了影响MySQL数据库的各种类型活动。

2.1 错误日志

对MySQL的启动，运行，关闭过程进行了记录。不仅记录所有的错误信息，也记录了一些警告信息或正确的信息。

• 定位文件命令

SHOW VARIABLES LIKE ‘log_error’

2.2 慢查询日志

记录执行时间超过某一阀值的所有SQL，慢查日志可以帮助定位可能存在问题的额SQL语句。默认情况下，MySQL并不启动慢查日志。

• 记录慢SQL的开关 （打开ON）

SHOW VARIABLES LIKE ‘log_slow_queries’

• 慢SQl的阀值 默认为10，单位秒（等于不会记）

SHOW VARIABLES LIKE ‘long_query_time’ 

• 是否记录没有使用索引的查询

SHOW VARIABLES LIKE ‘long_queries_not_using_indexes’

• 每分钟最多记录没使用索引的SQL的数量

SHOW VARIABLES LIKE ‘log_throttle_queries_not_using_indexs’

• 其他参数

slow-query-log-file:  指定目录，默认在data目录
log_output：输出格式，默认为FILE，可以配置为TABLE（记录到slow_log表）

通过表中slow_log查看

从5.1 开始，可以将慢查询日志输出到表中

• 表信息

SHOW CREATE TABLE mysql.slow_log

• 慢查询的输出格式，默认为FILE，需要设置为TABLW

SET GLOBAL log_output=‘TABLE’

• 从table中查看慢SQL语句

select * from  mysql.slow_log

2.3 查询日志

记录所有对MySql数据库的请求信息，无论请求是否执行。
默认文件名为主机名.log，也可以输出到mysql架构的general_log表中。

2.4 二进制日志

记录对MySql数据库执行更改的所有操作，不包括select，show这类操作。若更改操作并未修改，也会记录。
可用于恢复、复制、审计。 MySql官方手册测试表明，开启二进制日志会使性能下降1%，但考虑到复制，point-in-time的恢复等功，建议开启。

查看是否开启二进制日志

MySQL8.0之前默认是关闭的，之后默认是开启的

show variables like "log_bin"; //ON表示开启

开启二进制日志

如果需要开启二进制日志，需要在配置文件中添加 log-bin 选项

[mysqld]
log-bin=dir/[filename]
// dir参数指定二进制文件的存储路径
// filename参数指定二级制文件的文件名

如果没有 dir 和 filename 参数，二进制日志将默认存储在数据库的数据目录下，默认的文件名为hostname-bin.number。

查看 MySQL 中有哪些二进制日志文件

>show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000035 | 686110 | No |
| mysql-bin.000036 | 96148 | No |
+------------------+-----------+-----------+

查看当前正在写入的二进制日志文件

>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000036 | 96148 | | | |
+------------------+----------+--------------+------------------+-------------------+

缓冲区同步到磁盘策略

当使用事务的存储引擎时（InnoDB存储引擎）时，所有未提交的事务的二进制日志会被记录到一个缓冲区，缓冲区大小由binlog_cache_size决定。

• binlog_cache_size 二进制日志事务缓冲区大小
  事务开启时，uncommited的日志记录到缓冲，事务提交时，缓存写入二进制日志文件。 事务开始时自动分配，因此不能设置太大。当大于时，会把缓冲中的日志写入一个临时文件，因此也不能太小。默认32k。

二进制日志不是每次写入的时候同步到磁盘，MySQL提供sync_binlog参数来控制数据库缓冲区的binlog刷到磁盘上的策略。

• sync_binlog = N 表示每次缓冲写N次将binlog_cache中的数据强制写入磁盘, 默认为0
  sync_binlog = 0，当事务提交之后，不做fsync同步，依赖操作系统本身不定期把文件内容刷新到磁盘
  sync_binlog = 1，每进行1次事务提交之后，fsync同步磁盘
  sync_binlog = n，每进行n次事务提交之后，fsync同步磁盘

其他参数

• max_binlog_size 单个二进制日志文件的最大值
  该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。 超过后产生新的二进制文件，后缀+1，并记录到.index。默认1G。

二进制日志格式

• STATEMENT：逻辑SQL语句。
  对复制有要求，如主服务器运行rand，uuid，触发器等操作 ，可能导致主从表中数据不一致；
  默认事务级别 repeatable read，也是因为二进制日志文件的关系，若使用read commited, 会出现类似丢失，主从不一致。
• ROW：表的行更改情况
  可以为数据库恢复和复制带来更好的可靠性，但是二进制文件大小增加。
• MIXED
  因为 STATEMENT 和 ROW 各有优缺点，因此 MySQL 新增了一种格式 MIXED，采用折中方案，MySQL 会判断采用 STATEMENT 还是 ROW 来记录 binlog。

binlog_format 是动态参数，可以更改:

SET @@session.binlog.format='ROW';
SET GLOBAL binlog.format='ROW';

2.5 套接字文件

本地连接mysql可以使用UNIX域套接字，需要套接字文件。

SHOW VARIABLES LIKE ‘socket’

一般在/tmp/mysql.sock。

2.6 pid文件

MySQL实例的进程ID文件。

SHOW VARIABLES LIKE ‘pid_file’

默认数据库目录下/user/local/mysql/data/， 主机名.pid。

2.6 表结构定义文件

存放MySql表结构定义。
在数据目录下，每一个表都有一个子目录，存放对应的表结构定义文件，后缀为frm
MySql8.0后，移除了.frm文件，表结构定义存放到数据库系统表中。

3 InnoDB存储引擎文件

3.1 表空间文件

image.png

共享表空间文件

InnoDB将所有数据（表数据，索引，插入缓冲索引页，回滚信息，插入缓冲索引页，系统事务信息，二次写缓冲等等）逻辑地放在一个空间中。

• innodb_data_file_path = datafile_spec1[;datafile_spec2]...
  可通过多个文件组成一个表空间，文件位于不同的磁盘上，磁盘负载可能被平均，可以提高数据库整体性能。

独立表空间文件

从mysql 5.6.6版本开始，独立表空间（file-per-table tablespaces）默认是开启的。在开启的情况下，你创建一个innodb引擎的表，那么表有自己独立的一些数据文件。

• 表名.frm # 表的表结构文件(里面存放的是表的创建语句)
• 表名.ibd # 表的数据文件(当有数据往表中插入时,数据就保存之个文件中的)

3.2 重做日志文件

重做日志是为了保证事务的原子性，持久性。InnoDB采用Write Ahread Log策略，事务提交时，先写重做日志，再修改页。
重做日志（redo log）由内存中的重做日志缓冲（redo log buffer），和重做日志文件（redo log files）组成。如下图所示：

image.png

事务提交时，先写重做日志。redo log都是以512字节存储，重做日志缓冲，和重做日志文件都是以块（block）进行保存，重做日志块（redo log block）和磁盘扇区大小一样，都是512字节。因此重做日志的写入可以保证原子性，不需要doublewrite技术。

重做日志文件参数

每个innoDB存储引擎至少有一个重做日志文件组，每个文件组至少2个重做日志文件。默认在数据目录下，ib_logfile0和ib_logfile1。

• innodb_log_file_size 日志组中每个日志文件的大小
  如果该参数设置太大，由于意外(断电，OOM-Kill等)宕机时，二进制日志很大恢复需要时间长；参数设置太小，一个事物日志多次切换重做日志文件，频发发生asyc checkpoint，从而影响IO性能。最大512G。
• innodb_log_files_in_group
  指定日志组个数，默认为2个日志组
• innodb_log_group_home_dir
  指定日志组所在的路径，默认为./ ，表示在MySQL数据库的数据目录下。

重做日志缓冲（redo log buffer）什么时候写入重做日志

• master thread每秒（无论事务是否提交）
• innodb_flush_log_at_trx_commit 参数控制

关于innodb_flush_log_at_trx_commit参数

0 : 表示由后台Master线程每隔 1秒把 log buffer 刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区，如果机器宕掉，可能丢失1秒的事务数据；
1: 表示在每次事务提交的时候，都把 log buffer 刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去；
2：表示在每次事务提交的时候会把 log buffer 刷到文件系统中去，但并不会立即刷写到磁盘。

注意
为0或者2时， 都有可能发生恢复时部分事务丢失。
不同的是，设置为2时，当数据库发生宕机，但是操作系统服务器并没有宕时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据恢复不丢失。
为了ACID的持久性，建议 innodb_flush_log_at_trx_commit 设置为1，默认也是1。

重做日志与二进制日志区别

• 二进制日志记录所有包括Innodb，MYISAM，Heap等其他存储引擎的日志，
  重做日志是innodb产生的，记录存储引擎本身的事务日志。

• 二进制日志是是逻辑日志，记录关于事务的具体操作内容；
  重做日志是物理格式日志，记录关于每个页的更改的物理情况。

• 二进制仅日志在事务提交完成时一次写入，即只写磁盘一次；
  重做日志在事务进行中不断写入。

重做日志与Checkpoint机制的联系

如下图所示，一组redo log文件是一个类似环形的状态，循环利用。
write pos指的是当前写入redo log的位置，check point是要擦除并更新到数据文件的位置，所以write pos 到check point 位置就是还未使用的空闲空间。

image.png

• InnoDB中重做日志文件是循环使用的。当页被Checkpoint刷新到磁盘后，对应的重做日志就不需要使用 ，其空间可以被覆盖重用。
• 如果待写入的重做日志文件空间不可用（脏页还没有刷新到磁盘），就需要强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。
• 数据库宕机重启时通过执行重做日志恢复数据。但由于Checkpoint机制，数据库宕机重启并不需要重做所有的日志，因为Checkpoint之前的页都刷新到磁盘了，只需执行最新一次Checkpoint后的重做日志进行恢复，这样可以缩短数据库的恢复时间。