Interview summary

Did you do it MySQL Read / write separation ？ How to achieve mysql Separation of reading and writing ？MySQL What is the principle of master-slave replication ？ How to solve mysql Delay problem of master-slave synchronization ？

This , High concurrency stage , That must be the separation of reading and writing , What do you mean by that? ？ Because in fact most Internet companies , Some websites , Or is it app, In fact, it's all about reading more and writing less . So in this case , It's about writing a main library , But the master library has multiple slave libraries , Then read from multiple slave Libraries , That can support higher read concurrency pressure ？

Analysis of interview questions

（1） How to achieve mysql Separation of reading and writing ？

It's very simple , It's based on the master-slave replication architecture , Simply speaking , Just a main library , Hang multiple slave Libraries , And then we just write the main library , Then the master database will automatically synchronize the data to the slave database .

（2）MySQL What is the principle of master-slave replication ？

The master database will change to write binlog journal , Then connect the slave library to the master library , There's one from the library IO Threads , Will be the main library of binlog Copy the log to your own place , Write to a relay log . And then there's one from the library SQL The thread reads from the relay log binlog, And then execute binlog The contents of the log , That is to do it again on your own SQL, In this way, you can ensure that your data is the same as that of the main database .

 

Here's a very important point , That is, the process of synchronizing master database data from slave database is serialization , In other words, parallel operations on the main database , It will be executed serially on the slave library . So this is a very important point , Because the slave database copies logs from the master database and executes it serially SQL Characteristics , In high concurrency scenarios , The data of the slave database must be slower than that of the master database , There is a delay . So there's a lot of , The data just written to the main database may not be read , It's going to take tens of milliseconds , Even hundreds of milliseconds to read .

Extended problem ：

If the main library suddenly goes down , Then it happens that the data has not yet been synchronized to the slave library , Then some data may not exist in the slave database , Some data may be lost .

therefore mysql In fact, there are two mechanisms in this area

• Semi-synchronous replication , It is used to solve the problem of data loss in the main database
• Parallel replication , It is used to solve the problem of master-slave synchronization delay

This is called semi synchronous replication ,semi-sync Copy , Write to the main database binlog After the log , It will force the data to be synchronized to the slave library immediately at this time , Write the log from the library to your own local relay log after , And then there's going to be a return ack To the main library , The master library received at least one slave Library's ack After that, the write operation will be considered completed .

 

Parallel replication , It refers to opening multiple threads from the library , Parallel reading relay log Logs from different libraries in , Then replay the logs of different libraries in parallel , This is library level parallelism .

1. The principle of master-slave replication
2. The cause of the master-slave delay problem
3. Data loss of master-slave replication , And the principle of semi synchronous replication
4. The principle of parallel replication , Multi database concurrent replay relay journal , Alleviate the problem of master-slave delay

 

（3）mysql Master slave synchronization delay problem （ The essence of ）

 

I did deal with it online because of the delay of master-slave synchronization , Lead to online bug, Small production accidents

show status,Seconds_Behind_Master, You can see how far behind is copying data from the master library ms

In fact, we often come across this thing , For example, it uses mysql After the master-slave architecture , You might find , The data just written into the database was not found , And it ended up ....

So in fact, you have to think about the scene where you should use this mysql Master slave synchronization , The suggestion is that reading is much more than writing , In addition, when reading, the requirement for data timeliness is not so high , use mysql Master slave synchronization

So at this point , One thing we can think about is , You can use it. mysql Parallel replication of , But the problem is that it's Library level parallelism , So sometimes it doesn't work very much

So at this point .. Generally speaking , We will make sure that we can find the scene immediately after we write it , The main library is read by force , So that you can read the data for sure . In fact, it's no problem to use some database middleware .

Generally speaking , If the master-slave delay is serious

1. sub-treasury , Split a main library into 4 A main storehouse , The write concurrency of each main library is 500/s, In this case, the master-slave delay is negligible
2. open mysql Supported parallel replication , Parallel replication of multiple libraries , If we say that the write concurrency of a library is particularly high , Single library writing and developed to 2000/s, Parallel copy still doesn't make sense .28 The laws of , Many times, for example , Just a few order forms , Written 2000/s, Dozens of other tables 10/s.
3. Rewrite the code , Students who write code , Be careful , At that time, we actually asked that student to rewrite the code for a short time , After inserting data , Just update , Don't inquire
4. If it does exist, it must be inserted first , Immediately ask to find out , And then you immediately have to do something in reverse , Set up a direct connection to the main database for this query . This method is not recommended , If you do this, the meaning of the separation of reading and writing will be lost