What are MySQL Redo and Undo logs?

1. Introduction to Redo log

Redo log is a physical log that records page changes.

1.1 The role of Redo log

• Improve database writing efficiency

• Ensure that the database does not lose data and perform data recovery

1.2 Redo log format and type

The so-called REDO organization method is how to record the required REDO content into the disk file to facilitate efficient REDO writing , read, restore and clean. We divide REDO into three layers from top to bottom: logical REDO layer, physical REDO layer and file layer.

• Logical REDO layer

This layer is the real REDO content, a sequential data flow, REDO consists of multiple REDO records are connected at the end and have a globally unique incremental offset sn. InnoDB will maintain the maximum value of the current sn in the global log_sys and increase the sn by the REDO content length each time data is written.

• Physical REDO layer

The disk is a block device. InnoDB also uses the concept of Block to read and write data. The length of a Block is equal to OS_FILE_LOG_BLOCK_SIZE The size of a disk sector is 512B, and the smallest unit of each IO read and write is a Block.

• File layer

Eventually REDO will be written to the REDO log file, and the log file is written in a loop

2. The process of writing Redo logs (MTR)

• The operations of the user thread will first be written to the log buffer The logs in

• log buffer will be written sequentially to the Redo logical log

• and then will be written to disk for persistence. Store to disk

• Notify the user that the storage is completed

3. Checkpoint mechanism

Checkpoint is The marking bit of dirty pages in the Redo log has the following two functions:

• Maintain the Redo log to facilitate cleaning

• Reduce Restart workload

Database recovery process:

• Read checkpoint Information•Read the remaining logs starting from the checkpoint position

• Parse the log and build a hash table according to space_no and page_id

• Apply Redo log, Redo Log playback ensures idempotence

• Parse binlog and build xid list

• Scan the rollback segment to build a list of pending transactions

• Roll back transactions that are not in the xid list

4. Introduction to Undo log

The Undo log is a logical log that records the incremental changes of data, and its function is to ensure the atomicity of transactions and transaction concurrency control. Rewritten: The Undo log serves to guarantee the atomicity of transactions and control their concurrency by logging the incremental changes in data, functioning as a logical log.. It can be used to roll back transactions, implement multi-version control (MVCC), and resolve read-write conflicts and consistent reads.

5. MVCC

The meaning of MVCC:

• ##Reading and writing do not block each other;

• Reduce the probability of deadlock;

• Achieve consistent reading.

The role of UndoLog in MVCC:

##Each transaction has a single-increment transaction ID;
The row record of the data page contains DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR;
DB_ROLL_PTR concatenates all snapshot records of the data row through the linked list structure stand up.

The above is the detailed content of What are MySQL Redo and Undo logs?. For more information, please follow other related articles on the PHP Chinese website!