I haven't figured it out before:
Since MySQL first performs data operations and then writes BINLOG, what should we do if it fails to write BINLOG and MySQL crash?
The answer is that data is determined by the storage engine.
You can separate MySQL from its storage engine, because MySQL is only a framework rather than an implementation.
BINLOG is MySQL's own log, and transactions are guaranteed by the storage engine itself.
Taking update as an example, MySQL does the following simple tasks:
1. modify data update
2. Write BINLOG
3. If the currently processed table is a transactional table, commit or rollback
Note that both update and commit/rollback are implemented by the storage engine. MySQL only understands these operations logically.
For the transaction engine InnoDB, it has logs to ensure data consistency. In the implementation of InnoDB, before data is updated,
Creates a transaction and creates a rollback point. The commit/rollback interface provided by InnoDB commits/rolls back transactions.
Therefore, for InnoDB, the transaction of each SQL statement actually contains the write operation of BINLOG. Even so, InnoDB still cannot guarantee
BINLOG is consistent with the data, because InnoDB does not roll back the BINLOG after writing the commit successfully. According to the manual,
Set -- InnoDB-support-Xa to 1 and sync_binlog = 1 to ensure that the BINLOG and data of InnoDB are consistent.
For non-transactional engine MyISAM, there is no chance of commit/rollback. Therefore, data is inconsistent with BINLOG in case of exceptions.
So a new problem arises: how does MyISAM handle this inconsistency?