MySQL master-slave Database Synchronization Mechanism

For MySQL Master-Slave Database Synchronization, we set a Master database and a Slave database (Slave or Secondary ). The slave database replicates data content from the master database for Disaster Backup, read/write splitting, and so on. This article focuses on the synchronization mechanism. As for how to set up the MySQL master database, slave database, and synchronization, there is a lot of content on the Internet. You just need to Google the "MySQL Master/Slave database Settings"

For MySQL Master-Slave Database Synchronization, we set a Master database and a Slave database (Slave or Secondary ). The slave database replicates data content from the master database for Disaster Backup, read/write splitting, and so on. This article focuses on the synchronization mechanism. As for how to set up the MySQL master database, slave database, and synchronization, there is a lot of content on the Internet. You just need to Google the "MySQL Master/Slave database Settings"

Synchronization of MySQL Master/Slave Databases

We set a Master database and a Slave database (Slave or Secondary ). The slave database replicates data content from the master database for Disaster Backup, read/write splitting, and so on.

This article focuses on the synchronization mechanism. As for how to set the MySQL master database, slave database, and synchronization, there is a lot of content on the Internet. You just need to Google the "MySQL Master/Slave database Settings. This is omitted.

Basic Principles of master-slave Database Synchronization

When binary log is enabled for the master database, every operation update, modification, and deletion will be recorded. Therefore, the synchronization process from the database is actually to obtain these processes, and then restore them on site, data Synchronization is achieved.

Old synchronization Implementation Mechanism

Why are we talking about the implementation of the old synchronization, because the old implementation is actually a manifestation of the simplest principle according to the development process. Although not all considerations are taken, the general direction is correct, so the old one is to make a new one.

1. Slave enables a thread that requests the binlog from the last synchronization location to the Master. Then, wait for the Master to return the binlog, execute the events recorded in the binlog once, and record the location to which the events are synchronized.
2. The Master is also very simple. Start a thread and wait for the Slave request. After receiving the request, with the offset of the last Synchronization position, query the binlog after the offset and return it to the Slave.

OK, the task is completed. It is indeed completed, but the Slave thread does a lot of things, both requesting binlog and executing, so the latency of Slave and Master is actually quite large, because of the large latency, we can't find the changed data if the Master crashes after a large number of changes during the delay and the binlogs cannot be synchronized to the Slave. Therefore, the MySQL development team makes an improvement after recognizing this problem.

New synchronization Implementation Mechanism

The principle is the same as that of the old synchronization mechanism, but an improvement has been made. Most developers who are doing thread development understand this improvement and divide the Slave thread into two threads, one for binlogs synchronization (I/O thread ), A job for restoring the site (called an SQL thread ). In this way, the binlogs on the Master can be synchronized to the Slave with minimal latency, and the on-site restoration work on the Slave side can come slowly. In any case, binlogs is the source material, and even if the Master crashes, binlogs is lost, it can also be retrieved from the Slave and restored.

Conclusion

What are the benefits of understanding this implementation mechanism? It is easy to understand the configuration items of the MySQL master database and slave database, and know the direction in which to find the answer when there is a problem with MySQL synchronization.

However, I have to say that the new mechanism seems to have solved the synchronization problem, but in fact it does not. The latency still exists, but it is much smaller. Think about it, the Slave I/O thread has a time distance between two requests. Therefore, the Master may crash after the change within the time distance and the change is lost.

Therefore, to completely avoid this problem, you can only use MySQL Cluster (Database Cluster ). This is a big problem. Let's look at another chapter.

(End)

All Rights Reserved: the source information of the old white is reprinted. < >