How to achieve MySQL read-write separation?

Posted May 28, 20204 min read

Interview questions

Do you do MySQL read-write separation? How to achieve MySQL read-write separation? What is the principle of MySQL master-slave replication? How to solve the delay problem of MySQL master-slave synchronization?

Interviewer psychoanalysis

At this stage of high concurrency, it is definitely necessary to do read and write separation, what does it mean? Because in fact, most Internet companies, some websites, or apps actually read more and write less. So for this situation, it is to write a master library, but the master library hangs multiple slave libraries, and then reads from multiple slave libraries, then can it support higher read concurrency pressure?

Analysis of interview questions

How to achieve MySQL read-write separation?

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

What is the principle of MySQL master-slave replication?

The master library writes the changes to the binlog log, and then connects the slave library to the master library. The slave library has an IO thread to copy the master library's binlog log to its own local and write it into a relay relay log. Then there is a SQL thread in the slave library that will read the binlog from the relay log, and then execute the contents of the binlog log, that is, execute the SQL again locally, so that you can ensure that the data in the master library is the same.


There is a very important point here is that the process of synchronizing the data of the master library from the library is serialized, which means that the parallel operations on the master library will be executed serially on the slave library. So this is a very important point. Due to the characteristics of copying logs from the master library and serial execution of SQL from the library, in high concurrency scenarios, the data in the slave library must be slower than the master library, which is delayedof. Therefore, it often occurs that the data just written to the main library may not be readable, and it may take tens of milliseconds or even hundreds of milliseconds to be read.

And there is another problem here. If the master database suddenly goes down, and the data has not been 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 area, one is semi-synchronous replication, which is used to solve the data loss problem of the master database; one is parallel replication, which is used to solve the master-slave synchronization delay problem.

This so-called semi-synchronous replication, also known as semi-sync replication, refers to the fact that after the master library writes to the binlog log, it will force to immediately synchronize the data to the slave library and the slave library After writing the log to your local relay log, it will then return an ack to the master library, and the master library will only think that the write operation is completed after receiving the ack of at least one slave library.

The so-called parallel replication refers to starting multiple threads from the library, reading the logs of different libraries in the relay log in parallel, and then replaying the logs of different libraries in parallel, which is library-level parallelism.

MySQL master-slave synchronization delay problem(essential)

In the past, online bugs caused by master-slave synchronization delays have been dealt with online, which is a small production accident.

What is this scene? A classmate writes code logic like this. Insert a piece of data first, then find it out, and then update this piece of data. In the peak period of the production environment, the write concurrency reached 2000/s. At this time, the master-slave replication delay is about tens of milliseconds. You will find online that there is always some data every day. We expect to update some important data status, but it is not updated during the peak period. Users and customer service feedback, and customer service will feedback to us.

We use MySQL commands:

show status

Looking at Seconds_Behind_Master, you can see that the data copied from the master library is a few ms behind.

Generally speaking, if the master-slave delay is more serious, there are the following solutions:

  • Sub-library, splitting a master library into multiple master libraries, the write concurrency of each master library is reduced by several times, and the master-slave delay can be ignored at this time.
  • Turn on parallel replication supported by MySQL, and multiple libraries replicate in parallel. If the write concurrency of a certain library is particularly high, and the write concurrency of a single library reaches 2000/s, parallel replication is still meaningless.
  • Rewrite the code. Students who write code should be cautious. The query may not be found immediately when inserting data.
  • If it does exist, it must be inserted first, and it will be queried immediately, and then it will immediately perform some operations in reverse, set up a direct connection to the main library for this query. Not recommended This method, if you do this, the meaning of read and write separation will be lost.

Follow my WeChat public account, get the update reminder of My Blog for the first time, and more surprises are waiting for you ~

Scan the QR code below or search for WeChat shenshan \ _laoyuan Follow

Deep Mountain Old Ape

This article is automatically published by one article multi-posting platform ArtiPub