This is the 7th day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021.
Logs are an important part of the mysql database and record various status information during the running of the database. Mysql logs are classified into error logs, query logs, slow query logs, transaction logs, and binary logs. For development purposes, we focus on binary logs and transaction logs (including redo logs and undo logs), which are described in detail in the rest of this article.
1. Log definition
Binlog The binary log is used on the server layer regardless of the engine used by MySQL.
Redo log is an InnoDB storage engine layer used to ensure transaction security.
Undo log rollback logs hold a version of the data prior to the transaction, can be used for rollback, and can also provide multi-version concurrent control read (MVCC), that is, unlocked read. Undolog is used to ensure transaction consistency. When a transaction is rolled back, the corresponding Undolog is queried and rolled back.
Undolog can be divided into two types, one is insert undolog, which is the undolog corresponding to the insert statement. It will not be used as MVCC, so the log will be released directly after the commit. There is also an update undolog which is used for MVCC, so the undolog is linked to a list, until the undolog was created before the undolog was created, at which point the undolog can be deleted.Copy the code| The log | Physical log | The logical logs | define | role |
|---|---|---|---|---|
| redo log | InnoDB stores logs for the engine layer | N | Redo log | Atomicity and persistence of transactions |
| undo log | N | Y | Roll back log | Transaction consistency, transaction rollback, and implementation MVCC |
| binlog | N | Logs recorded at the MySQL Server layer | Archive logs | Synchronizing primary and secondary data and restoring data |
- Logical logging: It simply means that SQL statements are recorded.
- Physical log:
mysqlData is ultimately stored in the data page, and physical logs record changes to the data page
2. Log function
Redo log Log module
A redo log consists of a redo log buffer in memory and a redo log file on disk. Each DML statement executed by mysql is written to the redo log buffer, and at a later point in time, multiple operations are written to the redo log file. This technique of Logging first and then disk writing is often referred to as WRITE-Ahead Logging (WAL) in MySQL.
Redo log is an InnoDB storage engine layer log. It is also a log file that records transaction changes. The redo log is recorded regardless of whether the transaction is committed or not. Redo log files are useful in the event of media failures, such as a database power failure. InnoDB storage engine uses redo log to restore data to the time before the power failure to ensure data integrity.
When an update statement is executed, InnoDB writes the update to the redo log and then updates the memory. InnoDB then updates the contents of the redo log to disk at idle time or according to the specified update policy. WAL, or Write Ahead Logging, is involved here. The key is to Write to the log first and then to the disk.
The redo log enables crash-safe recovery in the event of an abnormal database restart.
The size of the redo log is fixed. When the redo log is full, it is written over again.
Binlog Log module
- A master-slave replicationIn:
MasterThe openbinlogAnd thenbinlogSend to eachSlaveEnd,SlaveAlso be putbinlogTo achieve master/slave data consistency. - Data recovery: by use
mysqlbinlogTools to recover data.
The binary log is a logical log that records the original logic of the statement in binary format. The binary log does not have the crash-safe capability.
The binlog can be used for master/slave replication. The secondary library uses the binlog on the master library for replay to achieve master/slave synchronization. Point-in-time, location-based restore operations for databases. There are three modes of binlog: Statement, Row, and Mixed.
Before MySQL 5.7.7, the default format was STATEMENT. After MySQL 5.7.7, the default value was ROW. The log format is specified in binlog-format.
Undo log Log module
One of the four characteristics of database transactions is atomicity. Specifically, atomicity refers to a series of operations on the database, either all successful or all failed, and it is impossible to have partial success. In fact, the underlying atomicity is implemented through undo log. The undo log records the logical changes of data. For example, an INSERT statement corresponds to a DELETE undo log, and each UPDATE statement corresponds to a reverse UPDATE Undo log. In this way, when an error occurs, You can roll back to the data state before the transaction.
In addition, a version of the data before the transaction occurs is saved.
- Can be used for rollback;
- Multiple versions of concurrent read control (MVCC) can be provided at the same time, that is, non-locked read;
- Before a transaction starts, the current transaction version generates undo log. Undo log also generates redo log to ensure the reliability of undo log.
- After the transaction is committed, the undo log cannot be deleted immediately. Instead, it is put into the linked list to be cleaned.
- The Purge thread determines whether the Undo log space can be cleared by determining whether there are other transactions using version information prior to the last transaction in the undo segment table.