preface

Hello, everyone. I am a little boy picking up field snails. MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL > MySQL

  • Public number: a boy picking up snails

1. What is redo log? Why do I need redo logs?

What is a redo log?

  • Redo logs are redo logs.
  • It records changes on the data page.
  • It refers to the data modified during the transaction that will be backed up and stored.
  • If the database server is down or dirty pages are not written to disk, you can use the redo log to restore the database.
  • It is unique to the Innodb storage engine

Why do I need redo logs?

  • The redo log is used to restore data after the MySQL restarts abnormally, ensuring data consistency.
  • The WAL mechanism of MySQL. Because MySQL performs update operations, in order to be able to respond quickly, it uses asynchronous write back to disk technology. After writing to memory, it returns. In this case, memory data may be lost after a crash. The redo log supports crash safe.

2. What is WAL technology and what are its benefits?

  • WAL (write-Ahead Logging in Chinese) is a key point of Logging that logs are written to memory first and then to disk. After MySQL performs update operations, it logs data before writing it to disk.
  • The advantage is that you do not need to write data to disk in real time for every operation. Even after a crash, you can use redo log to restore data, so you can quickly respond to SQL statements.

3. Redo log writing mode

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 logging first, disk later technique is WAL.

In the computer operating system, the Buffer data in the user space cannot be written directly to the disk, but must pass through the operating system kernel space Buffer (OS Buffer).

  • Logs are initially written to the redo log buffer in the storage engine Innodb, which is done in user space.
  • The logs are then saved to a buffer (OS buffer) in the operating system kernel space.
  • Finally, through system callsfsync(), fromOS bufferWritten to diskredo log file, the write operation is complete. This write to disk operation is calledBrush set.

The redo log buffer writes to the redo log file through the OS buffer. You can use the innodb_flush_log_at_trx_COMMIT parameter to configure the value. The meanings of the parameters are as follows:

  • 0: delayed write. The redo log is not written to the OS buffer, but is written to the OS buffer every second and calls are written to the redo log file.
  • The redo log buffer is written to the OS buffer and stored in the redo log file.
  • 2: called real-time write, delay brush. Each transaction commit is written to the OS buffer, and the log is written to the redo log file every second.

4. Log Execution process

Let’s take a look at the Redo log execution process. Assume that the following SQL is executed:

update T set a =1 where id =666
Copy the code

  1. The MySQL client will request statementsupdate T set a =1 where id =666, to the MySQL Server layer.
  2. After receiving the SQL request, MySQL Server layer analyzes, optimizes and executes it, and sends the generated SQL execution plan to InnoDb storage engine layer for execution.
  3. The InnoDb storage engine layer records the operation of changing a to 1 in memory.
  4. The redo log records are modified after logging to memory. A single line is added to the redo log to specify which data page you want to change.
  5. After that, set the transaction status to Prepare to indicate that the transaction is ready to commit.
  6. When the MySQL Server layer finishes processing the transaction, the transaction status is set to COMMIT, that is, commit the transaction.
  7. After receiving a transaction commit request, the redo log writes the log to disk.

5. Why do redo logs support Crash Safe?

  • If the redo log fails to write, the redo log fails and the transaction cannot commit.
  • The redo log is a page-based internal structure that records changes in the field values of a page. The redo log can be used to restore data after a crash.

6. What is the concept and function of binlog? Can crash-safe be guaranteed?

  • Bin logs are archive logs of the MySQL Server layer. It can implement master/slave replication and data recovery.
  • To restore data, you can retrieve the bin log in a certain time range.
  • However, the bin log cannot be used as a crash safe, because the bin log may hang before the MySQL database is fully written. Therefore, crash Safe can be performed only with the redo log.

7. What are the differences between Binlog and Redolog?

8. What is the flow between the executor and innoDB when executing an UPDATE statement?

  • After the optimizer selects the index, the executor calls the InnoDB read interface to read the rows to be updated into memory
  • After performing SQL operations, update to memory, and write redo log and bin log.
  • Later InnoDB will write the result of this operation back to disk when appropriate.

9. How can I restore data if the database is incorrectly operated?

If the database is misoperated at a certain time, it can find the bin log of the nearest time node to the misoperation, put it back into the temporary database, and then select the misdeleted data node and restore it to the online database.

10. Binlog logs can be in three formats

