MySQL进阶系列：你需要了解的几种MySQL日志

2021-12-26 22:21:32

MySQL版本：5.7.21

MySQL中有多种日志来保证实现其各种功能，我们最常见或者常听说的是Binlog(二进制日志)，Redo Log(重做日志)，Undo Log(回滚日志)。其实还有Relay Log(中继日志) ，General Query Log(一般查询日志)，Slow Query Log(慢查询日志)，Error Log(错误日志)，DDL Log(DDL 日志)等。

二进制日志(binlog)

binlog是记录MySQL中执行变更(增删改)操作的日志(Binary Log由包含描述数据库内容修改的“事件”的文件组成，以二进制格式追加写入这些文件) 。binlog是MySQL的日志在server层，所以无论是什么存储引擎都会有此日志。

binlog有什么作用：

复制：目前我们常说的高可用/高并发，那数据库的高可用/高并发就可以通过各个数据库实例之间复制binlog以达到数据的同步。如mysql的主从复制

恢复：某些数据可以通过执行binlog来恢复。

其实binlog的用处很大的，除了上面官方提到的两种作用外，还可以拓展其使用范围，如模拟mysql的协议，通过监控binlog的变化实现自己的特殊需求。对于复杂点的项目会使用redis和MQ，就会涉及到缓存一致性以及MQ消息处理，这时候就可以通过监控binlog的变化实现对redis/本地缓存/MQ的操作(像Alibaba开源项目Canal)，减少代码的侵入。

MySQL中binlog的三种格式

statement，row(默认)和mixed。在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW

mysql> show global VARIABLES like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.06 sec)

statement(基于语句的日志记录)：每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制（参数配置：--binlog-format=STATEMENT）。
row(基于行的日志记录) ：不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大（参数配置：--binlog-format=ROW）。
mixed(混合日志记录)：上面两种折中的方案，普通操作使用statement格式记录，当无法使用statement的时候使用row格式，例如sql中有函数UUID()等（参数配置：--binlog-format=MIXED）。

binlog的写入机制

binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，当server收到事务的commit命令后，在commit执行之前把binlog cache写到binlog文件中。而非事务表的更新在执行后立即存储在binlog文件中。

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到临时文件中，当线程结束时，临时文件会被删除。

事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache。

mysql实战45讲

如上图write只是把日志写入文件系统的page cache(操作系统内存的一部分)，但是真正实现持久化还是要进行刷盘即执行fsync，执行fsync的时机是由参数sync_binlog控制的

sync_binlog=0的时候，表示每次提交事务都只write ，不fsync（不安全，但是块）；

sync_binlog=1的时候，表示每次提交事务都write 和 fsync（最安全，但最慢）；

sync_binlog=N(N>1)的时候，表示每次提交事务都write ，但累积N个事务后才fsync（安全和速度折中）。

因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。

但是将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的 binlog日志。

来源：丁奇《MySQL实战45讲》

重做日志(Redo Log)

Redo Log是InnoDB中保证数据持久性的，可以在崩溃修复期间纠正不完整事务写入的数据。

Redo Log的使用可以减少磁盘的操作，通过顺序写代替随机写

那究竟Redo Log中记录的是什么：记录的是某数据的具体位置的变更情况，比如说某个事务将系统表空间中的第100号页面中偏移量为1000处的那个字节的值 1 改成 2 我们只需要记录为：将第0号表空间的100号页面的偏移量为1000处的值更新为 2 。

当这句话已经记录到Redo Log的磁盘后，即使系统奔溃了也可以在重启之后根据这句话去修复对应数据，这个就是重做。

Redo Log的写入机制

先写入Redo Log buffer，在写入page cache(操作系统内存的一部分)，最后持久化磁盘中。

首先把日志写入到mysql进程中缓存中，即Redo Log buffer.(缓存操作，很快)

接着是把日志写入到物理上的文件系统page cache里(速度很快)

最后就是持久化到真正的磁盘上（这个相对较慢）

InnoDB提供了innodb_flush_log_at_trx_commit参数控制Redo Log的写入方式

设置为0的时候，表示每次事务提交时都只是把Redo Log留在Redo Log buffer中;

设置为1的时候，表示每次事务提交时都将Redo Log直接持久化到磁盘；

设置为2的时候，表示每次事务提交时都只是把Redo Log写到page cache。

InnoDB有一个后台线程，每隔1秒，就会把Redo Log buffer中的日志，调用write写到文件系统的 page cache，然后调用fsync持久化到磁盘。

来源：丁奇《MySQL实战45讲》