Mysql's processing instructions for Binlog _mysql

Source: Internet
Author: User
Tags mysql in rollback
However, it is not intended to describe the implementation details of a storage engine, nor to describe the pros and cons of the various storage engines, but to explain how MySQL handles Binlog and to clarify several confusing issues.
Binlog is important to MySQL and is mainly embodied in its functions. The MySQL official document clearly points out that Binlog's startup will probably add a 1% load to MySQL, so in most cases binlog will not be a MySQL performance bottleneck.
Binlog is MySQL in binary form of the log, it is not encrypted by default, not compressed. Each normal Binlog file header has a 4-byte mark with a value of 0xFE 0x62 0x69 0x6e. Log_event is the unit in the Binlog, that is, under normal circumstances binlog in the form of log_event growth. Except for the mark on the head, Binlog is a log_event sequence. Each log_event has its own binary head, with no reference to each other, and it mainly records the time stamp, the type tag, and other descriptive information.
MySQL to the implementation of the disk operation encapsulated in the Io_cache structure, which also facilitates our research and description of Binlog, if not specifically described, read-write Binlog and read-write Io_cache the same meaning.
To understand the MySQL write Binlog process, you can find an SQL statement processing process to trace. For example, in the simplest case, MySQL invokes the interface ha_update_row that is open for the storage engine, but performs Binlog_query writes to Binlog. The reason for this is that, in the context of a master-slave backup, if the main library writes Binlog successfully and crash in the process of performing the update, it is possible to perform the update successfully from the library, when the main library restarts, inconsistent with the data from the library. If the update operation occurs on a transactional table, the Open interface Ha_autocommit_or_rollback is executed after writing to the Binlog, and the storage engine determines the result of the operation.
In the master-slave backup scenario, the main library is the equivalent of server, from the library equivalent to the client, the two sides adopt a TCP short connection. Requests to read logs from the library, the main library receives the request, reads the local binlog, and sends it to the library. Receives the log from the library, carries on the simple verification to write the local log, is called Relay log. The process from the library here is dedicated to one thread, called a synchronous IO thread. There is also a thread from the library called a synchronous SQL thread. It behaves by periodically reading relay log, parsing and executing the SQL statements that come in sync.

Here are a few questions to answer:

1. binlog format?
Binary sequential storage, no encryption, no compression

Does 2.binlog use Wal?

No
3. The main library to send Binlog, is the use of copy in memory?

Not sure, it is most likely to read a copy from the disk first, and then send.

4. Does relaylog use Wal?

Yes. When a log is received from a library, the relay log is written first

5. Are the binlog and Relaylog sql consistent?

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

Ask a question:
Since Binlog does not use Wal, then in the master-slave scenario, after the MySQL exception, the main library and from the library will not be inconsistent?

There's a problem that hasn't been figured out before:
Since MySQL is the first to do data manipulation, and then write Binlog, if you write Binlog failure, MySQL and crash, data how to do?
The answer is that the storage engine determines the data.
You can separate MySQL from its storage engine because MySQL is just a framework, not an implementation.
Binlog is MySQL's own log, and transactions are guaranteed by the storage engine itself.
In the case of update, MySQL does something simple:
1. Modify Data Update
2. Write Binlog
3. If the table currently being processed is a transactional table, commit or rollback
Note that both the update and the Commit/rollback are implemented by the storage engine, and MySQL simply understands these operations at a logical altitude.
For the transaction-type engine InnoDB, it has a log to guarantee the consistency of the data. In the InnoDB implementation, before update modifies the data,
A new transaction is created and a rollback point is established. The Commit/rollback interface provided in InnoDB commits/rolls back the transaction.
So for InnoDB, the transaction of each SQL statement actually contains the Binlog write operation. But even then, InnoDB still can't guarantee
Binlog and data consistency, because InnoDB crash after a successful write commit, the rollback operation does not roll back binlog. According to the manual,
The--INNODB-SUPPORT-XA is set to 1 and the sync_binlog=1 is guaranteed to ensure INNODB binlog and data consistency.

For non-transactional engine MyISAM, there is no chance of commit/rollback, so in exceptional cases, the data will be inconsistent with binlog.
So the new question arises: How does MyISAM deal with this inconsistency?
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.