MYSQL复习——第三章：文件（日志文件+其他）

JYY_JYY_

已于 2024-02-20 11:54:51 修改

阅读量187

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2020-10-20 15:08:39 首次发布

本文链接：https://blog.csdn.net/qq_41157876/article/details/109173663

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

日志文件
错误日志（error log）
慢查询日志 (slow query log)
查询日志 (log)
事务日志 (redo log + undo log)
redo log
日志刷盘规则（redo log buffer ——>redo log file）
提交时三种策略
基本单位：redo log block
log group和redo log file
undo log
LSN
二进制日志 (binlog)
中继日志
参数配置文件（my.conf）
套接字文件
pid文件：主机名.pid；里面记录了进程ID
表结构frm文件：frm文件定义表结构
innodb和myisam文件

3.1 日志文件

日志文件记录了影响MySQL数据库的各种类型活动，常见文件有以下几种

（1）错误日志（error log）

记录了错误信息，当数据库无法启动时，应该第一时间查看这个文件。

日志名：主机名.err

（2）慢查询日志（slow query log）

默认不启动，需要手动开启。三个参数

long_query_time：默认10秒
log_slow_queries：默认关闭
log_queries_not_use_indexes：控制如果没走索引，是否加入慢查询日志

超过阈值时间或没走索引的SQL都会记录在该日志中

（3）查询日志（log）

记录了所有对MySQL数据库请求的信息（比如SELECT和SHOW），默认文件名：主机名.log。高并发下不建议开启，因为查询信息很多，IO非常大影响性能

（4）二进制日志（bin log）

记录了对MYSQL数据库执行更改的所有操作，不包括SELECT和SHOW（这两类语句可以通过查询日志获取）。主要用于：恢复；主从复制；判断是否有注入攻击。默认二进制日志不开启，根据MySQL官方测试，开启二进制日志会使性能下降1%，影响尚可。由于是二进制记录的，查询需要用特定的mysqlbinlog工具

默认情况下并不是每次写都同步到磁盘（可以理解为缓冲写），因此会存在宕机丢失问题，由参数sync_binlog控制，要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1

sync_binlog=0：不同步，日志何时刷到磁盘由FileSystem决定，这个性能最好。
sync_binlog=n：每写n次事务(注意，对于非事务表来说，是n次事件，对于事务表来说，是n次事务，而一个事务里可能包含多个二进制事件)，MySQL将执行一次磁盘同步指令fdatasync()将缓存日志刷新到磁盘日志文件中。Mysql中默认的设置是sync_binlog=0，即不同步，这时性能最好，但风险最大。一旦系统奔溃，缓存中的日志都会丢失。

记录格式：binlog_format参数

binlog_format参数的三种格式	优缺点
STATEMENT：记录的是日志的逻辑SQL语句	优点：减少了binlog日志量，节约IO，提高性能（正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志）缺点：如mysql的主从复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题)
ROW：记录每行修改情况。性能好，但是占用空间大	优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中
MIXED：默认STATEMENT，但是一些情况下会使用ROW(比如UUID()等函数)	一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更

与事务日志（redo log）的区别：

1. bin log是一种逻辑日志，基于行的记录方式（记录的是SQL语句）；事务日志记录的是物理格式，是对每个页的修改（比如磁盘1的偏移为2的值为3）

2. bin log是在数据库上层产生的，针对所有存储引擎。而事务日志只针对innodb存储引擎。

3. 写入的时间点不一样

bin log只在事务提交时一次性写入(先写cache，提交时再刷盘)(对于非事务表的操作，则是每次执行语句成功后就直接写入)
修改数据前写入缓存的redo log buffer中，然后才对缓存的数据进行修改
提交事务时，先向缓存中的redo log写入日志，写入完成后才执行提交动作
而redo log在事务进行时不断被写入缓冲，写入log file的时机根据策略而定

4. 写入顺序不一样

bin log日志的记录方式和提交顺序有关，一次提交对应一次记录
而redo log由于支持并发，不同的事物之间的版本记录会交替穿插写入

5. 用途不一样

bin log可以作为恢复数据使用，主从复制搭建
redo log作为异常宕机或者介质故障后的数据恢复使用

（5）事务日志

ref: https://www.cnblogs.com/f-ck-need-u/p/9010872.html

redo log	undo log
用于事务恢复	用于事务回滚和MVCC
记录的是页的修改	逻辑日志——按行记录，记录完整修改语句
记录在redo log file中	记录在表空间中的 undo segment中

为了保证事务的ACID特性，InnoDB引擎引入事务日志: 重做日志redo和回滚日志undo

redo保证原子性和持久性，通常是物理日志，记录是页的物理修改操作

undo保证一致性（回滚和MVCC），是逻辑日志，根据每行进行记录

redo包括：重做日志缓冲和重做日志文件

5.1 redo log

（1）日志刷盘规则

对数据库的每次写入都会记录在 log buffer中，但是log buffer何时同步到硬盘有多种规则

每秒刷新一次（主线程负责），这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。要注意，这个刷日志频率和commit动作无关。
当log buffer中使用的内存超过一半时
当有checkpoint时，会将数据脏页和脏日志一起刷新到磁盘
发出commit动作时（即事务提交）：三种规则，见（2）

（2）redo log buffer写入到文件的规则

三种写入格式将buffer刷到文件中，由参数innodb_flush_log_at_trx_commit决定。见ref

事务提交时，log buffer根据一定规则将内存中（redo log buffer）的log block刷新到磁盘

参数	规则		插入50万数据的时间
0	与提不提交无关，log_buffer每秒写入os buffer并调用fsync()写入到log file on disk中	1秒的丢失	13.9秒
1	每次提交都写入，每次提交都会写入os_buffer并立马同步到log file on disk中	零丢失，但是每次提交事务都写入磁盘，IO差不推荐	2分钟
2	每次提交都写入os_buffer，每秒os_buffer同步到log file on disk中	建议使用，比0好。宕机不会丢失，因为尽管log buffer的内容不见了，但是os buffer的内容还在	>13.9秒，但是没差多少

（3）事务日志块

记录方式：重做日志缓存和文件都是以块redo log block保存的，每块512字节：

（日志块头12字节+ 内容492字节 + 日志块尾8字节 = 512字节）

（4）log group和redo log file

默认一个group，组内两个file循环写入。

写入方式为append，将block追加到redo log file的最后部分，当一个redo log file被写满时，会接着写入下一个redo log file，方式为round-robin。即先在第一个log file（即ib_logfile0）的尾部追加写，直到满了之后向第二个log file（即ib_logfile1）写。当第二个log file满了会清空一部分第一个log file继续写入。

在每个组的第一个redo log file中，前2KB记录4个特定的部分，从2KB之后才开始记录log block。除了第一个redo log file中会记录，log group中的其他log file不会记录这2KB，但是却会腾出这2KB的空间。如下图

（4）重做日志格式（492KB中怎么记录日志的）：参考ref：https://www.cnblogs.com/f-ck-need-u/p/9010872.html

可以理解为是基于页的修改记录：比如磁盘1的偏移为2的值为3（有待考证）

（5）整个流程，详见ref

（6）恢复

每次Innodb启动，都需进行恢复操作。通过checkpoint和LSN实现

5.2 undo log

undo log有两个作用：提供回滚和MVCC（让用户实现非锁定一致性读取）

内部机制：

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
update分为两种情况：update的列是否是主键列。
- 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
- 如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

5.3 LSN

待更新，见Reference：https://www.cnblogs.com/f-ck-need-u/p/9010872.html