update执行流程(MySQL redo log和binlog详解)

create table test(
    id bigint primary key auto_increment,
    score int
) ENGINE = InnoDB;

insert into test(score) value(1);
insert into test(score) value(2);

update test set score = 3 where id = 2;

update语句也需要经过连接器、分析器、优化器、执行器，但是update语句相比select语句还是有很大不同的，更新流程设计两个重要的日志模块：

• redo log：重做日志
• binlog：归档日志

redo log

什么是redo log？

在MySQL中，每一次更新并不会直接写入磁盘，因为如果每次都需要写入磁盘首先涉及到从磁盘中找到记录（随机IO，随机IO是很耗时的）然后更新。

每次更新后先写日志，再写磁盘（在合适的时候，比如空闲或者日志满了的时候）这就是WAL技术。

redo log是InnoDB独有的日志模块。

每当有一条记录更新时，InnoDB引擎会将记录写入redo log并且更新内存，这时候就算更新完成。InnoDB引擎会在合适的时刻将变更记录刷新到磁盘。

InndoDB redo log是固定大小的，是可配置的，通过下面的SQL我们可以看到一些关于redo log的配置：

show variables like 'innodb_log%';

• innodb_log_file_size：每一个redo log日志文件的大小，单位为字节，这里默认值是48M
• innodb_log_files_in_group：有几个redo log文件为一组，这里默认是2

通过以上两个参数，我们可以看出redo log总共可以记录98MB的操作。

redo log是如何工作的？

redo log并不能无限增长，有固定的大小限制，因此我们在写满以后需要一些机制进行处理，以便保证redo log一直可用（数据库能正常更新），主要用到的一些参数如下：

• write pos是redo log当前记录的位置，一边写一边后移，当写到最后一个文件末尾以后需要重新回到第一个文件
• checkpoint是当前要擦除的位置，也是往后推移并循环的，擦除之前需要把记录更新到数据文件
• write pos和checkpoint之间的位置就是可以追加记录的空闲空间

假设擦除的慢（checkpoint移动的慢），写入快（write pos移动的快），如果write pos追上了checkpoint，此时数据库将不能执行新的更新，必须先擦除掉部分记录，然后向前移动check point。

借助redo log，InnoDB可以保证数据及时异常发生重启，之前提交的记录也不会丢失。（crash-safe）

如何保证redo log不丢失？

• innodb_flush_log_at_trx_commit：将该参数设置为1（默认值为1），设置为1以后，表示每次事务的redo log都直接持久化到磁盘，从而保证MySQL异常重启数据不丢失

正常运行的实例数据落盘和redo log有什么关系？

实际上数据最终落盘和redo log没有多大关联，redo log没有记录数据页的完整数据，所以没有能力更新磁盘数据页。

• 在正常的运行的实例，数据页被修改以后和磁盘数据页不一致称为脏页，最终数据落盘就是内存中的数据页写入到磁盘，和redo log没有任何关系
• 在崩溃恢复的场景中，InndoDB如果判断一个数据页可能丢失了更新就会把该页读到内存，然后使用redo log更新内存内容。更新完成以后，内存也变为脏页，就回到了上一种情况

redo log buffer是什么？

在一个事务的更新过程中，日志可能会写多次，比如：

begin;
insert into t1 ..;
insert into t2 ..;
commit;

该事物需要往两个表中插入数据，插入数据的过程中，生成的redo log得先保存起来，但是又不能在没commit的时候就直接写入到redo log文件里。

redo log buffer就是用来存储redo日志的。

真正的将日志写入到redo log文件(ib_logfile+数字)是在执行commit语句的时候执行。

binlog

redo log是InndoDB引擎特有的日志模块，但是binlog是Server层自己的日志。

为什么需要两份日志？

• MySQL最初自带的引擎是MyISAM，MyISAM只适合归档，不具备crash-safe的能力
• InndoDB是后来另一个公司以插件的形式引入到MySQL，为了解决binlog的无crash-safe的能力，就研发并使用redo log来实现crash-safe能力

redo log和binlog的不同点？

• redo log是InnoDB引擎独有，binlog是Server层日志，所有引擎都可以使用
• redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是语句的原始逻辑
• redo log是循环写，固定大小，空间会使用完；binlog是追加写入，写到一定大小后切换到下一个文件继续写，不会覆盖以前的日志

如何保证binlog不丢失？

