How mysql processes binlog _ MySQL

Description of how mysql processes binlog bitsCN.com. However, we do not want to describe the implementation details of a storage engine or describe the advantages and disadvantages of various storage engines. we just want to describe how mysql processes binlog, clarify several confusing issues.
Binlog is important for mysql and mainly reflected in its functions. The Mysql official documentation clearly states that starting binlog will probably increase the load of mysql by 1%. Therefore, in most cases, binlog will not become the performance bottleneck of mysql.
Binlog is a log printed in binary form by mysql. it is not encrypted by default and is not compressed. Each normal binlog file header has a four-byte mark with a value of 0xfe 0x62 0x69 0x6e. LOG_EVENT is the unit in binlog. normally, binlog increases by LOG_EVENT. Except for the header tag, binlog is a LOG_EVENT sequence. Each LOG_EVENT is an independent unit and has no mutual reference relationship. it also has its own binary header, which records the time stamp, type tag, and other description information.
Mysql encapsulates the implementation of disk operations in the IO_CACHE structure, which facilitates our research and description of binlog. if not specifically described in the following article, read/write binlog and read/write IO_CACHE have the same meaning.
To understand the binlog writing process of mysql, you can find an SQL statement to track the processing process. Take update as an example. In the simplest case, mysql will first call the ha_update_row interface opened for the storage engine, but execute binlog_query to write the binlog. The reason for this is that, in the scenario of master-slave backup, if the binlog is successfully written to the master database and crash is executed during update, the slave database may execute update successfully, after the master database is restarted, it is inconsistent with the data in the slave database. If the update operation occurs on a transactional table, the open interface ha_autocommit_or_rollback will be executed after the binlog is written. the storage engine determines the operation result.
In the scenario of master-slave backup, the master database is equivalent to the server, the slave database is equivalent to the client, and both parties adopt tcp short connections. The slave database sends a log reading request. the master database receives the request, reads the local binlog, and sends the request to the slave database. The relay log is used to receive logs from the database, perform simple verification, and then write local logs. A thread is responsible for the process from the database, which is called a synchronous io thread. There is also a thread in the slave database, called a synchronous SQL thread. It regularly reads relay logs, parses and executes the synchronized SQL statements.

Below are a few questions:

1. what is the binlog format?
Binary sequence storage, not encrypted, not compressed

2. does binlog use WAL?

No
3. Does the master database use the copy in memory to send the binlog?

Uncertain, it is likely that a copy is read from the disk and then sent.

4. does relaylog use WAL?

Yes. After receiving logs from the database, the relay log will be written first.

5. are the SQL statements of binlog and relaylog consistent?

If the network transmission is correct and reliable, yes

Ask a question:
Since binlog does not use WAL, will the master database and slave database be inconsistent after mysql exception in the master-slave scenario?

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? BitsCN.com