MySQL UPDATE statement execution process wal redolog binlog
Full name: write ahead logging
When a record needs to be updated, the InnoDB engine will first write the record to the redo log and update the memory. At this time, the update is complete. And update the operation record to disk at the appropriate time.
The redo log is in the
Storage engine layerYes, it’s unique to InnoDB engine
The redo log storesPhysical log—That is, “what has been changed on a data page”
Redo log is a circular write. The space is fixed and will be used up.
The redo log space of InnoDB engine is limited. There are four files in a group, 1GB each, 4GB in total. When the “temporary record board” is full, write it from the beginning again.
There are two steps to write redo log—
commitStage — “two stage submission“
With redo log, you can ensure that even if the database is restarted abnormally, you will not lose records, which is called crash safe
The binlog is in the
Server layer, which is the log module of MySQL
Binlog is stored inLogic log—That is, “record the original statement.”. Therefore, it can be used to recover the database – equivalent to rerun the related statements at a certain time.
Binlog is appendable – after a binlog file is written to a certain size, it will be continued in the next file instead of covering the previous file.
How to use two kinds of logs
- Read the row in the table that needs to be updated
- (InnoDB) query whether the line information is in memory. If not, read from disk to memory. And then all return row data
- (actuator) changes row data and writes new rows
- (InnoDB) the new row is updated to the memory and written to the redo log (in the Prepare phase at this time)
- Write to binlog
- (InnoDB) commit transaction (in commit phase)
The use of “two-phase commit” is to avoid the inconsistency between the recovered database and the original state.
To sum up, there will be the following situations:
- Prepare phase crash: after database recovery, the transaction will be rolled back because the redolog in memory is lost and not written to binlog (that is, the DML of the transaction will be invalid)
- Binlog phase crash: the log has not been written to disk during crash, and this transaction will be rolled back when starting
- In the commit phase, crash without commit success: Although the commit in the two-stage commit is not completed, the XID of binlog will be read out when the MySQL database is restored (XID generated in the Prepare phase), and then InnoDB will be told to submit the XID transaction. After InnoDB submits, other transactions will be rolled back to make redolog consistent with binlog（ That is to say, transactions with XID can be recovered.)
Settings to avoid data loss in MySQL crash
We know that when a crash occurs, the data that is lost must be in memory. We can persist it through the following settings
- Redo log is used to ensure crash safe capability
innodb_flush_log_at_trx_commit = 1—The redo log of each transaction is persisted to disk directly
sync_binlog = 1—The binlog of each transaction is persisted to disk
Recovery and expansion
- Latest full backup + binlog to corresponding time point