Mysql has two main types of logs: Redo Log and BinLog.

BinLog

  1. Binlog Records write operations (excluding queries) performed by the database and saves them in binary format on the disk. Implemented by MySQL’s Server layer and available to all engines

  2. The BinLog is the logical log that records the original logic of the statement, such as “add 1 to the C field on the line ID=2”.

  3. Binlog can be appended. “Appending” means that the binlog file will be switched to the next one after being written to a certain size without overwriting the previous log.

  4. Application scenario: Primary/secondary replication and data recovery

  5. BinLog has three modes :Row Level,Statement Level (default), and Mixed automatic.

  • The Row Level mode records the modified form of each Row and modifies the same data on the slave side. This mode has the most complete logging, but the volume of logging is large.

  • Statement Level records the SQL of each modified data. When the slave replicates the data, the SQL process parses it into the same SQL that was executed by the master and executes it again. This mode can reduce the amount of bin-log, but can cause problems when certain functions or functions are used to modify data. For example, the sleep() function cannot be copied directly in some versions, the use of last_insert_id() in stored procedures may result in inconsistent ids on the slave and master, and so on. Since RowLevel logs changes on a per-row basis, similar problems do not occur

  • In Mixed mode, MySQL will select a different log format for each SQL Statement executed, that is, between Statement and Row. If the SQL statement is indeed an UPDATE or DELETE statement that modifies data, all row changes will still be recorded.

  1. Viewing the Log Mode
Query the log mode select @@binlog_formatCopy the code

Redo Log

  1. InnoDB engine is unique
  2. A physical log of what was changed on a data page
  3. Redo logs are written in a redo log; space is always running out
  4. The Innodb engine uses write-Ahead Logging (WAL) technology to improve efficiency. The key technology is to Write logs before disks. When a record needs to be updated, the InnoDB engine writes the record to the redo log and updates the memory. At the same time, the InnoDB engine will update the operation record to disk when appropriate.
  5. Crash-safe: With redo log, InnoDB guarantees that all previously committed records will not be lost even if the database restarts unexpectedly. This capability is called crash-safe.
  6. Binlog does not have crash recovery because binlog does not have a check point to determine where to start replaying logs.

Redo Log Log structure

The redo log is fixed in size. For example, you can configure a set of 4 1GB files to log 4GB operations. Start at the beginning, then go back to the beginning at the end, as shown below.The following figure shows a group of four redo log files. Checkpoint is the current location of the redo log file. Data is removed from disk before being erased (update memory pages and wait for dirty pages). The part between write pos and checkpoint can be used to record new operations. If write pos and checkpoint meet, redolog is full. At this time, the database stops executing database update statements. Redo log synchronization to disk instead. The portion between checkpoint and write POS is waiting to fall (memory pages are updated first, then dirty pages are flushed).

Log recording and crash recovery

When updating a row of data, the log-related operations are as follows

  1. Here Redolog does two commits, designed to ensure that the state of the database is consistent with the state of the library recovered from its logs.

  2. Assuming that a two-paragraph submission is not used, the following situation occurs:

  • Write redo log and then binlog. MySQL restarts unexpectedly when the redo log is complete. Because the system can recover data from a redo log crash, the value of c is 1. But because the binlog didn’t finish writing the crash, the binlog didn’t record the statement. Therefore, when the log is backed up later, the saved binlog does not contain this statement. Then you will notice that if you need to restore the temporary library with this binlog, the temporary library will miss the update because the binlog of this statement is lost, and the restored line of C will have a value of 0, which is different from the original library.
  • Write binlog and redo log. If there is a crash after the binlog has written, the transaction is invalid after the crash because the redo log has not been written, so the value of c in this row is 0. But binlog already records “change c from 0 to 1”. The value of c in the restored row is 1, which is different from that in the original library.
  1. When the system crashes, the recovery process is as follows: Redo log and binlog share a common data field called XID. During crash recovery, redo log is scanned in sequence:
  • If you encounter a prepare and COMMIT redo log, commit it.
  • If you encounter a redo log that has only Parepare but no COMMIT, take the XID to the binlog to find the corresponding transaction.