Detailed explanation of redolog in mysql transaction-innodb

redo log

What is redo log? It is a concept generated to restore the state before the crash when the system is restarted due to a crash. MySQL is generated by the innodb engine when the data is modified during operation (the offset in the nth page of a table space is the value at m Update from x to y) log to ensure durability;

When redo log is flushed from log buffer to disk:

Insufficient log buffer space;

Transaction commit (this is also one of the points why transactions affect performance);

Regular flushing (per second), through the flush linked list

The service stops normally;

When doing checkpoint;

The innodb_flush_log_at_trx_commit parameter can be configured to flush to the disk by what mechanism

0 means that it is processed by a background thread;

1 means flash disk synchronously (default value);

2 means writing to the operating system buffer, as long as the operating system is not hung up, it is okay, and the operating system is hung up, and the transaction cannot be guaranteed

concept

MTR (Mini-Transaction) represents an atomic access to the underlying page, this process will generate a redo log;

checkpoint represents the operation process of adding checkpoint_lsn;

Dirty pages: modified data pages

Flush linked list: During the execution of MTR, the modified pages will be added to the flush linked list, using the header insertion method

Data Format:

type: redo日志的类型
space ID:表空间id
page number:数据页面号
data:具体内容

Data storage: Redo log is placed in a 512-byte page, also called a block;

header中:存储该block的元数据:包含页号、使用了多少,已经checkpoint的序号,以及最近一次修改后对应的lsn值(每个页面中都维护了这个值,在恢复脏页时,如果这个值大于checkpoint_lsh,就不恢复)

body: 真正记录redo log的内容

Buffer pool and disk logfile are used circularly. As long as the checkpoint is completed, the space smaller than checkpoint_lsn can be overwritten.

Global variables in redo log

lsn:(log sequence number) records the total amount of redo logs that have been written currently (including log block header and log block trailer space), starting from 8704;

buf_next_to_write marks the amount of logs that have been flushed to disk in the current log_buffer;

flushed_to_disk_lsn: Indicates the amount of redo logs flushed to disk

checkpoint_lsn is used to indicate the weight of the redo log that can be overwritten in the current system, and the space corresponding to chekcpoint_lsn can be overwritten;

SHOW ENGINE INNODB STATUS \G;
Log sequence number 表示 lsn
log flushed up to 表示:flushed_to_disk_lsn
Last checkpoint at 表示 checkpoint_lsn

checkpoint process

Calculate the lsn corresponding to the redo log that can be overwritten (that is, flushed_to_risk_lsn)

Modify the offset corresponding to checkpoint_lsn to the corresponding flushed_to_risk_lsn (the offset has been calculated in the first step, if there is an increase in the process, it will be inconsistent)

How to recover?

The corresponding lsn in redolog is greater than checkpoint_lsn and needs to be processed, and the lsn greater than checkpoint_lsn may or may not be brushed. Starting point: First of all, we have to confirm the starting point of lsn. In the management information of the first file in the redo log file group, there are two blocks that store checkpoint_lsh information, and whichever is larger is selected as the starting point. End point: In the last lsn position, the blocks that are full are 512 bytes, and the blocks that are not full are less than 512. Because it is written sequentially, it means that this is the end point.

Both MySQL and ES have a checkpoint mechanism. When the service process hangs up, the basic principles of restoring data from redo log or translog are similar.

Kafka does not have a similar mechanism. Kafka uses the ack mechanism + minimum synchronization replica set to ensure durability.
If you think it is helpful to you, please pay attention to the official account: 5ycode, and we will continue to update it in the future.

Public account picture