Back-end distributed series: Distributed Storage-MySQL database transaction and replication _ MySQL

I haven't written any technical articles for a long time, because I have been thinking about how to write the "backend distributed" series. Recently, I have basically figured out that "backend distributed" includes "distributed storage" and "distributed computing. Find the answer based on the problems encountered in actual work

I haven't written any technical articles for a long time, because I have been thinking about how to write the "backend distributed" series. Recently, I have basically figured out that "backend distributed" includes "distributed storage" and "distributed computing. In combination with the problems encountered in actual work and looking for answers to explore the technology, many times we are not creating new technologies, but applying them. To improve efficiency and effectiveness, we need to understand the principles and working methods of some technologies. The technical principles are analyzed from the perspective of users, and open-source technical products and frameworks are used as a reference for implementation of a type of technology. The main purpose is to clarify the principle. if there are no special technical details for the specific implementation, try to get it started.

Transactions and replication

Recently, I participated in a data distribution project involving data distribution of MySQL databases. In short, it is necessary to implement multi-point writability in a remote data center and ensure that the distributed data can achieve eventual consistency. In the past, data distribution for MySQL was only a separation of read and write. the master database and read slave database can be written through the master-slave replication of the database itself. Now we need a dual-write master database and reach a final consistency after a short delay. this problem is complicated at first glance, but it is still the final consistency of data.

First, let's go back to the simplest case. how can we ensure data consistency when there is only one MySQL database? All users who know the database know that this is guaranteed by the transaction features of the database. transactions include the following four features:

Atomicity Consistency Isolation Durability persistence

The four major features of ACID in transactions are not the focus of this article, so I will not discuss them in the academic field. if you do not know about the four features, you can refer to [3] For more information. Here, I just want to raise a question. a single database transaction can ensure data consistency. how can I ensure data consistency between the master and slave databases when MySQL is deployed in a master-slave architecture?

To provide the master-slave replication function, MySQL introduces a new log file called binlog, which contains a collection of event logs that cause data changes. The slave database requests the master database to send binlogs and restores data through log events to write data to the slave database. Therefore, the data source of the slave database is binlog. In this way, only the binlog and local data of the MySQL master database can be consistent to ensure that the data of the master and slave databases is consistent (the master database is inconsistent due to network transmission temporarily ). We know that the guarantee of local data consistency is achieved by the database transaction feature, and how is the database transaction implemented? First look at the figure below:

VcrHu/examples + 3 aGjy/examples + rn9s8y88ruvw + examples/W0LbPo6y/ydLUzai5/examples/bKws7xu/examples + samples /summary + s9a + summary/summary + qwcujrL/J0tSyzr + summary/XWzVdoaM8L3A + DQo8aDIgaWQ9 ">

We first raised a question, and then thought about it from the perspective of data consistency, referring to the implementation method of MySQL. Clarified and analyzed how MySQL standalone environment ensures data consistency of the replication mechanism, that is, the consistency between binlog and transaction data. Later, we can implement replication and ensure the consistency of master-slave replication based on the binlog mechanism. Master-slave replication introduces network factors, which further increases the complexity of ensuring master-slave data consistency, and will be further analyzed later.