MySQL’s RedoLog and BinLog. Details of MySQL BinLog | by Dwen | Jun, 2022

Particulars of MySQL BinLog

Picture by Workperch on Unsplash

We all know that when MySQL writes information, it could be appending information or finding an already present piece of information to change.

For random reads and writes from disk, that is sluggish and can’t meet the state of affairs of excessive IO operations.

To enhance write effectivity, we will write to reminiscence first after which write to disk when free.

However this creates an issue: the info in reminiscence isn’t persistent, so if there’s a energy failure, the info shall be misplaced?

To resolve the info loss drawback, MySQL has launched redo logs to unravel this drawback.

That is known as WAL (Write-Forward Logging), a standard observe to enhance IO effectivity in opposition to non-in-memory databases.

This makes it doable to depend on logging to get well information within the occasion of a crash, making certain information persistence.

An environment friendly logging algorithm in databases. For non-memory databases, disk I/O operations are a significant bottleneck in database effectivity.

With the identical quantity of information, a database system utilizing WAL logging has solely about half the disk write operations of conventional rollback logging at transaction commit time, vastly bettering the effectivity of database disk I/O operations, thus bettering database efficiency.

Benefits of WAL

  • Reads and writes could be executed utterly concurrently with out blocking one another (however writes are nonetheless not concurrent with one another).
  • WAL has higher efficiency typically (as a result of there isn’t a want to put in writing two recordsdata for every write).
  • Disk I/O conduct is extra predictable.
  • Fewer fsync() operations are used, lowering system fragility points.

RedoLog implementation

Let’s see how MySQL actually does it.

When a report must be up to date, the InnoDB engine writes the report redo log first and updates the reminiscence. On the acceptable time, corresponding to when the disk is free, the info within the redo log is flushed to the disk.

Within the InnoDB engine redo log is fastened in dimension, for instance, it may be configured as a set of 4 recordsdata, every of 1GB in dimension, so a complete of 4GB of operations could be logged.

write pos is the present location of the report, shifting as it’s written, checkpoint marks the present location to be erased, and the report is up to date to the info file earlier than it’s erased.

The checkpoint marks the present place to be erased, and the report is up to date to the info file earlier than it’s erased, once more shifting in a single course whereas erasing.

If write pos finishes writing the final file, it should transfer to ib-log-file-0 and begin writing once more.

If the write pos catches up with the examine level, the redo log is full, so you’ll be able to’t carry out a brand new replace, you must cease and erase some information and synchronize the info to disk earlier than shifting the examine level ahead.

How one can view redo log parameters?

Every InnoDB storage engine has at the least one redo log file group and every file group has at the least two redo log recordsdata, the default being ib-log-file-0 and ib-log-file-1.

present variables like '%innodb_log%';


The redo log is particular to the InnoDB engine and retains information protected, however how do different engines log information?

On the Server stage, MySQL has its personal log, that’s bin-log (an archived log). You should take a look at MySQL in isolation. MySQL = Server + totally different information storage engines, not as an entire.

Binlog information a binary log of all modifications to the MySQL database desk construction and desk information, however not the choose and present queries. bin-loglogs are logged as occasions and embrace the time consumed by the statements.

The 2 most necessary situations for turning on Binlog logging are as follows:

  • Dad or mum-Baby replication: allow the Binlog operate within the major library, in order that the first library can move Binlog to the secondary library, and the secondary library can get Binlog and obtain information restoration to attain primary-secondary information consistency.
  • Knowledge restoration: get well information by way of instruments corresponding to mysqlbinlog.

Bin-log recordsdata are logged in three modes: assertion, rowand blended, with row mode often getting used.

Why are there two logs?

MySQL didn’t have an InnoDB engine at first, the engine that got here with MySQL was MyISAM, however MyISAM didn’t have the flexibility to deal with crash restoration information and the bin-loglogs might solely be used for archiving.

InnoDB was added as a plugin later, so it carried out its personal logging system to safe information and address crash restoration.

The distinction between bin log and redo log.

Right here, I’ve summarised 5 details:

  • The content material is totally different: redo log is a bodily log and the content material is predicated on the Web page on disk, bin-log content material is binary and relying on the binlog_format parameter, could also be based mostly on SQL statements, on the info itself, or a mix of the 2.
  • Totally different ranges: redo log works with InnoDB and the engine, bin-log is situated on the MySQL Server stage and is obtainable to all engines.
  • Totally different types of disk storage: redo log writes cyclically, bin-log accumulates, so it may be used for information restoration or primary-secondary synchronization
  • The timing of writing is totally different: bin-logare written when a transaction often commits or when N transactions commit as soon as, redo log are written at quite a lot of instances, both each time a transaction commits, by one other threaded transaction, or each second when the disk is flushed. (Observe: uncommitted transactions in redo log might also be flushed to disk)
  • Totally different roles: redo log is used for crash restoration to make sure that MySQL downtime doesn’t have an effect on persistence; bin-log is used for point-in-time restoration to make sure that the server can get well information based mostly on the time limit, as well as bin-log can also be used for primary-secondary replication.

Two-phase Commit

As a result of redo-logis within the InnoDB tier and bin-logis within the Server tier, this introduces a brand new drawback.

If the redo log is written efficiently and the bin-log crashes earlier than it’s written to disk, the transaction has not but been dedicated, so the brand new information written to the redo-log is invalid.

Restarting the database for information restoration restores the info within the redo-log to disk, which creates invalid information.

On this case, as you properly know, a two-phase commit is launched.

Within the first stage, the redo-log is written and within the ready state. After the Server layer saves the bin-log information and drops it to disk, the transaction commits the redo-log on the identical time, in order that the redo-log turns into dedicated, which ensures the consistency of the redo-log information and the bin-log information.

With the earlier data, now you can discover how the replace assertion is executed in MySQL.

Suppose we now execute the SQL : replace table_test set a = a+1 the place id = 2;

  • First, the shopper connects by way of the connector and determines the permissions.
  • After verification, the SQL goes by way of the parser for lexical and syntax evaluation (AST) and whether it is an Replace assertion, MySQL will clear all of the question cache for the question desk table_test. (As you’ll be able to see, it isn’t really useful to activate the question cache)
  • The optimizer optimizes the validated SQL, plans to match the id index, and generates an execution plan.
  • The executor will get the ultimate SQL and calls the interface of the corresponding storage engine to begin executing the replace SQL.
  • The InnoDB engine opens a transaction, the execution engine first queries from reminiscence whether or not there’s information with id=2, if it matches then the corresponding information with subject+1, after which saves it to reminiscence. If it doesn’t question the info with id=2 then it should go to the disk, the question will learn the info into reminiscence in pages, then replace it and reserve it to reminiscence.
  • The InnoDB engine will then save the info rows to redo-log, which is pre-committed, notifying the Server’s executor that it is able to commit the transaction.
  • The executor will generate the corresponding bin-log and write it to disk.
  • The transaction is dedicated and the redo-log is then dedicated.
  • That is the place the execution of a transaction is full.

More Posts