MySQL中常见的几种日志类型【重点】

在MySQL中，有几种不同类型的日志，用于记录数据库的活动和操作，以便于故障排查、性能调优和数据恢复等目的。以下是MySQL中常见的几种日志类型：

1. 错误日志（Error Log）：
   错误日志记录了MySQL服务器在启动和运行过程中出现的错误消息、警告和一般信息。错误日志对于诊断数据库问题非常有用，例如数据库启动失败、查询执行错误等情况。

2. 查询日志（Query Log）：
   查询日志记录了所有已执行的SQL语句，包括SELECT、INSERT、UPDATE、DELETE等操作。查询日志可以用于分析数据库的性能问题、优化慢查询以及审计数据库操作等。

3. 慢查询日志（Slow Query Log）：
   慢查询日志记录了执行时间超过预定义阈值的SQL查询语句。通常情况下，管理员可以根据自己的需求设置慢查询日志的阈值，以便于找出需要优化的查询语句，并对其进行性能调优。

4. 归档日志（BinLog）：
   事务日志记录了所有对数据库进行修改的操作，例如INSERT、UPDATE、DELETE等。事务日志是MySQL数据库复制（Replication）的基础，它可以用于在主从服务器之间复制数据，并实现数据备份和灾难恢复。（用于数据备份和主从复制）

5. 重做日志（Redo Log）：
   重做日志记录了数据库引擎对数据文件进行的物理级别的修改，例如页的插入、更新和删除操作。重做日志用于在数据库崩溃或断电等意外情况下，恢复数据到最新的一致状态。

6. 回滚日志（Undo Log）：
   撤销日志记录了数据库引擎对事务进行的修改操作的逆操作，以便于在事务回滚或数据库崩溃时撤销事务对数据库的影响。

这些日志类型在MySQL中起着不同的作用，可以帮助管理员监控数据库的健康状态、优化性能和实现数据的安全备份和恢复。

参考：
【1】 https://zhuanlan.zhihu.***/p/150105821
【2】https://xiaolincoding.***/mysql/log/how_update.html#%E4%B8%BA%E4%BB%80%E4%B9%88%E9%9C%80%E8%A6%81-undo-log
【3】https://javaguide.***/database/mysql/mysql-logs.html#%E5%89%8D%E8%A8%80

1——undo log（回滚日志）

undo log（回滚日志），它保证了事务的 ACID 特性 (opens new window)中的原子性（Atomicity）。

undo log 两大作用：

1. 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。

当进行操作（修改、删除、新增）时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据，比如：

• 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
• 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
• 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

2. 实现 MV***（多版本并发控制）关键因素之一。MV*** 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

• 通过 trx_id 可以知道该记录是被哪个事务修改的；
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

undo log 是如何刷盘（持久化到磁盘） 的？

1. undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。
2. buffer pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log。redo log 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的

2——Buffer Pool

Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

1. 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
2. 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

查询一条记录，就只需要缓冲一条记录吗？ 不是的。
当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

Buffer Pool 虽然提高读写效率，但Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。
为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。
后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是WAL （Write-Ahead Logging）技术。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

过程如下图：

先写到的日志就是写到 redo log里面

3——redo log（重做日志）

保持数据的持久性，

redo log 是物理日志，记录了某个数据页做了什么修改，每条 redo 记录由**“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”**组成。比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

1. 只要每次把修改后的数据页直接刷盘不就好了？为什么还要redo log 要写到磁盘？
【其实内存的数据页在一定时机也会刷盘，我们把这称为页合并】

1. 数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，没有必要把完整的数据页刷盘
2. 如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值，所以刷盘速度很快。
3. 写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

2. redo log 刷盘时机
执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。
所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

1. MySQL 正常关闭时；
2. 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
3. InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
4. 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_***mit 参数控制）。

innodb_flush_log_at_trx_***mit
可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

1. 当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。
2. 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。
3. 当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。

innodb_flush_log_at_trx_***mit 为 0 和 2 的时候，什么时候才将 redo log 写入磁盘？
InnoDB 的后台线程每隔 1 秒：

1. 针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
2. 针对参数 2 ：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2的策略，较取值为 0 情况下更安全，因为 MySQL进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
   如图：
   参考链接：https://xiaolincoding.***/mysql/log/how_update.html#redo-log-%E4%BB%80%E4%B9%88%E6%97%B6%E5%80%99%E5%88%B7%E7%9B%98

3.redo log 文件写满了怎么办
默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫 ：ib_logfile0 和 ib_logfile1 。
重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。
redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的，那么如果随着系统运行，Buffer Pool 的脏页刷新到了磁盘中，那么 redo log 对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出空间记录新的更新操作。
redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置，如下图：

图中的：

1. write pos 和 checkpoint 的移动都是顺时针方向；
2. write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
3. check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；

如果 write pos 追上了 checkpoint，就意味着== redo log 文件满了==，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。
所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程

所以redo log保持数据的持久性，用于掉电等故障恢复。
4.如果不小心 整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

• 不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。 因为 redo log文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。
• binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，用 binlog 文件恢复数据。

4——binlog

binlog 文件记录了所有数据库表结构（DDL）变更和表数据修改（DML）的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

1.binlog写入机制（刷盘）

1. 事务执行过程中，先把日志写到binlog cache，
2. 事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。（如果拆开写，破坏原子性）
3. 我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。
   binlog日志刷盘流程如下：
   在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。
   
   虽然每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件：

• 图中的 write，指的就是指把日志写入到 binlog 文件，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里，write 的写入速度还是比较快的，因为不涉及磁盘 I/O。
• 图中的 fsync，才是将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高。

MySQL提供一个== sync_binlog 参数==来控制数据库的 binlog 刷到磁盘上的频率：

1. sync_binlog = 0 （默认）的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
2. sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
3. sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

• 在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失。

• 而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

• 如果能容少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000中的某个数值。

2. 主从复制是怎么实现
MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

MySQL 集群的主从复制过程梳理成 3 个阶段：

1. 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
2. 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
3. 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

1. MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
2. 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
3. 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。
   

MySQL 主从复制还有哪些模型？

主要有三种：

1. 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
2. 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
3. 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

从库不是越多越好，因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。
所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

5——两阶段提交（待更新…）

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。