数据库日志redologbinlog的解读

1 为什么要有redo log?

一般当MYSQL更新数据时&＃xff0c;有两种情况&＃xff0c;追加数据或定位到已经存在的一条数据进行修改。然而磁盘随机读写速度很慢&＃xff0c;无法满足高IO操作的场景。为了提高写入效率&＃xff0c;一般我们可以先将数据写入内存&＃xff0c;空闲时再批量刷入磁盘。但是这样就产生了一个问题&＃xff1a;内存中的数据不是持久化的&＃xff0c;如果掉电&＃xff0c;数据就会丢失。

为了解决数据丢失的问题&＃xff0c;MySQL引入了redo log来解决这个问题。这被称为**WAL(Write-Ahead Logging)**技术&＃xff0c;对于非内存数据库&＃xff0c;这是一种提高IO效率的常见做法。通过WAL技术&＃xff0c;使得在发生崩溃时依靠日志记录&＃xff0c;就可以恢复数据&＃xff0c;从而确保数据不丢失。

关于WAL(Write-Ahead Logging)

对于非内存数据库&＃xff0c;磁盘I/O操作是导致数据库性能低下的主要原因。在数据量相同的情况下&＃xff0c;使用WAL日志的数据库系统&＃xff0c;可以成倍减少磁盘写入操作&＃xff0c;大大提高了数据库磁盘I/O操作的效率&＃xff0c;从而提高了数据库性能。

2 redo log的实现

当需要更新记录时&＃xff0c;InnoDB引擎先写redo log并更新内存。在适当的时候&＃xff0c;例如当磁盘空闲时&＃xff0c;将redo log数据刷新到磁盘。在InnoDB引擎中&＃xff0c;redo log的大小是固定的&＃xff0c;例如&＃xff0c;它可以配置为一组四个文件&＃xff0c;每个文件大小为1GB&＃xff0c;因此总共可以记录4GB的操作。

write pos用于记录当前位置&＃xff0c;在写入时移动&＃xff0c;注意是循环移动的。checkpoint用于标记要擦除的位置&＃xff0c;在擦除时往后移动&＃xff0c;注意也是循环移动的。记录在被擦除之前会更新到磁盘数据文件。

write pos到check point之间的部分是redo log空着的部分&＃xff0c;用于记录新的记录&＃xff1b;check point到write pos之间是redo log待落盘的数据页更改记录。

如果write pos完成最后一个文件的写入&＃xff0c;它将移动到ib-log-file-0并再次开始写入。

如果write pos赶上check point&＃xff0c;则表示redo log已满&＃xff0c;因此无法执行新的更新&＃xff0c;必须停止并擦除一些数据&＃xff0c;将数据同步到磁盘&＃xff0c;然后再继续以上步骤。

3 如何查看redo log 配置参数

每个InnoDB存储引擎至少有一个重做日志文件组&＃xff0c;每个文件组至少有两个重做日志文件&＃xff0c;默认为ib-log-file-0和ib-log-file-1。
可使用以下命令查看参数配置信息&＃xff1a;

show variables like &＃39;%innodb_log%&＃39;;


4 关于Binlog

redo log是InnoDB引擎特有的&＃xff0c;可以记录数据&＃xff0c;并保证数据的安全性&＃xff0c;那么其它存储引擎如何记录数据呢&＃xff1f;在Server级别&＃xff0c;MySQL有自己的日志&＃xff0c;即Binlog&＃xff08;归档日志&＃xff09;。了解MySQL逻辑架构的同学应该知道&＃xff0c;Mysql不是一个整体&＃xff0c;MySQL &＃61; Server层 &＃43; 不同的数据存储引擎。

Binlog用于记录MySQL数据库表结构和表数据的所有变更操作&＃xff0c;但不包括select和show查询操作。Binlog以事件的形式记录下来&＃xff0c;包括语句消耗的时间。Binlog日志最重要的两个使用场景如下&＃xff1a;
主从复制&＃xff1a;在主库开启Binlog功能&＃xff0c;使主库可以将Binlog传递给从库&＃xff0c;从库获取Binlog并实现数据恢复&＃xff0c;实现主从数据一致性。
数据恢复&＃xff1a;通过mysqlbinlog工具恢复数据。

binlog日志有三种格式&＃xff0c;分别为STATMENT、ROW和MIXED。在 MySQL 5.7.7之前&＃xff0c;默认的格式是STATEMENT&＃xff0c;MySQL 5.7.7之后&＃xff0c;默认值是ROW。日志格式通过binlog-format指定。

STATMENT&＃xff1a;
Statement模式记录的是所有数据库操作对应的SQL语句&＃xff0c;如INSERT、UPDATE 、DELETE 等DML语句&＃xff0c;CREATE 、DROP 、ALTER 等DDL&＃xff0c;所以&＃xff0c;从理论上讲&＃xff0c;只要按顺序执行这些SQL 语句&＃xff0c;就可以实现不同数据库间的数据复制。

优点&＃xff1a;不需要记录每一行的变化&＃xff0c;减少了binlog日志量&＃xff0c;节约了IO, 从而提高了性能&＃xff1b;
缺点&＃xff1a;在某些情况下会导致主从数据不一致&＃xff0c;比如执行sysdate()、sleep()等。

ROW&＃xff1a;
Row模式更关心每一行的变更&＃xff0c;这种在实际应用中会更普遍一点&＃xff0c;因为有时候更关心数据的变化情况&＃xff0c;例如一个订单被创建出来&＃xff0c;司机通过App接收了某个运输任务等。

