Mysql日志binlog、redolog、undolog

2023-12-07 15:44
文章标签 mysql 日志 binlog database redolog undolog

本文主要是介绍Mysql日志binlog、redolog、undolog，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Mysql有多种日志，承担着不同的功能。

BinLog高可用的基石

Binlog是Mysql的server层记录的日志，包含表结构和数据的变更。Binlog有两个常用的作用，一个是用作数据库恢复，通过数据库快照和binlog，我们可以把数据库恢复到任意时刻。另一个是用于数据库复制，通过将binlog传给其他数据库副本，然后再执行binlog中的sql实现数据的同步，构建出一套高可用的数据库服务。

使用show global variables like 'log_bin'; 可以查看日志是否开启。在Mysql5.7默认是不开启，可通过--log-bin=xx开启，xx代表日志文件的名称。在Mysql8.0之后是默认开启，可以使用--skip-log-bin或--disable-log-bin进行禁用。

日志格式

Binlog有三种格式，通过--binlog-format启动参数进行设置，也可以通过系统变量查看当前binlog_format的值。

show global variables like 'binlog_format';
+-------------+-----+
|Variable_name|Value|
+-------------+-----+
|binlog_format|MIXED|
+-------------+-----+

  1. statment，基于语句的模式，记录原始的sql。这种模式存在不确定的问题，例如下面这条语句的now()函数取的是当前时间，在从库重新执行这个sql时，时间值跟主库执行的时间必然不同。

    -- 原始SQL
BEGIN;
update post set updated_date = now() where id <= 10;
COMMIT;-- bin log文件
# at 123456
#210714 12:34:56 server id 1  end_log_pos 123 Query thread_id=123  exec_time=0  error_code=0
SET TIMESTAMP=123456;
BEGIN;
update post set updated_date = now() where id <= 10;
COMMIT;

  2. row（默认值），基于行变更的模式，记录sql影响每条数据的变化。日志里记录的数据就比较多。

    -- 原始SQL
BEGIN;
update post set updated_date = now() where id <= 10;
COMMIT;-- bin log文件
# at 123456
#210714 12:34:56 server id 1  end_log_pos 123 Xid = 456
BEGIN
#210714 12:35:00 server id 1  end_log_pos 234 Table_map: `demo`.`post` mapped to number 123
#210714 12:35:05 server id 1  end_log_pos 345 Update_rows: table id 123 flags: STMT_END_F
### UPDATE `demo`.`post`
### WHERE
###   @1=1
### SET
###   @2='2023-12-01 12:34:56'
#210714 12:35:10 server id 1  end_log_pos 456 Update_rows: table id 123 flags: STMT_END_F
### UPDATE `demo`.`post`
### WHERE
###   @1=2
### SET
###   @2='2023-12-01 12:34:57'
# ... (类似的 Update_rows 记录，覆盖 id 为 3 到 10)
#210714 12:35:15 server id 1  end_log_pos 567 Xid = 456
COMMIT

  3. mixed，statment和row混合模式，默认情况下使用statment格式记录日志，日志内容会少一些，在特殊情况下会使用row格式。例如，使用这些函数，UUID()、FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、LOAD_FILE()或者使用系统变量时，会用到row模式。

写日志的时机

对于innodb引擎。mysql在引擎提交事务时，先保存binlog再完成事务提交。为了保证数据一致性，完整的流程是先把redo log设置成prepare状态，再提交binlog，然后把redo log设置为commit状态。

RedoLog

redolog是innodb引擎提供的日志，日志会记录数据执行sql前后的变化。redo log在磁盘上存储时会有很多种格式类型，下面例子只是说明会记录哪些信息:

-- 原始SQL
BEGIN;
update user set name = '小李' where id = 1;
insert into user(id, name) values(2, '小红');
delete from user where id = 3; 
COMMIT;-- 日志内容示意
TransactionID: 127
PageID: 462
Operation: MULTIPLE_UPDATES
Updates:- UpdateType: UPDATEBefore Image: (1, '小明')After Image: (1, '小李')- UpdateType: INSERTAfter Image: (2, '小红')- UpdateType: DELETEBefore Image: (3, '小张')

使用redo log能提升数据库写入的性能，并且在数据库奔溃时，帮助数据库恢复到崩溃前的状态。

提升写入性能

redo log以AWL（Ahead Write Log）的方式记录日志，通过顺序写日志的方式记录变更。数据库中的记录存是放在磁盘的不同位置，直接修改数据会产生随机io，性能比顺序io要差。

