So without further ado here’s a picture

update T set value = value+1 where ID =2
Copy the code

I think most people may read this chart, think for a moment, the next need not continue to see, but considering some friends or novice (including their own) and the review behind, or a little nagging.

The update process

First, the dark-background representation in the figure above is executed in the executor, the Server layer, and the light-colored representation is executed in the InnoDB engine. Since many of you are not professional DBAs or don’t have a clear understanding of the inner workings of MySQL, I will introduce Redolog and binlog briefly.

  • redolog

    The redo log is a physical log that stores the final contents of the database. It has a fixed size and can be read and written in a cycle. Innodb_flush_log_at_trx_commit is set to 1. The redolog file and redolog buffer will be flushed to disk when the transaction is committed. The two states are prepare and COMMIT. Data will be restored according to the COMMIT state during MySQL restart and recovery.

  • binlog

    Archiving logs are logical logs that store initial changes. The logic can be simply interpreted as SQL statements and can be appended. Generally, the sync_binlog is set to 1, indicating that the data in the binlog is flush to disks for archiving during the commit transaction. Data recovery and synchronization are implemented through binlog.

Update T set value = value+1 where ID =2

  1. The lexical analyzer recognizes the error update statement;
  2. The executor queries InnoDB to find data that meets ID = 2;
  3. The actuator increments the value of value by 1;
  4. The executor tells InnoDB to write the new value to InnoDB’s memory;
  5. InnoDB adds a record to redolog and sets the state of the record to prepare.
  6. Update T set value = value+1 where ID =2 is written to binlog.
  7. Commit the transaction, set the status of the prepared file in redolog to COMMIT, and flush the new data from memory to disk.

So that’s a simple process to understand, so why write redolog separately? The legendary two-phase commit? Here’s another simple analysis.

First, to summarize the benefits of this approach: ensure that all of the above procedures will not result in transaction loss or exceptions if the MySQL instance crashes.

Here’s why:

  1. If the redolog and binlog files are recovered, the transaction does not exist. If the redolog and binlog files are recovered, there will be no problems.
  2. If the prepare file crashed after redolog was written, the transaction will be rolled back as long as a prepare log is compared with the binlog file.
  3. If a crash occurs after writing the binlog, the system compensates for redolog according to the binlog. The prepared record before redolog is set to commit, and the transaction is guaranteed.
  4. Finally after commit, crash, redolog, and binlog are normal.

Redolog only appears in InnDB, and is written in a loop, can not persist, so temporarily can not use Redolog to master or data backup

The above is a summary of a lot of blog and book content, not completely original, I am also sorting out, I hope you can correct the wrong.