Binlog logs are in three formats

  • Statement: SQL based replication (statement-based replication,SBR)
  • Row: row-based replication. (row-based replication,RBR)
  • Mixed: Mixed mode replication. (mixed-based replication,MBR)

The Statement formats

Every SQL that modifies data is recorded in a binlog

  • Advantages: You do not need to record the changes of each row, reducing the amount of binlog logs, saving I/O, and improving performance.
  • Disadvantages: Since only executing statements are recorded, in order for the statements to run correctly on the standby database, some information about the execution of each statement must be recorded to ensure that all statements in the standby database get the same result as when they were executed on the primary database.

The Row format

No SQL statement context information is recorded. Only the records that have been modified are saved.

  • Advantage: Binlog does not record context-specific information about the SQL statement being executed, only record which record has been modified. So the rowLevel log content clearly records the details of each row of data modification. There will be no problems with stored procedures, or functions, or triggers being called and fired incorrectly in certain cases.
  • Disadvantages: May generate a large amount of log content.

Mixed format

It’s essentially a combination of Statement and Row. For example, for functions that cannot perform primary/secondary replication, a row file is used to store binlogs. MySQL will use different log formats for each SQL statement executed

11. What is MySQL two-phase commit and why is it required?

In fact, the so-called two-phase is a transaction divided into two phases to commit.

There are three main steps to a two-phase commit:

  1. Description The redo log entered the prepare state after being written
  2. The executor writes the bin log
  3. Enter the COMMIT state and the transaction can commit.

Why two-phase commit?

  • Without a two-phase commit, you might have a situation where the machine crashed before the bin log was written and needed to be restarted. After the redo log restarts, the redo log replays operations performed before the crash. However, data inconsistency occurs when the bin log is used as a backup.
  • If the crash occurred before bin log commit, after the restart, and the redo log is in the prepare state and the bin log is complete (after the redo log is successfully written, the bin log is marked with bin log), the storage engine commits the transaction.
  • The purpose of two-phase commit is to ensure secure consistency between redo log and binlog data. Only these two log files are logically highly consistent. Use the redo log to restore your database to its original state. Use the binlog to back up, restore, and make master/slave copies of your database.

12. If there is no two-phase commit, what is the problem with redo log and bin log?

  • Redo log was written first, and bin log backup was performed after crash.
  • After the crash, the bin log was not written to the redo log, so the transaction was invalid. Therefore, the data in the bin log backup was inconsistent.

13. Binlog disk flushing mechanism

All uncommitted transactions will be recorded in the binlog cache first. When the transaction commits, the data in the cache is written to the binlog log file. The size of the cache is controlled by the parameter binlog_chache_size.

When does the binlog flush to disk? Controlled by the sync_binlog parameter

  • whensync_binlogIf the value is 0, MySQL does not control the refreshing of binlog. Instead, the system determines when the binlog is written to the disk. With this strategy, if the operating system goes down, the binlog in the cache will be lost.
  • sync_binlogIf the value is N, the binlog will be written to the disk every N transactions.
  • whensync_binlogIf the value is 1, the binlog is written to the disk every commit.

Take a look at a more complete flow diagram:

14. What is undo log? What does it do

  • Undo log is called rollback log, which records information before data modification.
  • This is the opposite of the redo log, which records information about data that has been modified. The Undo log records logical changes of data. To roll back previous operations when errors occur, you need to record all previous operations so that the rollback can be performed when errors occur.

15. Describe the Redo log

The size of the redo log is fixed. It writes in a circular way, and when it writes to the end, it goes back to the beginning and circulates. As shown below (picture source network) :

The redo log buffer is made up of four files, ib_logfile_1, ib_logfile_2, ib_logfile_3, ib_logfile_4.

  • Write POS represents the current write record location (logical sequence location of data pages written to disk)
  • Check point Indicates the position after the disk is flushed (written into a disk).
  • The section between write POS and check point is used to record new logs, that is, the space left for new records.
  • Between the check point and write POS are records to be flushed. If disks are not flushed, new records will be overwritten.

With the redo log function, you can restore the remaining data (after the check point) to ensure that committed transaction records are not lost after the database restarts. This function is called crash-safe.

Reference and thanks

  • Log through Redo logs and binlogs
  • The most ox X of whole net!! MySQL two-stage commit talk
  • MySQL tutorial 45