This article was originally posted on the public account maimo Coding.

Address: school recruit mysql binlog those thing | log module/redolog/undolog

directory

• background
• Physical logs and logical logs
• Log module: Redo log
  • Redo log generation background
  • Redo log basic concepts
  • Redo log Log format
  • Redo log usage scenarios
• Log module: bin log
  • Basic concepts of bin log
  • Bin log Disk flushing mechanism
  • Bin log usage scenarios
• Log module: undo log
  • Basic concepts of undo log
  • Usage scenarios of undo log
• Binlog /redo log/undo log
• Small talk
• Welcome to join my official account [Maimocoding] pk big factory together

background

Logs are an important part of the mysql database and record various status information during the running of the database. Mysql logs are classified into error logs, query logs, slow query logs, transaction logs, and binary logs. For development purposes, we focus on binary logs (bin logs) and transaction logs (including redo logs and undo logs), which are described in more detail later in this article.

Physical logs and logical logs

Physical log and logical log are not familiar with each other, so they are not familiar with each other.

• Physical log: in layman’s terms, only “I” myself can use, others can not share my “physical” format, private.
• Logical logs: Can be used by other engines and are shared by all engines.

Log module: Redolog

Redo log generation background

Mysql: Redo log: start with a story. There was once a tavern where the landlord kept a powderboard, which was one of his magic tricks. If there were not many people who owed money, he could make a note of the people who owed money and the accounts on the chalk board. If there were a lot of credit accounts, he would need an extra book to keep track of all the credit accounts because there was not enough room on the board.

When someone asks for credit, there are two ways employers can do it:

• Open the books, find the records of the credit holders, and make additional credit records
• Write the credit person’s record on the pink board first, the moment of low traffic, and then update the credit account

If the boss uses the first method, every time someone asks for credit, he would first have to open a thick ledger, look up the customer’s name page by page, and make a register. You see, if there are not many credit books, the boss will have an easier track record of finding credit people. If there are several credit books, the boss will have a headache looking for them one by one.

The process of plan one is mind-boggling. Compared to the second solution, it is relatively easy for the boss, who only needs to write the credit person’s information on the pink board, and then update it to the credit book when the traffic is low.

Mysql also has the same problem. If every data operation is written to the disk, the disk must first find the corresponding record and then update it. The IO cost of the whole process is relatively high. In order to improve performance, mysql uses the boss fan board method, which stores the updated data someplace and then writes it to disk when it is free.

Mysql stores update results in the redo log. Next, let’s have a good conversation with the protagonist.

Redo log basic concepts

Redo log InnoDB storage engine layer log (also known as redo log) is used to record transaction changes. The redo log is recorded regardless of whether the transaction was committed successfully or not.

The Write Ahead Logging (WAL) technology is often mentioned in mysql.

In particular, when a record needs to be updated, the InnoDB engine first updates the record to the redo log and then updates the memory. At the same time, the InnoDB engine stores redo log records to disk at idle times.

Redo log Log mode

Due to theredo logChanges to the data page are recorded, and such records are not necessarily kept permanently, soredo logIn implementation, the size is fixed and the writing cycle is adopted. When the record is written to the end, it will be written from the beginning again, as shown in the figure below.As the picture shows,write posIs the position of the current record, moving back as you write, returning to the beginning of file 1 when you reach the end of file 4.check pointIs the location where the record is currently written to the data file, and is also backward and cyclic.

If described in combination with the boss fan board scenario above, write POS means that the boss writes the credit person record position sequentially on the fan board. For mysql, write pos moves later. The check point is the position where the boss writes the record on the powder board to the credit book. When the boss writes the record on the credit book, the record on the powder board will be erased. For mysql, the check point is moved later.

Redo log usage scenarios

• Used for system crash recovery (crash-safe)

Log module :binlog

Basic concepts of bin log

Bin log is a log module shared by all storage engines at the service layer of the mysql database. It is used to record write operations performed by the database, that is, during the transaction commit phase. Bin log is stored in the disk in binary format.

Bin logs are logical logs and are executed by the service layer of the mysql database, which means that all storage engine databases record bin logs.

The binlog file is appended. You can set the size of the binlog file by using the max_binlog_size parameter. When the file size reaches a certain value, a new file is generated to save logs.

Bin log Disk flushing mechanism

For the InnoDB engine, the bin log is only recorded at each transaction COMMIT. At this point, the record is still in memory, so when is it saved to disk? The sync_binlog parameter is used by mysql to control the binlog flush time. The value ranges from 0 to N: 0: the system determines when to write data to disks. 1: Write bin log to disk every time the transaction commit. N: Bin logs are written to disks only after N transactions commit.

You are advised to set the sync_binlog parameter to 1. In this way, the binlog will be written to the disk every time the transaction is committed. In this way, the binlog will not be lost after the mysql restarts unexpectedly.

Bin log usage scenarios

In actual scenarios, bin log has two main scenarios, one is master slave replication, the other is data recovery master slave replication: Enable bin Log on the master server and send bin Log to slaver servers. Slaver servers read bin log logs to ensure data consistency between the master and slave databases. Data recovery: Use bin log to obtain data within the specified time range

Log module :undolog

Basic concepts of undo log

Undo log is a rollback log, which records all versions of each piece of data. For example, the UPDATE statement first records the data of this record to the Undo log, and sets the roll_pointer pointer of the latest version to the previous version, so that all versions of the current record can be formed. This is also the implementation mechanism of MVCC.

MVCC implementation mechanisms, check my another article < < the school recruit mysql those things | MVCC principle mechanism > >. The school recruit mysql those things | MVCC principle mechanism

Usage scenarios of undo log

• MVCC multiversion controlundo log

Binlog /redo log/undo log

Small talk

• After reading the article, I am not and mysql log module cp rate increased again
• I’m Maimo, welcome to talk to me

Original is not easy, feel that the article is written well small partners, a praise 👍 to encourage it ~

Welcome to join my official account [Maimocoding] pk big factory together

Welcome to Maimocoding