• sync_binlog：将该参数设置为1（默认值为1），设置为1以后表示每次事务的binlog都会持久化到磁盘，从而保证MySQL异常重启后binlog不丢失。

binlog的形式有几种？

• statement：记录的是SQL语句
• row：记录行的内容，一条更新前，一条更新后
• mixed：混合模式，也不推荐使用

一般我们会采用ROW的形式做binlog，因为如果采用statement的话碰到时间有可能会导致主从数据不一致。

-- 该命令可以查看binlog格式，默认是ROW
show variables like 'binlog_format';

-- 获取binlog文件列表
show binary logs;

-- 查看当前正在写入的binlog文件
show master status;

-- 查看第一个binlog的内容
show binlog events;

-- 查看指定binlog文件的内容
showe binlog events in 'binlog.000006';

# 找到mysqlbinlog工具位置
find / -name "mysqlbinlog"

# 将binlog导出
/usr/bin/mysqlbinlog /var/lib/mysql/binlog.000006 -r test.sql

# 查看statement格式的binlog
/usr/bin/mysqlbinlog /var/lib/mysql/binlog.000006

# 查看row格式的binlog
/usr/bin/mysqlbinlog -v /var/lib/mysql/binlog.000006

MySQL如何知道binlog是否完整？

• statement格式的binlog，最后会有COMMIT
• row格式的binlog，最后会有一个XID event

MySQL 5.6.2以后还引入了binlog-checksum参数用来验证binlog内容的正确性。

UPDATE语句的执行流程

update test set score = 3 where id = 2;

整个update语句中牵涉到写redo log和binlog，并且redo log在前，binlog在后，并且redo log的写入被拆分成了prepare和commit两个步骤，这就是两阶段提交在数据库中的应用。

DBA如何对数据库进行恢复？

如果你的DBA告诉你半个月内的数据都可以进行恢复，那么他必定保存了最近半个月的binlog，同时它会对数据库进行定期全量备份，定期视系统重要性可以一天一备、一周一备等。

假设我们的数据库系统是一天一备（假设时间是0点），老王在操作数据库时（假设时间是12点）不小心误删了一张表，那么我们此时如何对数据库进行恢复呢？

1. 首先，我们找到最近的一次全量备份（也就是今天误删数据那天0点时的全量数据），从这个备份恢复到临时数据库
2. 然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的时刻
3. 此时临时库和误删之前的线上库一致，然后可以将表中的数据从临时库取出来按需恢复到线上库去。

redo log写完直接提交，binlog再写会有什么问题？

-- score原始值为1
update test set score = 3 where id = 2;

假设我们的update语句在写完redo log，binlog还没写时系统发生了crash。

由于redo log写完以后，系统即使发生crash，仍然能够把数据恢复，也就是说恢复后的score值为3。

但是由于binlog没有进行写入，所以binlog的记录里没有这次变更。此时如果用binlog恢复临时库或者做主从同步时，临时库或者从库就会缺少这次更新，恢复出来的这一行score的值为1，与原库数据不同

先写binlog，再写redo log有什么问题？

-- score原始值为1
update test set score = 3 where id = 2;

假设我们的update语句在写完binlog，redo log还没写时系统发生了crash。

如果binlog写完之后发生crash，但是redo log还没写，原库恢复以后这一行score的值依旧是1。但是如果此时用binlog恢复数据库时辛苦的score值将会为3，与原库也不一致。

redo log和binlog都可以表示事务的提交状态，两阶段提交是为了保证redo log和binlog的状态保持逻辑上的一致性。

两阶段提交如何保证日志逻辑的一致性？

假设redo log处于prepare阶段发生了crash，此时由于binlog没写，redo log也没有提交，所以在崩溃恢复时这个事务会回滚，binlog没写所以也不会传到备库。

假设binlog写完，但是redo log还没commit之前发生了crash，此时MySQL在崩溃恢复时会有一定的处理逻辑？

1. 如果redo log里面的事务是完整的（有commit标识），则直接提交
2. 如果redo log里面只有完整的prepare，则判断对应的事务binlog是否存在且完整，如果完整则提交事务，否则回滚事务。

redo log和binlog如何关联？

redo log和binlog有一个共同的数据字段是XID，在崩溃恢复时，会顺序扫描redo log：

• 如果redo log既有prepare，又有commit，则直接提交
• 如果redo log只有prepare，则会拿着XID去找binlog，如果binlog里面有则提交，否则回滚