MySQL binlog与redo log

binlog

binlog存储的是逻辑日志，即每次执行的SQL语句

binlog（归档日志）属于Server层也有自己的日志

Binlog的作用是做数据复制，主从同步，或者通过Canal读取binlog的变更日志进行业务处理

可以通过整库全量备份+binlog重放，将数据恢复到任意时间点的状态

-- 查看binlog格式
show GLOBAL VARIABLES like 'binlog_format%';
-- 查看binlog配置信息
show variables like 'log_%';
-- 查看当前正在写入的binlog文件名
show master status;
-- 查看指定binlog文件的内容
show binlog events in 'mysql-bin.000034';

设置MySQL binlog，修改my.cnf

[mysqld]
log-bin=mysql-bin # 开启 binlog
binlog-format=ROW # 选择 ROW 模式  可选Statement,MiXED,以及ROW

下图为本地binlog文件，从文件大小看binlog应该是固定大小的文件，binlog30/31文件大小都写满了104.9M，032没有写满就创建了033文件，是因为我修改了binlog的格式为Statement，不同格式的binlog不能存储在同一个文件，在文末更正说明

Statement格式的binlog如下图

ROW格式的binlog如下图

# 对ROW格式的binlog进行格式化
sudo /usr/local/mysql-8.0.26-macos11-x86_64/bin/mysqlbinlog -vv --base64-output=decode-rows  /usr/local/mysql-8.0.26-macos11-x86_64/data/mysql-bin.000034 > mysql-bin.000034.txt

格式化后如下图

redo log

redo log主要作用是崩溃恢复。

redo log是InnoDB引擎特有的日志

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中D（Durability持久性）的要求。

Write Ahead Log利用的是循环写文件如下图

bin log VS redo log

区别

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

一条更新语句的执行过程

update T set c=c+1 where ID=2;

执行器和InnoDB引擎在执行这个简单的update语句时的内部流程。

1. 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

这里我给出这个update语句的执行流程图，图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。

update语句执行流程

你可能注意到了，最后三步看上去有点“绕”，将redo log的写入拆成了两个步骤：prepare和commit，这就是”两阶段提交”。

两阶段提交

如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

更正：不同格式的binlog是可以放在同一个文件的

下图操作为切换binlog格式的输出，Statement->ROW->Statement

SET@@session.binlog_format=’Statement’;

SET@@session.binlog_format=’ROW’;

SET@@session.binlog_format=’Statement’;