Summary of MySQL log related knowledge

The file used to store data in the database is called a data file, and the log file is called a log file. In addition, if each read and write directly accesses the disk, the performance will be very poor, so the database has a cache, the data cache is the data buffer, and the log cache is the 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 executor obtains the row record with id=2 through the storage engine. If the data page where the row record with id=2 is located is already in memory, it will be returned directly; otherwise, the data needs to be read from disk.
• The executor gets the returned row data, adds 1 to the value of field c to get the new row data, and then calls the storage engine interface to write the row data.
• The engine updates this row of data to the 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 executor generates a bin log for this operation and writes the bin log to disk.
• The executor calls the engine's commit transaction interface, and the engine changes the redo log just written to the commit state, and the update is completed
  

Supplement: The basic storage structure of MySQL is pages (records are stored in pages), 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

Bin log is called archive log or 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 storing each changed SQL statement, such as insert, delete, update (of course, not only SQL, but also transaction ID, execution time, etc.).

When is it produced

When a transaction is committed, all SQL statements in the transaction are recorded in a certain format to the bin log at one time.

What's the use

There are two main functions: master-slave replication and data recovery

• Currently, most database architectures have one master and multiple slaves. The slave server accesses the bin log of the master server to ensure data consistency.
• The bin log records changes to the database and can be used to restore data
  

When will the disk be dropped?

Distinguishing between innodb_flush_log_at_trx_commit and sync_binlog

​ Binary log depends on the sync_binlog parameter

• 0: After the transaction is committed, the operating system decides when to flush the cache to disk (best performance, worst security)
• 1: Each time a transaction is submitted, fsync is called to write the cache to disk (the best security, the worst performance)
• n: After committing n transactions, call fsync once to write the cache to disk
  

File Logging 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 row of data and fully ensure the consistency of master-slave data. Disadvantages: A large number of logs will be generated during batch operations, such as alter table.

• statement: records each SQL statement that modifies data, which can be considered as SQL statement replication
  

Advantages: small amount of log data, reduced disk IO, improved storage and recovery speed Disadvantages: in some cases, master-slave inconsistency may occur, such as when the SQL statement contains functions such as **now()**

• Mixed: A mixture of the above two modes. MySQL will select the write mode based on the SQL statement. Generally, statement mode is used to save bin logs. For operations that cannot be replicated in statement mode, row mode is used to save bin logs.
  

redo log

What is

The redo log is called the redo log. It is a log at the InnoDB storage engine layer. It records the modification information of the physical page, rather than how a certain row or several rows are modified.

When is it produced

When a transaction starts, the redo log is written. The redo log is not written to disk when the transaction is committed, but is written to disk during the transaction execution.

What's the use

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

When will the disk be dropped?

InnoDB first writes the log to the log buffer, then flushes the log from the log buffer to the OS buffer, and finally calls the file system's fsync function to flush the log to the disk. The timing of redo log writing is determined by the parameter innodb_flush_log_at_trx_commit

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

The general value is 2, because even if MySQL crashes, the data is not lost. Only when the entire server crashes will 1 second of data be lost.

bin log VS redo log

After reading the above introduction, I feel that bin log and redo log are very similar. Both record data changes and can be used for recovery. In fact, there are still obvious differences between them.

• The bin log belongs to the MySQL Server level, and the redo log belongs to the InnoDB storage engine level
• The bin log is a logical log that records the original logic of the SQL statement; the redo log is a physical log that records the content of the physical page update.
• Bin log is written in an appended manner. When the file reaches the limit, it will be replaced by the next file and will not be overwritten. Redo log is written in a cyclic manner. The file size is fixed. When it is full, it will be written from the beginning again, overwriting the original content.
• The function of bin log is to replicate and recover data between master and slave. When the database is deleted or the slave synchronizes data with the master, the bin log can be used to recover the data because it records the SQL statements that changed the data. The role of redo log is persistence. When the server crashes or loses power, data is lost and can be restored through redo log.
• The bin log is written to disk only when the transaction is committed, while the redo log is written to disk when the transaction is started.

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

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

undo log

Detailed analysis of MySQL transaction logs

What is

Undo log is called rollback log, which 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 that when a piece of data is inserted, the undo log will record a corresponding delete log, and vice versa.

When is it produced

Before the transaction starts, generate an undo log for the current version

What's the use

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

• Rollback: When a rollback is required, the corresponding content is read from the logical record of the undo log to roll back.
• MVCC: The undo log records store old versions of data. When a transaction needs to read data, it will follow the undo chain to find records that meet its visibility.

The above is the detailed content of the summary of MySQL log related knowledge. For more information about MySQL log, please pay attention to other related articles on 123WORDPRESS.COM!