Shulou(Shulou.com)06/02 Report--

What is the principle of master-slave replication in MySQL? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

How to achieve the read-write separation of MySQL?

In fact, it is very simple, it is based on the master-slave replication architecture, to put it simply, create a master library, hang multiple slave libraries, and then we just write the master library, and then the master library will automatically synchronize the data to the slave library.

What is the principle of MySQL master-slave replication?

After the master database writes the changes to the binlog log, and then connects to the master library from the slave library, there is an IO thread in the slave library to copy the binlog log of the master library to its own local and write it to a relay relay log. Then a SQL thread from the library reads the binlog from the relay log, and then executes the contents of the binlog log, that is, SQL is executed locally again, which ensures that the data of the main library is the same.

A very important point here is that the process of synchronizing master database data from the slave library is serialized, that is, parallel operations on the master library will be performed serially on the slave library. So this is a very important point. Due to the characteristics of copying logs from the master database and executing SQL serially from the slave database, in high concurrency scenarios, the data of the slave database must be slower than that of the master database, and there is a delay. Therefore, it often occurs that the data just written to the main database may not be readable, and it will take dozens or even hundreds of milliseconds to read.

And there is another problem here, that is, if the master library suddenly goes down, and then the data is not synchronized to the slave database, then some data may not be available on the slave database, and some data may be lost.

So MySQL actually has two mechanisms in this block, one is semi-synchronous replication, which is used to solve the problem of data loss in the master database, and the other is parallel replication, which is used to solve the problem of master-slave synchronization delay.

This so-called semi-synchronous replication, also known as semi-sync replication, means that after the master database writes the binlog log, it will force the data to be synchronized to the slave database immediately at this time. After the slave database writes the log to its own local relay log, it will then return an ack to the master database. The master database will not consider the write operation complete until it has received at least one ack of the slave database.

Parallel replication means to start multiple threads from the library, read the logs of different libraries in relay log in parallel, and then replay the logs of different libraries in parallel, which is library-level parallelism.

MySQL master-slave synchronization delay problem (essence)

It is true that the frontline has dealt with the online bug caused by master-slave synchronization delay, which is a small production accident.

Is that it? the scene. A classmate wrote the logic of the code in this way. First insert a piece of data, then check it out, and then update the data. At the peak of the production environment, the write concurrency reached 2000 seconds, and the master-slave replication latency was about tens of milliseconds smaller. Online, we will find that there is always some data every day, and we expect to update some important data status, but not during the peak period. The user gives feedback to the customer service, and the customer service will give back to us.

We use the MySQL command:

Show status

Looking at the Seconds_Behind_Master, you can see that the data copied from the main library is several ms behind.

In general, if the master-slave delay is serious, there are the following solutions:

Sub-library, split a master library into multiple master libraries, the write concurrency of each master library is reduced several times, and the master-slave delay can be ignored.

Turn on parallel replication supported by MySQL, and multiple libraries replicate in parallel. If the write concurrency of a library is particularly high, and the write concurrency of a single library has reached 2000 Universe, parallel replication is still meaningless.

Rewrite the code, write the code students, be careful, insert data immediately query may not be found.

If it does exist, you must first insert it, query it immediately, and then immediately perform some operations in reverse to set up a direct connection to the main library for this query. This method is not recommended, if you do so, the meaning of separation of reading and writing will be lost.

Distributed transaction series:

Overview of Spring distributed transaction implementation

Distributed transaction implementation of REST Micro Services-fallback pattern using Spring Cloud

Distributed transaction implementation of Spring-with and without XA

Distributed transaction implementation of REST Micro Services-based on message Middleware

Distributed transaction implementation of REST Micro Services-introduction to distributed system, transaction and JTA

Design of a treasure transaction architecture

Talk about distributed transactions in vernacular

Distributed transaction solution

Message queue series:

Why use message queuing?

How to ensure the high availability of message queues?

How to ensure that messages are not consumed repeatedly? In other words, how to ensure the idempotency of message consumption?

How to ensure the reliable transmission of messages? Or, how to deal with the problem of message loss?

How to ensure the sequence of messages?

How to solve the delay and expiration of message queues? What should I do when the message queue is full?

Sub-library and sub-table series:

Why do you want to divide the database and table?

How to design to make the system switch from never sub-database sub-table to sub-database sub-table dynamically?

How to design a sub-database and sub-table scheme that can dynamically expand and reduce capacity?

After the sub-library and sub-table, how to deal with the id primary key?

The answer to the question about the principle of master-slave replication in MySQL is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.