What are MySQL Binlog logs and master-slave replication?

1. Introduction to Binlog log

Binlog is the abbreviation of Binary log, that is, binary log. The three main functions of Binlog include converting random IO into sequential IO for persistence, realizing master-slave replication and supporting data recovery. This article focuses on issues related to master-slave replication.

Binlog log consists of an index file and many log files. Each log file consists of a magic number and an event. Each log file ends with a Rotate type event.

For each event, it can be divided into two parts: event header and event body:

The event header The structure is as follows:

The structure of the event body includes two parts: fixed size and variable size.

As for the format of Binlog log, you can have a simple understanding. Interested students can study in depth.

2. Master-slave replication

2.1 Master-slave replication process

MySQL master-slave replication process is roughly as follows:

• The main library synchronizes its own Binlog log to the slave library

• The IO thread of the slave library writes the Binlog log content to the Relay Log

• Get the Relay Log from the SQL thread of the library and play it back in the database

2.2 GTID

GTID refers to the global Transaction flag, used to mark master-slave synchronization.

When the master node submits a transaction, a GTID will be generated and recorded in the Binlog log. When the IO thread of the slave library reads the Binlog log, it will store it in its own Relaylog and set this value to gtid_next, which is the next GTID to be read. When reading this gtid_next from the library, it will compare Whether there is this GTID in your Binlog log:

• If there is this record, it means that the transaction with this GTID has been executed and can be ignored (idempotent).

• If there is no such record, the slave will execute the GTID transaction and record it in its own Binlog log.

2.3 Replication model

• Asynchronous replication: master pushes the Binlog log to the slave, and the master does not need to wait until the slave After successfully updating the data to the Relay log, the main database can directly submit the transaction. This mode sacrifices data consistency.

• Synchronous replication: Every time the user operates, it must be ensured that both the Master and Slave are executed successfully before returning it to the user.

• Semi-synchronous replication: does not require the Slave to execute successfully, but can notify the Master to return after successfully receiving the Master log.

2.4 MGR mode

Distributed consensus algorithm Paxos. A database cluster consists of at least 3 or more nodes. Transaction submission must be approved by more than half of the nodes before it can be submitted. Multi-write mode is supported.

MGR is a share-nothing replication solution, implemented based on the distributed paxos protocol. Each instance has an independent complete copy of the data. The cluster automatically checks node information and synchronizes data. Both single-master mode and multi-master mode are provided. The single-master mode can automatically select the master after the main database goes down. All writes are performed on the master node. The multi-master mode supports multi-node writing. The cluster provides fault tolerance. As long as most nodes are running normally, the cluster can provide services normally.

2.5 Parallel playback

Transaction playback is the process of executing Relay Log from the SQL thread of the library. Parallel playback is to improve the efficiency of this process and perform transactions that can be carried out in parallel at the same time.

• Parallel playback based on logical clock

Because MySQL's own transactions have ACID characteristics, the transactions from the master database to the slave database are synchronized. As long as the logical timing of their execution overlaps, the two transactions can be safely played back in parallel.

• Parallel playback based on writeSet

Store the transaction set regarding a specific data block area within a certain period of time in a HashMap. Conflicts do not occur between transactions within the same group or between transactions with overlapping logical clocks, and otherwise it is impossible to determine whether a conflict exists.

The above is the detailed content of What are MySQL Binlog logs and master-slave replication?. For more information, please follow other related articles on the PHP Chinese website!