另外，数据库会创建几个(默认4个，8.0.30改成了32个)日志文件合成日志组，循环记录日志。当一个文件满了就会写入到下一个文件，当最后一个文件满了，又重新写入到第一个文件。为了避免新日志写入会覆盖之前的日志，当redo log提交后，会有后台线程清理掉这部分日志数据，把文件空出来。日志组有write pos和checkpoint两个指针来记录新日志写到哪个位置，以及旧日志已经清除的位置。当write pos追上checkpoint时，数据库就要停下来将一部分日志变更sync到磁盘并擦除日志，把位置空出来。

奔溃恢复

数据库发生crash，会导致部分事务的数据还没提交完成。在重启数据库的时候就需要将这部分数据恢复回去。哪些数据需要恢复是根据redo log和bin log综合进行判断。

在描述奔溃恢复逻辑之前，我们先看一下redo log和binlog的写入过程。

两阶段提交

在事务内执行变更sql会生成redo log，因为事务还没提交所以redo log还不能直接落盘。这时会把修改先记录到log buffer，在提交事务时，先把redo log的状态设置为prepare并存入磁盘，然后提交binlog存入磁盘，最后将redo log设置为commit状态存日磁盘。这也是分布式事务常用的方式，是为了保证redo log和binlog的事务性。

然后我们看下，这个过程中不同时刻数据库发生crash需要怎么恢复:

  1. 写入log buffer后，数据库奔溃。因为数据只是写入了内存，redo log和binlog还没记录，所以丢失后就相当于事务回滚了。
  2. redo log完成prepare保存，数据库奔溃。这时binlog还没提交，所以数据变更事件还没同步给从库，所以可以直接回滚本地事务。
  3. binlog已经提交，数据库奔溃。这时redo log已经处于prepare阶段，redo log里的内容是完整的，binlog内容也是完整的，并且binlog的数据已经同步给从库，这时候只能提交事务，把redo log设置为commit。
  4. redo log的commit已提交，那么说明事务已经处理成功，把事务设置成完成。

落盘策略

redo log在事务未提交前会先写入到log buffer。然后在事务提交时，再写入page cache，最后同步回磁盘。根据数据可靠性和性能的考虑，数据库提供了三种落盘策略，由innodb_flush_log_at_trx_commit参数控制:

0：redo log先写入log buffer，由后台线程每秒写入page cache并同步到磁盘。这个方式性能最高，但数据库crash会导致数据丢失。

1：redo log会马上同步到磁盘（这个是默认值），性能差一点，但数据可靠性高。

2：redo log先写入到page cache，由后台线程每秒将日志写入磁盘。这个过程数据库crash不会影响page cache的内容，但是服务器宕机会导致数据丢失。

另外，可以通过innodb_flush_log_at_timeout控制log buffer刷新到磁盘的频率，默认1秒，最大值是2700秒。

UndoLog

undo log记录了事务内的所有数据变更，用于回滚事务和实现一致性读。当数据行更新后后，会生成一条undo log，记录之前的值。然后数据行会有一个隐藏的字段Roll Pointer指针，指向上一条undo log的位置。如果同一条记录有多次更新，就会有多条undo log，形成链表。

单条日志的格式如下:

-- 原始SQL
BEGIN;
update user set name = '小李' where id = 1;
COMMIT;-- 日志内容示意
-------------------------------------
| Transaction ID    | 123456
| Roll Pointer       | 789012
| Transaction Type   | UPDATE
| Table ID           | 456
| Page Number        | 789
| Slot Number        | 1
| Undo Record Type   | Regular Undo
| Undo Record Size   | 100 bytes
| SQL Operation      | UPDATE user SET name = '小李' WHERE id = 1;
| Undo Segment ID    | 789012
| Segment State      | ACTIVE
| Transaction State  | COMMITTED
| Prev Transaction ID| 789455
| 其他记录信息
-------------------------------------

需要回滚事务，就通过记录里的Roll Pointer指针，找到上一条redo log，然后更新成原来的值。

一致性读

在可重复读的事务隔离级别，多次查询同一条记录，看到的结果是一样的，我们称这个现象为一致性读。

一致性读依赖于MVCC，MVCC的多个版本指的是一行记录存在多个版本，记录每次更新就会产生一个新版本。这里的新版本并不是说一行记录会拷贝成多份，记录只存最新的一条，然后之前的版本会各自对应一条undo log，日志里会记录变更内容的原始值。一致性读就是从最新版本开始，沿着redo log链找到当前事务可见的版本，将沿途的undo log都执行一次，就是当前事务能看到的数据状态。

