MySQL系列｜日志&文件

SmellyCat1024
124 阅读9分钟

MySQL文件分为MySQL Server文件和存储引擎文件。

MySQL Server文件

1.1 参数文件

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

mysql --help|grep my.cnf

1.2 错误日志

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

SHOW VARIABLES LIKE ‘log_error’

1.3 慢查询日志

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

1.3.1 慢查询相关参数

记录慢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’

慢查询日志存储路径

SHOW VARIABLES LIKE  ‘slow-query-log-file ‘ //默认在data目录，缺省文件host_name-slow.log

慢查询日志输出格式

log_output // 默认为FILE，可以配置为TABLE（记录到slow_log表）

1.3.2 慢查询日志查看

慢查询日志也是以文本文件的形式存储的，可以使用普通的文本文件查看工具来查看。日志路径 由参数slow_query_log_file决定（log_output = TABLE ）。
从MySQL5.1 开始，可以将慢查询日志输出到表中通过slow_log表查看：

表信息

SHOW CREATE TABLE mysql.slow_log

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

SET GLOBAL log_output=‘TABLE’

从table中查看慢SQL语句

select * from  mysql.slow_log

1.4 查询日志

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

1.5 二进制日志

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

1.5.1 二进制日志相关参数

查看是否开启二进制日志
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 |
+------------------+-----------+-----------+

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

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

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

1.5.2 缓冲区同步到磁盘策略

binlog_cache_size
当使用事务的存储引擎时（InnoDB存储引擎）时，所有未提交的事务的二进制日志会被记录到一个缓冲区，二进制日志事务缓冲区大小由binlog_cache_size决定。
事务开启时，uncommited的日志记录到缓冲区，事务提交时，缓存区写入二进制日志文件。
事务开始时自动分配，因此缓存区不能设置太大。
当大于binlog_cache_size时，会把缓冲区中的日志写入一个临时文件，因此缓存区也不能太小。默认32k。

sync_binlog
二进制日志不是每次写入的时候同步到磁盘，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同步磁盘

1.5.3 二进制日志格式

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';

1.6 套接字文件

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

SHOW VARIABLES LIKE ‘socket’

一般在/tmp/mysql.sock。

1.7 pid文件

MySQL实例的进程ID文件。

SHOW VARIABLES LIKE ‘pid_file’

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

1.8 表结构定义文件

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

2 InnoDB存储引擎文件

2.1 表空间文件

image.png

2.1.1 共享表空间文件

InnoDB将所有数据（表数据，索引，插入缓冲索引页，回滚信息，插入缓冲索引页，系统事务信息，二次写缓冲等等）逻辑地放在一个空间中。
innodb_data_file_path = datafile_spec1[;datafile_spec2]... 可通过多个文件组成一个表空间，文件位于不同的磁盘上，磁盘负载可能被平均，可以提高数据库整体性能。

2.1.2独立表空间文件

从mysql 5.6.6版本开始，独立表空间（file-per-table tablespaces）默认是开启的。在开启的情况下，你创建一个innodb引擎的表，那么表有自己独立的一些数据文件。 表名.frm：表的表结构文件(里面存放的是表的创建语句)
表名.ibd：表的数据文件(当有数据往表中插入时,数据就保存之个文件中的)

2.2 重做日志

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

image.png

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

2.2.1 重做日志参数

每个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数据库的数据目录下。

2.2.2 重做日志缓冲什么时候写入重做日志文件

  • 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 刷到文件系统中去，但并不会立即刷写到磁盘。

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

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

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

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

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

2.2.4 重做日志与Checkpoint机制的联系

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

image.png

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