MySQL replication mechanism principle explanation

Background

Replication is a complete copy of data. When it comes to why we need to replicate, the first thing that comes to mind is the fear of accidental data loss, which may cause losses to users.

After completing the data replication, you will find that its advantages are more than that. If one machine goes down, you can enable the data backed up on another machine. After all, the probability of downtime is very small, and the backup machine can share the traffic pressure of the main machine in spare time. In addition, when you need to upgrade the database version, you can upgrade the standby machine first without stopping user services, and then upgrade the main database when it is observed to be available and stable.

However, we cannot always let the DBA complete the replication by manual copying. In case of a crash while the DBA is on duty, the data generated during the period will not be backed up in time, which will lead to data loss in the standby database. Therefore, we still need to design a mechanism for automatic replication.

Designing replication mechanisms

We tentatively define the copied database as the master database and the pasted one as the slave database. To achieve replication from the master database to the slave database, it seems very simple. Only a scheduled task is needed to regularly copy a copy of the master database data file and transfer it to the server where the slave database is located.

But after all, scheduled tasks are not real-time. If the master database fails ten minutes after the last replication, the activated slave database will use the most recently replicated data, so ten minutes of data will be missing, and the consequences will be disastrous.

If real-time replication is still required, then the master database can send each executed statement to the slave database in real time, allowing the slave database to execute it immediately, thus ensuring the consistency of data on both sides.

What is not so good is that the master database sends data to the slave database in real time, and the next statement can only be processed after the slave database has finished executing, which seriously takes up the execution time of the master database. If there are too many slave databases, the master database will be useless.

It has to be changed to asynchronous to save time for the main database. The statements executed by the main database can be saved in a file and the slave database can retrieve it, so that the main database does not have to wait for the slave database. Since it is written to a file, the speed is very fast. The main database can write the statement to the file before execution to achieve higher synchronization efficiency.

For some of the above problems, the slave database cannot run to the master database to fetch data. It can only start a thread to establish a connection with the master database and request data from the master database. Then the master database also starts a thread to read the file content and push it to the slave database thread. After receiving the statement, the slave database can execute it immediately.

This is still very inefficient. The thread of the master database has to wait until the slave database receives the statement and completes execution before it can push the next one. If there are multiple slave databases, the master database will have to start multiple threads to maintain long-term communication with each slave database, occupying the master database server resources. It is better for the slave database to create a file to temporarily save the statement sent by the master database, save it first and then execute it slowly. This will reduce the pressure on the master database and the slave database will also be relieved.

Now that the slave has its own file for relay, there is no need to worry. The slave can start another thread and slowly execute the statements in the relay file. After the execution is completed, the original file has no value and can be cleaned up to avoid occupying server resources.

So far, the most basic replication mechanism has been designed. This replication method from the master database to the slave database is a typical master-slave architecture, which can be evolved on this basis. For example, if there are many slave databases, the master database needs to push data to each slave database, and the pressure on the master database will increase accordingly. Because the master database's responsibilities are not only to synchronize data, but also to read and write data, the data synchronization task can be replaced by someone else. For example, another master database is established between the master database and the slave database. The only responsibility of the newly established master database is to synchronize data to the slave database. In this way, the real master database only needs to push data to the newly established master database once, and can focus on reading and writing data the rest of the time.

This evolved replication mode is called a multi-level replication architecture. This article ends here. The above are two of the three replication architectures. In addition, there is also a "master-master" architecture. I will not go into details here. If you are interested, you can learn more about it yourself or pay attention to subsequent articles.

The above is all the knowledge about MySQL replication mechanism. Thank you for your support to 123WORDPRESS.COM.