例如：

  1. t1时刻，事务tx1，查询select name,age from user where id = 1；结果是‘小李’和25。
  2. t2时刻，事务tx2，将update user set name = ‘小明’ where id = 1；结果是‘小明’和25。undo log 1记录name=‘小李’
  3. t3时刻，事务tx3，将update user set age= 26 where id = 1；结果是‘小明’和26。undo log 2记录age=25
  4. t4时刻，事务tx1，再查询select name,age from user where id = 1；结果依然是‘小李’和25。这时，会从‘小明’和26开始，执行undolog 2，结果得到‘小明’和25；再执行undelog 1，结果得到‘小李’和25。

UndoLog对事务性能的影响

事务变更数据的时候需要记录undo log，undo log的数量是有限的。undo log存放于undo log segments，undo log segments会包含多个事务的undo log，但同时只能被一个事务使用。所以新事物只能等之前的事务提交或回滚之后才能，才能用它的undo log segments。undo log segments默认是数据页的1/16，innodb数据页是16k，所以undo log segments默认是1024。

undo log segments存放于rollback segments，rollback segments默认是128个，通过show global variables like 'innodb_rollback_segments'; 查看。

rollback segments从undo tablespace或者global temperary tablespaces（全局临时表空间）分配。undo tablespace默认分配2个，也可以自己添加。global temperary tablespaces是1个。undo log数量公式是：

undo log数量 = tablespaces * rollback segments * undo log segments

所以，非内存表能分配2×128×1024=262144个undo log segments，内存表1×128×1024=131072个undo log segments，

一个事务会消耗的undo log数量是不一样的，有四种情况会消耗undo log:

  1. 非内存表的所有insert会放入一个undo log
  2. 非内存表的所有update或delete会放入一个undo log
  3. 内存表的所有insert会放入一个undo log
  4. 内存表的所有update或delete会放入一个undo log

如果事务里包含这四种操作，就会占用四个log rollback segment。当undo log数量不够时，就会影响事务写入。

这篇关于Mysql日志binlog、redolog、undolog的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/466420。

相关文章

Mysql 中的多表连接和连接类型详解

Mysql 中的多表连接和连接类型详解

《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据... 目录什么是多表连接？1. 内连接（INNER JOIN）2. 左连接（LEFT JOIN 或 LEFT
阅读更多...
mysql重置root密码的完整步骤(适用于5.7和8.0)

mysql重置root密码的完整步骤(适用于5.7和8.0)

《mysql重置root密码的完整步骤(适用于5.7和8.0)》：本文主要介绍mysql重置root密码的完整步骤,文中描述了如何停止MySQL服务、以管理员身份打开命令行、替换配置文件路径、修改... 目录第一步：先停止mysql服务，一定要停止！方式一：通过命令行关闭mysql服务方式二：通过服务项关闭
阅读更多...
SQL Server数据库磁盘满了的解决办法

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发
阅读更多...
mysql主从及遇到的问题解决

mysql主从及遇到的问题解决

《mysql主从及遇到的问题解决》本文详细介绍了如何使用Docker配置MySQL主从复制,首先创建了两个文件夹并分别配置了`my.cnf`文件,通过执行脚本启动容器并配置好主从关系,文中还提到了一些... 目录mysql主从及遇到问题解决遇到的问题说明总结mysql主从及遇到问题解决1.基于mysql
阅读更多...
MySQL的索引失效的原因实例及解决方案

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6
阅读更多...
Linux下MySQL8.0.26安装教程

Linux下MySQL8.0.26安装教程

《Linux下MySQL8.0.26安装教程》文章详细介绍了如何在Linux系统上安装和配置MySQL,包括下载、解压、安装依赖、启动服务、获取默认密码、设置密码、支持远程登录以及创建表,感兴趣的朋友... 目录1.找到官网下载位置1.访问mysql存档2.下载社区版3.百度网盘中2.linux安装配置1.
阅读更多...
PostgreSQL如何用psql运行SQL文件

PostgreSQL如何用psql运行SQL文件

《PostgreSQL如何用psql运行SQL文件》文章介绍了两种运行预写好的SQL文件的方式：首先连接数据库后执行,或者直接通过psql命令执行,需要注意的是,文件路径在Linux系统中应使用斜杠/... 目录PostgreSQ编程L用psql运行SQL文件方式一方式二总结PostgreSQL用psql运
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2