This is the 8th day of my participation in the August Text Challenge.More challenges in August

Introduction: It is well known that master/slave replication in mysql relies on logs. Mysql has many logs, for example: redo log undo log bin log error log slow query log relay log Mysql bin log undo log redo log

bin log

Mysql master/slave replication leverages the bin log feature, which is also used for mysql data recovery

Bin log flush time: mysql controls the binlog flush time using the sync_binlog parameter. The value ranges from 0 to N, where:

  • 0: the system decides when to write data without mandatory requirements.
  • 1: Bin log will be written to disk every time a transaction is committed.
  • N: The bin log is written to the disk every N transactions. In practice, set the number of N, sacrificing some consistency for performance.

This parameter is set to 1 by default after mysql 5.7.7.

  • Binlog: belongs to the mysql service. The binlog mainly records database write operations (INSERT, UPDATE, or DELETE), but does not record query operations. The binlog is a logical log of the mysql database.
  • Bin log You can specify max_bin_size to set the size of a bin log file. If the size exceeds the threshold, a new file will be generated to save logs.
  • The expiration time of bin log can be specified by expire_logs_days.
  • Bin log has three formats (STATMENT, ROW, MIXED). You can specify the format by specifying binlog_format.

undo log

  • Undo the log: Atomicity in the database is implemented based on the Undo log, which mainly records the logical changes of data. For example, an INSERT statement corresponds to an DELETE Undo log, and each update statement corresponds to an opposite Update Undo log. In addition, undo log is key to the IMPLEMENTATION of MVCC (Multi-version Concurrency Control). It belongs to the InnoDB storage engine

redo log

Persistence in mysql InnoDB is implemented through redo log. The smallest unit of mysql interaction with disk is a page. Mysql can have performance problems if it flusher data pages directly to disk:

1. Flushing full pages to disk with minimal changes can result in a significant waste of resources.

2. The modified data may not be contiguous, that is, the data may be distributed on different pages, so using random I/O writes is also a waste of performance.

  • Redo log: At transaction commit time, only modified data pages are logged (because log files are relatively small and contiguous at the physical level). Innodb storage engine
  • The redo log is divided into two parts: Each transaction is recorded in the redo log buffer and the redo log file. Each transaction is flushed to the redo log file at a later point in time. This Logging first and then disk writing technique is **WAL (write-Ahead Logging) ** technology.

In the computer operating system, the Buffer data in user space cannot be written directly to the disk, and must pass through the OS Buffer in the middle. Therefore, redo log buffer writing to redo log file is actually written to the OS buffer first and then brushed to redo log file via the system call fsync ().

Differences between redo log and bin log

Bin log Logs are only used for archiving. Relying on bin log does not provide the crach-safe capability. However, redo logs alone do not work either, because redo logs are unique to innoDB and are overwritten when logged. Therefore, both bin log and redo log are required to ensure that data is not lost when the database is restarted after a sending failure.