References:

(1) MySQL log system

(2) Understand several kinds of MySQL logs

(3) Log interview questions

The files used to store data in a database are called data files, and log files are called log files. In addition, if every read and write is directly accessing disk, performance is poor, so the database has a cache, data cache is data buffer, log cache log buffer.

SQL Execution sequence

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

  • The executor gets the row record with ID =2 from the storage engine. If the data page of the row with id=2 is already in memory, it is returned directly; Otherwise, data needs to be read from disk
  • The executor takes the returned row data, increments the value of field C by 1 to get the new row data, and then calls the storage engine interface to write the row data
  • The engine updates the row to memory and logs the update toredo logInside, the redo log is inPrepare status. The executor is then told that the execution is complete and the transaction can be committed at any time
  • The executor that generates the operationbin logAnd write the bin log to disk
  • The executor calls the commit transaction interface of the engine, and the engine changes the redo log to commit

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

bin log

What is the

The bin log is used to record the change of the database table structure and table data. It can be simply interpreted as the SQL statement that stores each change, such as INSERT, DELETE, update (of course, not only SQL, but also transaction ID). Execution time, etc.).

When does it happen

When a transaction is submitted, the SQL statements in the transaction are recorded in a bin log in a certain format

What’s the use of

It has two main functions: primary/secondary replication and data recovery

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

When will it fall

Distinguish innodb_flush_LOG_at_trx_COMMIT from SYNc_binlog

Binary logs depend on the sync_binlog parameter

  • 0: After the transaction commits, the operating system decides when to flush the cache to disk (best performance, worst security)
  • 1: Call fsync every time a transaction commits to write the cache to disk (best security, worst performance)
  • N: After n transactions have been committed, fsync is called once to write the cache to disk

File record 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: Records the details of data modification in each row, and ensures data consistency between master and slave
    • Disadvantages: A large number of logs are generated during batch operations, such as ALTER table
  • Statement: Records each MODIFIED SQL statement. The statement is considered to be a copy of the SQL statement
    • Advantages: Small amount of log data reduces disk I/O and improves storage and recovery speed
    • Disadvantages: Master/slave inconsistencies can occur in some cases, such as SQL statements containing functions such as **now()**
  • Mixed: a mix of the above two modes. MySQL will select a write mode based on SQL statements. In general, statement mode is used to save bin logs.

redo log

What is the

Redo logs are InnoDB storage engine logs. Redo logs record changes to physical pages, not how one or more rows were changed

When does it happen

When a transaction starts, a redo log is written. Redo logs are not written to disk as the transaction commits. They are written to disk as the transaction executes

What’s the use of

It can be used to recover data. Redo logs are sequential I/OS that are written to disk after a transaction starts. In the event of a sudden power failure, InnoDB uses the redo log to restore the database to the time before the power failure to ensure data integrity

When will it fall

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

  • 0: Writes the log buffer to the OS buffer once every second and calls fsync to flush it to disk
  • 1: Write the log buffer to the OS buffer and call fsync to flush it to disk every time the transaction is committed
  • 2: Each time a transaction is committed, it is simply written to the OS buffer and then fsync is called once per second to flush the log to disk

Generally, the value is 2, because data is not lost even when MySQL is down. Only one second of data is lost if the entire server goes down

bin log VS redo log

The bin log is similar to the redo log. It records data changes and can be used for recovery. In fact, there are obvious differences.

  • The bin log belongs to the MySQL Server layer, and the redo log belongs to the InnoDB storage engine layer
  • Bin logs are logical logs that record the original logic of SQL statements. The redo log is a physical log that records updates to physical pages
  • Bin log appending data. When a file reaches the upper limit, the next file will be replaced. Redo log: Redo log is a redo log that rewrites data to a fixed size
  • Bin log is used to replicate and restore data from the primary database to the secondary database. When a database is deleted or data is synchronized from the secondary database to the primary database, the bin log records THE SQL of the changed data. Therefore, the bin log can be used to restore data. The redo log is used for persistent data restoration in case of server downtime or power failure.
  • The bin log is written to disk when a transaction is committed, while the redo log is written to disk when a transaction is started

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

No way! Because the redo log focuses on storing data changes from a transaction, the redo log data is no longer useful when the data in memory is flushed to disk. In addition, the redo log overwrites historical data and it is not possible to recover all data.

undo log

Analyze the MySQL transaction log in detail

What is the

Undo log is called rollback log, belongs to InnoDB storage engine layer, is a logical log, records each row of data. When we change data, an Undo log is generated. It can be said that when we insert data, the Undo log records a corresponding DELETE log, and vice versa.

When does it happen

Before the transaction starts, the current version generates an undo log

What’s the use of

Main benefits: Provides rollback and multi-version concurrency control (MVCC)

  • Rollback: when rollback is required, read the corresponding contents from the undo log logical records to rollback
  • MVCC: 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