Summary of MySQL log related knowledge

Time:2022-5-3

The file used to store data in the database is called data file, and the log file is called log file. In addition, if you directly access the disk every time you read or write, the performance is very poor, so the database has cache, data buffer and log buffer.

SQL execution order

When we execute an update statement, such as update table set C = C + 1 where id = 2, the execution order is as follows:

  • The actuator obtains the row record with id = 2 through the storage engine. If the data page of the row record with id = 2 is already in memory, it will be returned directly; Otherwise, you need to read data from disk
  • The actuator gets the returned row data, adds the value of field C +1 to get the new row data, and then calls the storage engine interface to write the row data
  • The engine updates this line of data to memory and records the update operation in the redo log. At this time, the redo log is in the prepare state. Then tell the executor that the execution is complete and the transaction can be committed at any time
  • The actuator generates the bin log of this operation and writes the bin log to disk
  • The executor calls the commit transaction interface of the engine. The engine changes the redo log just written to the commit state, and the update is completed

Add: the basic storage structure of MySQL is page (records are stored inside the page), so MySQL first finds the page where the record is located, then loads the page into memory, and then modifies the corresponding record.

bin log

What is it?

Bin log is called archive log and binary log. It belongs to MySQL server level and is used to record changes in database table structure and table data. It can be simply understood as SQL statements that store each change, such as insert, delete and update (of course, not only SQL, but also transaction ID, execution time, etc.).

When did it happen

When the transaction is committed, the SQL statements in the transaction are recorded in Bin log in a certain format at one time

What’s the usage?

It has two main functions: master-slave replication and data recovery

  • At present, most database architectures are one master and many slaves. The slave server accesses the bin log of the master server to ensure data consistency
  • Bin log records the changes of the database and can be used to recover the data

When will it fall

Distinguish InnoDB_ flush_ log_ at_ trx_ Commit and sync_ binlog

Binary log depends on sync_ Binlog parameter

  • 0: after the transaction is committed, the operating system decides when to refresh the cache to disk (the best performance and the worst security)
  • 1: Every time a transaction is committed, fsync is called to write the cache to disk (the best security and the worst performance)
  • n: After n transactions are committed, call fsync once to write the cache to disk

File recording mode

Bin log has three file recording modes: row, statement and mixed

  • Row (row based replication, PBR): records the modification of each row of data

Advantages: it can clearly record the modification details of each line of data and fully ensure the consistency of master-slave data
Disadvantages: a large number of logs will be generated during batch operation, such as alter table

  • Statement: records the SQL of each modified data, which can be considered as SQL statement replication

Advantages: small amount of log data, reduce disk IO, and improve storage and recovery speed
Disadvantages: in some cases, the master and slave are inconsistent. For example, the SQL statement contains * * now() * * and other functions

  • Mixed: a mixture of the above two modes. MySQL will select the write mode according to the SQL statement. Generally, the statement mode is used to save the bin log. For operations that cannot be copied in the statement mode, the row mode is used to save the bin log.

redo log

What is it?

Redo log is called redo log, which belongs to the InnoDB storage engine layer. It records the modification information of physical pages, rather than the modification of a line or lines

When did it happen

The redo log will be written when the transaction starts. Redo logs are not written to disk until the transaction is committed, but are already written to disk during transaction execution

What’s the usage?

Can be used to recover data. Redo log is written to the disk after the transaction starts. It is sequential IO, and the writing speed is fast. If the server suddenly loses power, InnoDB engine will use redo log to restore the database to the time before power failure to ensure data integrity

When will it fall

InnoDB writes the logs to the log buffer first, then brushes the logs from the log buffer to the OS buffer, and finally calls the fsync function of the file system to flush the logs to the disk. The redo log writing time is determined by the parameterinnodb_flush_log_at_trx_commitdecision

  • 0: once per second, write the log buffer to the OS buffer and call fsync to brush to the disk
  • 1: Each time a transaction is committed, write the log buffer to the OS buffer and call fsync to brush to the disk
  • 2: Each time a transaction is committed, it is only written to the OS buffer, and then fsync is called every second to refresh the log to disk

The general value is 2, because even if MySQL goes down, the data is not lost. Only when the whole server hangs up, one second of data is lost

bin log VS redo log

After reading the above introduction, I feel that bin log and redo log are very similar. They both record data changes and can be used for recovery. In fact, they are obviously different.

  • Bin log belongs to MySQL server level and redo log belongs to InnoDB storage engine level
  • Bin log is a logic log, which records the original logic of SQL statements; Redo log is a physical log, which records the updated content of the physical page
  • Bin log is an additional write. When the file reaches the limit, the next file will be replaced and will not be overwritten; Redo log is a circular write with a fixed file size. When it is full, it starts writing again, overwriting the original content
  • Bin log is used to copy and recover data from the master and slave. When the database is deleted or the master database data is synchronized from the database, bin log records the SQL of the changed data, so it can be recovered through bin log. The role of redo log is persistence. In case of server downtime or power failure, data loss can be recovered through redo log.
  • Bin log is written to disk only when the transaction is committed, while redo log starts writing to disk when the transaction is started

If the entire database is deleted, can it be restored through redo log?

no way! Because redo log focuses on saving the data changes of a transaction, when the data in memory is brushed to disk, the data in redo log actually has no reference value. In addition, redo log will overwrite historical data, and it is impossible to recover all data through it.

undo log

Detailed analysis of MySQL transaction log

What is it?

Undo log is called rollback log and belongs to the InnoDB storage engine layer. It is a logical log that records each line of data. When we change data, an undo log will be generated. It can be considered as inserting a piece of data, and the undo log will record a corresponding delete log, and vice versa.

When did it happen

Before the transaction starts, the undo log is generated from the current version

What’s the usage?

Main function: provide rollback and multi version concurrency control (mvcc)

  • Rollback: when rollback is required, read the corresponding content from the logical record of undo log to rollback
  • Mvcc: undo log records store old version data. When a transaction needs to read data, it will find records that meet its visibility along the undo chain

The above is the detailed summary of MySQL log related knowledge. For more information about MySQL log, please pay attention to other relevant articles of developeppaer!