How does binlog ensure data consistency?
I talked about redo logs in detail in the previous article, but there is also a binary log called a binlog in MySQL.Redo log is a physical log that records what was changed on a data page and belongs to the InnoDB storage engine.The binlog is a logical log that records the original logic of the statement. It is similar to “add 1 to the C field in the row ID=2” and belongs to the MySQL Server layer.Binlog Regardless of the storage engine, a binlog is generated whenever a table data update occurs.What exactly is the binlog for?It can be said that the data backup, master/slave, master/master, and master/slave of MySQL database are all dependent on binlog. Binlog is required to synchronize data and ensure data consistency.Binlog records all logical operations that involve updating data, and writes them sequentially.Record format Binlog There are three log formats, which can be specified by using the binlog_format parameter.Statementrowmixed specifies an SQL statement, such as an update T set update_time=now() where id=1.Update_time =now(); update_time=now(); update_time=now();To solve this problem, we need to specify row to record not just a simple SQL statement, but also the specific data of the operation, as shown below.The contents of the row format are not detailed and are parsed by the mysqlbinlog tool.Update_time =now() =1627112756247 (@1, @2, @3); update_time=1627112756247 (@2, @3);This ensures consistency of synchronized data, which is typically specified as ROW, and improves reliability for database recovery and synchronization.However, this format requires a larger capacity for recording and occupies more space. Therefore, recovery and synchronization consume MORE I/O resources, affecting the execution speed.So there is a compromise, named mixed, where the content is recorded as a mixture of the first two.MySQL determines whether the SQL statement is likely to cause data inconsistencies. If so, use the row format, otherwise use the statement format.Write mechanism The write time of binlog is also very simple. During transaction execution, logs are written to the binlog cache first, and when a transaction is committed, logs are written to the binlog cache.Since the binlog of a transaction cannot be broken apart, no matter how big the transaction is, it must be written at once, so the system allocates a block of memory for each thread as the binlog cache.We can control the size of a single thread’s binlog cache using the binlog_cache_size parameter. If the cache exceeds this parameter, it is temporarily stored to a disk (Swap).Write in the figure below means that logs are written to the page cache of the file system, but data is not persisted to disks. Therefore, the data is persisted to disks faster. Fsync in the figure above is the timing of data persistence.This can be controlled by the sync_binlog parameter, which defaults to 0.If the value is 0, only write is committed for each transaction. The system determines when to execute fsync.Although the performance is improved, the binglog in the page cache is lost when the machine is down.For security, you can set it to 1 to indicate that fsync will be performed every time a transaction is committed, just like the binlog flush process.Finally, there is a compromise that can be set to N(N>1), which means that each committed transaction is written, but N transactions are accumulated before fsync.In IO bottleneck scenarios, setting sync_binlog to a large value can improve performance.Similarly, if the machine goes down, the binlog of the last N transactions will be lost.Over the years, have you learned about the architecture design of MySQL?What is redo log?MySQL Innodb storage engine MySQL Innodb storage engine MySQL Innodb storage engine MySQL Innodb storage engine MySQL Innodb storage engine MySQL Innodb storage engine MySQL Innodb storage engine