Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve synchronous replication in MySQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Primary master synchronization scheme for native replication based on MySQL

This is a common solution, generally speaking, when small and medium-sized scale, the use of this framework is the most convenient.

The two nodes can adopt a simple dual-master mode and use a dedicated line connection. After the failure of the master_A node, the application connection can be quickly switched to the master_B node, and vice versa. There are several things to pay attention to, in the case of brain fissure, the two nodes write the same data and cause a conflict, while setting the auto_increment_increment (self-increment step size) and auto_increment_offset (starting value) of the two nodes to different values. Its purpose is to avoid the unexpected downtime of the master node, some binlog may not be copied to the slave in time to be applied, which will cause the self-increment of the newly written data in slave to conflict with the original master, so it will be staggered in the first place. Of course, if there is a suitable fault-tolerant mechanism to solve the master-slave self-increment ID conflict, you can not do so. Using the updated data version 5.7 replication, you can use multithreaded replication to greatly reduce replication latency. At the same time, another option that is particularly sensitive to replication delay is semi-sync semi-synchronous replication, which basically has no delay, but transaction concurrency performance will suffer a considerable loss. Especially in two-way writing, it needs a comprehensive evaluation before making a decision.

Based on Galera replication scheme

Galera is a multi-master data synchronous replication mechanism provided by Codership, which can achieve synchronous data replication, read and write among multiple nodes, and ensure high service availability and data consistency of the database. The high availability schemes based on Galera mainly include MariaDB Galera Cluster and Percona XtraDB Cluster (PXC for short).

At present, PXC will be used more, the data is strictly consistent, especially suitable for e-commerce applications, but PXC also has its limitations. If the amount of concurrent transactions is large, it is recommended to use InfiniBand network to reduce network latency. Because PXC has write expansion and short board effect, concurrent efficiency will be greatly lost. Similar to semi-sync semi-synchronous replication, Gelera can only use three nodes. Habitual problems of performance and stability caused by network jitter

Based on Group Replication scheme

Through the Paxos protocol to provide database cluster node data strong consistency guarantee, MGR is precisely a high availability solution officially launched by MySQL, based on native replication technology, and provided in the form of plug-ins, and all nodes between clusters can be written, solving the write performance of a single cluster, all nodes can read and write, solve the problem of brain fissure caused by network partition, improve the reliability of replication data, but the reality is still some cruel At present, there are not many trials, and only InnoDB tables are supported, and each table must have a primary key for write set conflict detection, the GTID feature must be turned on, and the binary log format must be set to ROW to select master and write set.

COMMIT may lead to failure, similar to the failure scenario of snapshot transaction isolation level. Currently, a MGR cluster supports up to 9 nodes, does not support foreign keys on save point, cannot do global constraint detection and partial rollback, and binary logs do not support binlog event checksum.

Based on canal scheme

For the real-time synchronization of the database, Alibaba has a special open source project, namely otter, to realize the synchronous replication of the distributed database. Its core idea is still to obtain the incremental data logs of the database to carry out quasi-real-time synchronous replication. Therefore, otter itself relies on another open source project, canal, which focuses on obtaining incremental database synchronization log information.

At present, the focus of otter is to achieve synchronous database replication between mysql, that is, the use of similar technology to achieve two-way synchronous database replication between two mysql databases. Note that the two-way itself means that either A-> B or B-> A, the node itself is unidirectional at some point in time.

Master-slave replication is divided into three steps:

Master records changes to a binary log (binary log) (these records are called binary log events, binary log events, and can be viewed through show binlog events)

Slave copies the binary log events of master to its relay log (relay log)

Slave redoes the events in the relay log and changes the data that reflects itself.

The principle of canal is relatively simple:

Canal simulates the interaction protocol of mysql slave, disguises itself as mysql slave, and sends dump protocol to mysql master

Mysql master receives the dump request and starts pushing binary log to slave (that is, canal)

Canal parses binary log objects (originally byte streams)

On how to achieve dual-live synchronous replication in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.