MySQL's BINLOG Processing

MySQLIt has better scalability than other open-source databases. The main reason is that it provides open interfaces for the storage engine. I like tossing databases myselfProgramMembers can start from this interface to create a personalized database. However, we do not want to describe the implementation details of a storage engine or describe the advantages and disadvantages of various storage engines.MySQLHow to handleBINLOGAnd clarify several confusing issues.

BINLOG for mysql it is important, mainly reflected in its functions. mysql BINLOG is mysql added 1% , so in most cases, BINLOG none will become mysql performance bottleneck.

BINLOG Yes MySQL Logs that are printed in binary format are not encrypted by default and are not compressed. Each normal BINLOG File Header, 4 Byte mark, value: 0xfe 0x62 0x69 0x6e . Log_event Yes BINLOG The Unit in, that is, under normal circumstances BINLOG Follow Log_event . Remove the header tag, BINLOG Is Log_event . Each Log_event They are all independent units and do not reference each other. They also have their own binary headers, which record time stamps, type tags, and other descriptive information.

MySQLEncapsulate the implementation of disk operations inIo_cacheStructure, which makes it easier for usBINLOGIn this article, if there is no special description, read/writeBINLOGAnd read/writeIo_cache.

To understand MySQL Write BINLOG You can find SQL Statement. To Update For example, in the simplest scenario, MySQL Will first call the interface opened for the storage engine Ha_update_row However, execute Binlog_query Pair BINLOG Write operation. The reason for this is that in the scenario of master-slave backup, if the master database writes BINLOG Succeeded. Update In the process Crash The slave database may execute Update Successful. After the master database is restarted, it is inconsistent with the data in the slave database. If Update The operation occurs on a transactional table and BINLOG The open interface will be executed later Ha_autocommit_or_rollback Which is determined by the storage engine.

In the scenario of master-slave backup, the master database is equivalent Server The slave database is equivalent Client , Adopted by both parties TCP Short connection. When a log reading request is sent from the database, the master database receives the request and reads the local BINLOG And then send it to the slave database. Receives logs from the database, performs simple verification, and then writes local logs. Relay log . A thread is responsible for the process from the database, which is called synchronization. Io Thread. There is another thread in the slave database, called synchronization. SQL Thread. It reads data regularly. Relay log , Parse and execute the synchronized SQL Statement.

Below are a few questions:

1.BINLOGFormat?

Binary Sequence storage, not encrypted, not compressed

2.BINLOGUseWal?

No

3.Master database sendingBINLOGIs in memoryCopy?

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

4.RelaylogUseWal?

Yes. After receiving logs from the databaseRelay log

5.BINLOGAndRelaylogOfSQLAre they consistent?

On the premise that the network transmission is correct and reliable,Yes

 

Ask a question:

SinceBINLOGNot usedWalIn the master-slave scenario,MySQLAfter an exception, will the master database and slave database be inconsistent?

 

 

 

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?