• This article is a note type article, only the conclusion is not proved, can be used to review the consolidation of knowledge, can not be used for new knowledge learning. Thank you for correcting any mistakes you have made.
  • Juejin (juejin) – juejin (juejin)
  • The article is still unfinished and will be improved gradually

Series of articles:

  • 【MySQL】 logical architecture (juejin. Cn)
  • MySQL > select * from juejin.cn;
  • 【MySQL】 concurrency, transactions and locks
  • MySQL > juejin.cn

binlog

The Binary Log is a Log of the MySQL service layer that records write operations. The binlog is written only after the transaction is committed. If the transaction is committed but the data does not fall down, the transaction can be redone after the restart using mysqlBinlog and binlog. Since logs are at the MySQL service layer, they are supported by all storage engines.

Since the MySQL service layer is not responsible for storage, the drop operation is controlled by the storage engine, so the service layer must ensure the persistence of DML execution through binlog.

configuration

  • Enable and disable:

    You can enable binlog by setting log-bin=[log_file_name] in the configuration file or running SET SQL_LOG_BIN=1. To disable binlog, delete the log-bin configuration item or run SET SQL_LOG_BIN=0.

  • Write time: The write time of the binlog is controlled by sync_binlog.

    • sync_binlog=0MySQL does not control the flushing of binlogs. The operating system flushes binlogs to disk at idle times.
    • sync_binlog=N, N is a positive integer, indicating that every N transactions, MySQL manually flusher binlog to disk.

So sync_binlog=1 gives the highest consistency, and we usually choose to set it to 1.

create

A new binlog file is created in the following cases:

  1. MySQL restart.
  2. useflush logCommand.
  3. File size exceedsmax_binlog_size.max_binlog_sizeBy default, the maximum value is 1G and the minimum value is 4K. However, when the size of a single DML exceeds 1G, the single operation is not split into multiple file storage, so the binlog size is larger than 1 gbmax_binlog_sizeIn the case.

Written to the event

Write events for binlog include:

  • STATEMENT: Saves data based on SQL statements.
  • ROW: Saves on a ROW basis.
  • MIX: Mixed mode. Common statements are saved in STATEMENT format. For the statement that cannot complete the master-slave replication operation, if anydate()Function, is saved in ROW format.

redo log

Redo log is a log of InnoDB storage engine. Since InnoDB supports transactions, redo log is used to ensure the consistency of transaction execution. The redo log only records which data pages are changed by transactions. The redo log is sequential, so it is fast and does not have a significant performance impact. When a transaction is committed, InnoDB will automatically redo the lost transaction according to the redo log if the database is down before the data is dropped.

Why not just drop the data when the transaction commits? InnoDB’s default page size is 16K, so if a change involves multiple pages, multiple random I/OS are required. After modification, it is necessary to brush into the disk and perform random I/O for many times, which is very slow. Therefore, writes to transactions typically cache changes in memory until they are free to write.

InnoDB’s WAL (Write Ahead Log) technology is the redo Log. The redo Log is always written first to ensure consistency.

log buffer

In addition to being a file entity on disk, the redo log contains a log buffer in memory. Therefore, a buffer is used to record the write operations in memory. After multiple write operations occur, the data is written to the disk again. You can greatly improve redo log performance by optimizing random writes to sequential writes. Innodb_flush_log_at_trx_commit There are three drop strategies for redo log buffers, controlled by innodb_flush_log_at_trx_commit:

  • Zero:Write by second, brush by second. A second callwrite()Write OS Buffer and callflush()Flush to disk.
  • 1:Real time write, real time brush. Called every time a transaction commitswrite()Write OS Buffer and callflush()Flush to disk.
  • 2:Real-time write, delay brush. Called every time a transaction commitswrite()Writes OS Buffer, but is called every secondflush()Flush to disk.

Therefore, the system runs fastest when the write delay policy is adopted, but data will be lost for 1s if an accident occurs. Select this parameter based on the application scenario.

The concept of write and flush derives from the existence of OS buffers, which are write buffers provided by the operating system to coordinate the speed of memory and disk. So our write() alone does not guarantee that the data will drop immediately, so the OS provides a flush() system call to trigger the flush buffer immediately.

Loop written

The current write position of the redo log has a write_pos pointer. Data logs that are stored in the current disk have a check_point pointer at the end of the log. Logs that are stored before the safe point can be deleted, and data that is stored after the safe point cannot be deleted. When the write_pos/check_point log encounters, the redo log space is insufficient. In this case, some data must be removed immediately, and the redo log must be moved to check_point later.

undolog

Comparison of the three