This is the fourth day of my participation in Gwen Challenge

One, foreword

Related concepts:

  • redo log
  • binlog

Redo logs are important, physical logs of what records were made to which data pages, and what changes were made to them. The redo log itself is unique to the InnoDB storage engine.

A binlog is an archived log. It is a logical log, such as “what is the value of the update on the row where user =10?”

Binlog is not a log file specific to InnoDB storage engine, but a log file belonging to MySQL Server.

When a transaction is committed, it also writesbinlog:

The redo log is written to the disk file when the transaction is committed, and the binlog is written to the disk file when the transaction is committed.

As shown in figure:

InnoDB interacts with InnoDB by loading data from disk into Buffer Pool, writing undo logs, updating data in Buffer Pool, writing redo log Buffer, and writing redo log to disk. Write binlog and so on.

Steps 5 and 6 are when the commit transaction begins and belong to the commit transaction phase.





Second,binlogAnalyzing the flush policy of logs

Binlog, there are also different flush policies, there is a **sync_binlog** parameter to control the binlog flush policy, the default value is 0.

Sync_binlog parameter value:

  • 0, at this timebinlogWhen writing to disk, it does not go directly to the disk fileos cacheMemory cache.
  • 1, this will force you to commit the transaction with thebinlogWrite directly to the disk file.

Commit transactions based on binlog and redo log:

When the log file is written to disk, the final transaction commit is performed, and the file name and location of the update are written to the redo log file. Also write a commit flag in the redo log file.

As shown in figure:

Transaction submission completion criteria: Step 5, step 6 and step 7 are all completed.

At this point, there will be the following situation:

  1. redoAfter writing to the disk file,MySQLdown

Because there is no final commit mark in the redo log, the transaction is considered unsuccessful.

The redo log file does not have a log for this update, but the binlog file does not have a log for this update, so data inconsistency does not occur.

  1. redoAfter writing to the disk file,binlogLogs are written to disk,MySQLdown

Because there is no final commit flag in the redo log, the transaction commit fails.





3. Update data

Background I/O threads randomly flush dirty data back to the disk after memory update

MySQL has a background IO thread that at some later time randomly flusher the modified dirty data from the buffer pool back to the data file on disk.

As shown in figure:

Now, what happens when you crash?

It doesn’t matter if MySQL crashes before the IO thread flusher dirty data back to disk, because after the restart, transactions committed before the redo log are updated to memory.

Question: Why does modifying data on disk passIODoes the thread execute irregularly?

Asynchronous operation to speed up operation execution.