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

This article will explain in detail what logs there are in Mysql, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Redo log

The redo log is on top of the binlog. When a update statement is executed, a redo log is first recorded, and then the binlog log is generated by the executor. Redo log is the log of the InnoDB storage engine layer.

The meaning of the question 1.redo log

First of all, redolog does not exist in the original MySQL storage engine MyISAM, and then there is a plug-in developed by a third-party company, that is, the predecessor of INNODB, and then there is an Innodb storage engine, so redolog is a unique log of innodb. Its purpose is to help mysql record changes quickly and keep the data straight. So every time you update and insert data in MYsql, you need to store the disk, so the cost of IO is too high, so in order to optimize the real-time flushing problem of mysql, Innodb uses to write the log first, and update the cache, and then write to disk when the machine is idle, so you have redo logs.

What are the benefits of the problem 2:redo log?

Redo log can prevent the abnormal restart of the mysql database, you can quickly recover the data in the redo log and restore the previously committed transaction data into memory. This is the crash-self capability of mysql. In addition, redo log is equivalent to a cache, which is written to the cache and then persisted to disk, which is relatively fast.

Question 3: how to ensure data consistency after joining redo log?

What to do with redo log,binlog write failure, so this is set to the problem of stand-alone consistency. Generally speaking, we know that the XA protocol, that is, two-phase commit, is handled in the same way here. The following is a simple update statement execution process, we know the write timing of redo log and binlog, and data recovery issues.

Binlog

There are three formats for binlog: Statement, Row, and Mixed

-replication based on SQL statements (statement-based replication,SBR)

-Row-based replication (row-based replication,RBR)

-mixed mode replication (mixed-based replication,MBR).

Mysql innodb update execution process

1. The executor first finds the data engine to query the rows that need to be operated, and if the data page where the data is located is in memory, it is returned directly to the executor, otherwise it needs to be read from the disk to memory, and the data is returned.

two。 The executor calls the execution engine to write the data to be updated after the operation to the new row data.

3. The engine first updates to memory and records the update operation to redo log, when redo log is in a preparatory phase, and then tells the executor that the execution is complete and the transaction can be committed at any time

4. The executor generates the binlog for this operation and writes the binlog to disk

5. The executor calls the transaction commit interface, and the engine changes the redo log state that has just been written to the commit state, and the whole update operation is completed.

As you can see from the whole step, both redo log and binlog need to be successful in a single transaction to maintain consistency. The so-called system idle time flushing and binlog are two different things, binlog is the transaction commit synchronization is about to complete.

Undo log

Undo log serves two purposes: to provide rollback and multiple row versioning (MVCC), and to achieve atomicity of transactions.

When the data is modified, not only the redo is recorded, but also the corresponding undo is recorded. If the transaction fails or rolls back for some reason, you can use this undo to roll back. Unlike redo log, which records physical logs, undo log is a logical log. It can be assumed that when delete a record, a corresponding insert record is recorded in undo log, and vice versa, when a record is update, it records a corresponding update record. When the rollback is executed, the corresponding contents can be read from the logical records in the undo log and rolled back. Sometimes when it comes to row versioning, it is also achieved through undo log: when a read row is locked by another transaction, it can analyze the previous data of the row record from the undo log, thus providing the row version information and allowing the user to achieve non-locking consistent reading. Undo log is recorded as segment, and each undo operation takes up a undo log segment when recording. In addition, undo log also produces redo log because undo log also implements persistence protection.

Innodb-relay.log

A log file that only appears when relay logMySQL does master-master replication or master-slave replication. He essentially copies binlog, then generates relay log, and then backs up data from slave nodes based on the log.

MySQL master-slave replication involves three threads, one running on the master node (log dump thread) and the other two (I _ thread, SQL thread) running on the slave node.

1. Master thread binlog dump thread: when the slave node connects to the master node, the master node creates a log dump thread to send the contents of the bin-log. When reading an operation in bin-log, this thread locks the bin-log on the master node, and when the read is complete, even before initiating to the slave node, the lock is released.

two。 Slave node IO thread: when the start slave command is executed on the slave node, the slave node creates an I O thread to connect to the master node and request the updated bin-log in the master library. After receiving the update from the binlog dump process of the master node, the iPot O thread saves it in the local relay-log.

3. Slave node SQL thread: SQL thread is responsible for reading the contents of relay log, parsing into specific operations and performing, and finally ensuring the consistency of master-slave data.

About which logs in Mysql are shared here, I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.