Shulou(Shulou.com)06/03 Report--

Editor to share with you what are the differences between undo, redo and binlog in Mysql. I believe most people don't know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Preface

There are six kinds of log files in MySQL, namely: redo log (redo log), rollback log (undo log), binary log (binlog), error log (errorlog), slow query log (slow query log), general query log (general log), relay log (relay log).

Among them, redo log and rollback log are closely related to transaction operations, and binary logs are also related to transaction operations. These three kinds of logs are of great significance for understanding transaction operations in MySQL.

The core role of the relationship with different engines is that the life cycle log type undo log belongs to the unique rollback of the innodb engine to ensure the "atomicity" of the transaction. Before the transaction log transaction starts, recording the field logical log in a similar "snapshot" way redo log belongs to the unique redo of the innodb engine to ensure the "persistence" of the transaction. After the transaction log transaction starts, the physical log binlog works in the Server layer of mysql. Regardless of which engine is used to record the replication transaction of master-slave node data, the logical log before the completion of the commit phase [undo log]

Before the transaction starts, the current transaction version is generated into undo log (Tips:undo log also generates redo log to ensure the reliability of undo log).

After the transaction is committed, the undo log cannot be deleted immediately, but is put into the linked list to be cleaned, and it is up to the purge thread to determine whether there are other transactions that use the version information of the previous transaction in the table in the undo segment, thus determining whether the log space of undo log can be cleaned.

One of the four major characteristics of database transactions is atomicity, specifically, atomicity refers to a series of operations on the database, either all successful or all failed, it is impossible to have partial success.

In fact, the bottom layer of atomicity is achieved through undo log. Undo log mainly records the logical changes of the data, such as an INSERT statement corresponding to a undo log of DELETE, and for each UPDATE statement, a undo log of the opposite UPDATE, so that when an error occurs, it can be rolled back to the data state before the transaction. For example, the original record in the user table is as follows:

Idname1xiaoming

The undo log generated when executing sql update user set name = 'xiaohong' where id = 1; is about update user set name =' xiaoming' where id = 1

At the same time, undo log is also the key to the implementation of MVCC (multi-version concurrency control).

[redo log]

How does mysql ensure the persistence of transactions? The easiest thing to do is to flush all the data pages that the transaction involves modification to disk each time the transaction commits. But there are serious performance problems in doing so, mainly in two aspects:

Because Innodb interacts with disk on a page-by-page basis, and a transaction is likely to modify only a few bytes in a data page, brushing the complete data page to disk at this time is a waste of resources!

A transaction may involve modifying multiple data pages, and these data pages are not physically contiguous, so write performance using random IO is too poor!

Therefore, mysql designs the redo log mechanism and optimizes its performance through WAL (Write-Ahead Logging) technology. The core of WAL is to write log disks by sequential IO first, and then write data disks by random IO, which saves the IO consumption of random writes to disks. Every time mysql executes a DML statement, it first appends the record order to redo log buffer and updates the data in memory, and then persists in batch when there are free threads, insufficient memory and full Redo Log.

[binlog]

Binlog is the logical log of mysql and is recorded by the Server layer. The recording object is the written operation (excluding query) information of any database engine, and is stored on disk in binary form.

In practical application, there are two main usage scenarios of binlog, namely, master-slave replication and data recovery.

Master-slave replication: enable binlog on the Master side, then send the binlog to each Slave side, and the Slave side replays the binlog to achieve master-slave data consistency.

Data recovery: restore data by using the mysqlbinlog tool.

In the process of data update, in case the system fails and restarts abnormally in the process of updating data, how to ensure the persistence and atomicity of the transaction? The summary is as follows:

The snapshot site where the data record before this update is recorded (that is, write undo log)

Read the data needed for this update into memory

Update data in memory (efficient)

Write redo log and set redo log status to prepare

Write binlog

Set redo log status to commit

Based on the above simplified undo log, redo log, and binlog writing processes, let's sort out the reliability guarantees of atomicity, persistence, and consistency:

A) if a failure occurs at any of the steps in 1-2-3, and there is no unfinished record in redo log after fault recovery, you only need to roll back the undo log recovery site after fault recovery.

B) if a failure occurs at any of the steps in step 4amp 5, and the redo log is found to be in the prepare state after the fault is recovered, it is further determined whether the binlog has been written:

If the binlog has been written, then re-execute the relevant records of the redo log until the commit state is successfully reached (master-slave consistency)

If binlog is not written, roll back the undo log recovery site (atomicity);

C) if a failure occurs at step 6 and the redo log is found to be in the commit state after recovery, indicating that the process is all completed normally, nothing needs to be done.

The above is all the contents of the article "what are the differences between undo, redo and binlog in Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.