This article will show you the principle of MySQL master-slave synchronization

Brief Analysis of Mysql Master-Slave Synchronization Principle

Before we start explaining the principles, let's first summarize the knowledge.
What is master-slave, why do we need master-slave, how can we implement master-slave, the principle of MySQL master-slave synchronization

1. What is master-slave?

In fact, the concept of master-slave is very simple. The host is the service we usually use for reading and writing. We call it master.
A slave is an extension of the host. It is usually not used for reading and writing. We call it a slave.
The data on the slave is obtained from the host in some form and written to the slave. In theory, the slave cannot directly obtain data from the outside world.

2. Why do we need master-slave relationship?

The earliest master-slave system was for master-slave, that is, the master is the main machine and the salve is the backup machine. Because the data volume and concurrency of early software were not high. The host can fully support daily use.
Therefore, the high availability of the database is guaranteed through the master-slave mode. When the master database is detected to be down, the data source of the service is automatically switched to the backup database.
As the business developed, we found that a single master was often unable to support the business needs, so we began to expand the traditional master-slave system.

(1) One master and one slave. The slave not only serves as a backup machine, but also as a database for reading data sources. When the business service writes data, it writes it to the master machine, and when it reads data, it reads it from the slave machine. Thereby reducing the pressure on the host (2) One master and multiple slaves. After the master writes and the slave reads, we found that a single slave node still has a performance bottleneck when supporting business query data, so we will horizontally expand the slave to achieve multiple slaves.
(3) Double M
The slave is promoted to the master, and the two masters are slaves of each other, sharing the read and write pressure. (4) Cascade Replication Cascade replication is similar to one master and multiple slaves, the difference is that the data source of the secondary slave comes from the slave instead of the master. This is mainly due to the impact of synchronized data on the performance of the host after the number of slaves increases. (5) Multiple masters and one slave. In scenarios where there are more writes and fewer reads, multiple masters are used to reduce the pressure on the host, while one slave is used to complete the read operation and data backup.

3. How to achieve master-slave synchronization?

Don't think that master-slave synchronization is too difficult to understand. It is actually very simple. For a software engineer, you only need to be able to write code, find data from the master database, connect to the backup database, and write the data into it. This is the easiest master-slave synchronization to understand.
However, this method is too rough and has poor performance, so MySQL itself has provided a complete master-slave synchronization mechanism to ensure that data can be efficiently synchronized from the host to the slave.
In addition to MySQL's own master-slave synchronization, the industry also has some components that support it, such as Alibaba's canal. This is mainly for more flexible data synchronization, such as parsing the synchronized data. The synchronized slave machine no longer needs to use MySQL, but other storage services.

4. The principle of mysql master-slave synchronization

MySQL itself implements master-slave synchronization, mainly using binlog logs.
Since it is not the focus of this article, here is a brief description of the binlog log:
It is a log used by MySQL to record db changes.
For example, the value of a piece of data is changed from 0 to 1 (DML statement)
For example, a table is deleted (DDL statement)
There are three forms of binlog:
(1) statement: records the specific operation statement that caused the change, such as insert xxxxx....
(2) row: based on data rows. The original data row value is changed from xx to yy. This type of data row usually takes up more space.
(3) mixed: mixed mode, the service itself decides what form the change will take.
When the SQL operation is written to the binlog, it is considered that the SQL execution is successful, instead of being written to the corresponding disk (flush disk). Therefore, we can understand the corresponding value in binlog as a mapping of mysql. Synchronizing mysql data is different from synchronizing the data in the disk, but only synchronizing the binlog log is needed.
The specific synchronization principle is as follows:
(1) After the master-slave synchronization is set up (after relevant operations such as IP, port, service ID, etc. are set up)
(2) The relevant changes will be written to the binlog. (3) The maser will start a thread: the binlog dumplog thread. This thread will notify the slave that there is a SQL change and send the binlog changes to the slave. (4) After the slave receives the request, it will start a thread: the i/o thread. This thread will load the received binlog log into the relay log delay log. (5) Another thread in the slave: the SQL thread will read the information in the relay log and refresh it to the slave. See the figure below for details.

According to the CAP theory (not clear here), this architecture obviously cannot guarantee real-time data consistency, such as:
1. After writing to the host, the host immediately hangs up and the master-slave switch is performed, and data may be lost at this time.
2. When a write operation occurs on the master, there will be a delay in synchronizing the data to the binlog of the slave. Therefore, when you query the slave immediately, you may not be able to find the data. The solution to this situation is:
1. Enable semi-synchronous replication. Previously, the master-slave synchronization information was asynchronous, which did not affect the logic of the master database. In semi-synchronous replication, the host waits for the binlog to be written to the relay log of (at least one) slave before the host determines to return it to the client.
2. Force sensitive data to call the host, but this makes the concept of read-write separation vague and is not recommended
3. Use middleware (canal). The general principle is that when a write request occurs, it is recorded in the cache and the time to synchronize to the slave is estimated.
When writing data to the master database and querying the slave database, the estimated time to write to the cache is used to determine the time on the slave machine, and whether to wait for the slave database or query the slave database directly.

This is the end of this article about understanding the MySQL master-slave synchronization principle. For more information about the MySQL master-slave synchronization principle, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!