优点&＃xff1a;不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题&＃xff1b;
缺点&＃xff1a;会产生大量的日志&＃xff0c;尤其是alter table的时候会让日志暴涨

MIXED&＃xff1a;
Statement模式和Row模式的混合体&＃xff0c;因为Statement模式和Row模式都有各自的不足&＃xff0c;前者可能会导致数据不一致&＃xff0c;而后者则会占用大量的存储空间。一般的复制使用STATEMENT模式保存binlog&＃xff0c;对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

5 为什么有两个日志&＃xff1f;

MySQL一开始没有InnoDB引擎&＃xff0c;MySQL自带的引擎是MyISAM&＃xff0c;但是MyISAM没有处理崩溃恢复数据的能力&＃xff0c;binlog日志只是用来归档。InnoDB后来作为插件添加&＃xff0c;它实现了自己的日志系统来保护数据&＃xff0c;防止崩溃丢失数据问题。

6 bin log和redo log的区别

内容不同&＃xff1a;redo log是物理日志&＃xff0c;记录的是“在某个数据页上做了什么修改”&＃xff1b;binlog是记录的这个语句的原始逻辑&＃xff0c;比如“给D&＃61;2这一行的C字段加1”。

不同级别&＃xff1a;redo log只能和InnoDB引擎一起使用&＃xff0c;而binlog位于MySQL服务器级别&＃xff0c;可用于所有引擎。

磁盘存储形式不同&＃xff1a;redo log是循环写的&＃xff0c;空间固定会用完&＃xff1b;binlog是可以追加写入的。“追加写“是指binlog文件写到一定大小后会切换到下一个&＃xff0c;并不会覆盖以前的日志。

写入的时间不同&＃xff1a;binlog通常在一个事务提交时写入&＃xff0c;而redo log会在不同的时间点写入。&＃xff08;注意&＃xff1a;未提交的事务redo log也可能被刷新到磁盘&＃xff09;

对于InnoDB存储引擎而言&＃xff0c;只有在事务提交时才会记录biglog&＃xff0c;此时记录还在内存中&＃xff0c;那么biglog是什么时候刷到磁盘中的呢&＃xff1f;mysql通过sync_binlog参数控制biglog的刷盘时机&＃xff0c;取值范围是0-N&＃xff1a;
0&＃xff1a;不去强制要求&＃xff0c;由系统自行判断何时写入磁盘&＃xff1b;
1&＃xff1a;每次commit的时候都要将binlog写入磁盘&＃xff1b;
N&＃xff1a;每N个事务&＃xff0c;才会将binlog写入磁盘。
从上面可以看出&＃xff0c;sync_binlog最安全的是设置是1&＃xff0c;这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能&＃xff0c;因此实际情况下也可以将值适当调大&＃xff0c;牺牲一定的一致性来获取更好的性能。


作用不同&＃xff1a;binlog日志只用于归档&＃xff0c;只依靠binlog是没有crash-safe能力的。但只有redo log也不行&＃xff0c;因为redo log是InnoDB特有的&＃xff0c;且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录&＃xff0c;才能保证当数据库发生宕机重启时&＃xff0c;数据不会丢失。

7 两阶段提交

由于redo-log是在InnoDB层&＃xff0c;而bin-log是在 Server 层&＃xff0c;这样就引入了一个新的问题。如果redo log写入成功&＃xff0c;但是在bin-log写入磁盘之前&＃xff0c;程序crash了&＃xff0c;说明事务还没有提交&＃xff0c;所以redo-log里写入的新数据是无效的。重新启动数据库进行数据恢复会将redo-log数据恢复到磁盘&＃xff0c;从而创建无效数据。

为了解决这个问题&＃xff0c;引入了两阶段提交。

在第一阶段&＃xff0c;redo-log写入并处于准备状态。Server层将bin-log数据保存到磁盘&＃xff0c;然后执行器调用引擎的提交事务接口提交事务&＃xff0c;把redo-log变更为已提交状态&＃xff0c;更新完成。通过两阶段提交协议保证了redo-log数据和bin-log数据的一致性。

8 一条更新语句的执行过程

假设我们现在要执行SQL&＃xff1a;

update table_test set a &＃61; a&＃43;1 where id &＃61; 2;


首先&＃xff0c;客户端通过连接器连接并确定权限。

验证后&＃xff0c;SQL会经过解析器进行词法和语法分析&＃xff08;AST&＃xff09;&＃xff0c;如果是Update语句&＃xff0c;MySQL将清除查询表的所有查询缓存table_test。&＃xff08;一般不建议开启查询缓存&＃xff0c;通常会影响性能&＃xff09;。

优化器优化经过验证的SQL&＃xff0c;匹配id索引&＃xff0c;并生成执行计划。

执行器获取最终的SQL&＃xff0c;并调用相应存储引擎的接口开始执行该更新SQL。

InnoDB引擎打开一个事务&＃xff0c;执行引擎首先从内存中查询是否有数据id&＃61;2&＃xff0c;如果匹配则设置对应的数据为field&＃43;1&＃xff0c;然后将其保存到内存中。如果内存中没有查询到id&＃61;2的数据&＃xff0c;那么它将以页的形式加载磁盘中的数据到内存&＃xff0c;然后更新并保存到内存中。

然后InnoDB引擎会将数据行保存到redo-log&＃xff0c;此时处于准备状态&＃xff0c;表示已经准备好可以提交事务了。

执行器将生成相应的bin-log并将其写入磁盘。

事务被提交&＃xff0c;然后redo-log也被提交。

这样事务就执行完成了&＃xff01;&＃xff01;&＃xff01;&＃xff01;