Shulou(Shulou.com)05/31 Report--

What is the principle of the replication mechanism in MySQL, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Background introduction

Replication, that is, a complete copy of the data, when it comes to why to copy, the first thing you can think of is the fear of accidental loss of data, causing users to suffer losses.

When the data replication is completed, you will find that its advantage is more than that. If one machine goes down, you can enable backing up data on another machine. After all, the probability of downtime is very small, and leisure time can also allow the backup machine to share the traffic pressure of the host machine. In addition, when you want to upgrade the database version, you can first upgrade the standby machine without stopping the user service, and then upgrade the primary database when the stability of its availability is observed.

However, you cannot always let DBA copy manually to complete replication. In case of downtime when DBA is squatting, the data generated during squatting will be missing from the standby database because it is not backed up in time, so it is necessary to design a mechanism that can be replicated automatically.

Design replication mechanism

We tentatively decide that the copied database is the master library and the pasted database is the slave library. it seems very simple to copy the master database to the slave library. It only needs a scheduled task to copy a copy of the master database data file regularly and transfer it to the slave server.

But after all, the timing task is not real-time, in case the master database fails ten minutes after the last replication, the activated slave database uses the most recently replicated data, so the data will be missing for ten minutes, and the consequences will be unimaginable.

It is still necessary to copy in real time, so that the master library will send the statements executed by the master library to the slave database in real time, and let the slave library execute immediately, which will ensure that the data on both sides are consistent.

What is not so good is that the master database sends data to the slave database in real time, and the next statement can not be processed until the slave database is completed, which seriously takes up the execution time of the master database. If there are too many slave libraries, the master database will be invalidated.

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

In some of the above problems, the slave library cannot run to the master database to get data, so a thread can only establish a connection with the master library and request data from the master library, and then the master library also has a thread to read the contents of the file and push it to the slave database thread. It can be executed immediately after receiving the statement from the library.

The efficiency is still very low. The thread of the master library cannot push down one until it has received the statement from the library and finished its execution. If there are multiple slave libraries, the master library will open multiple threads to keep communication with each slave library for a long time and occupy the resources of the master database server. It is better to create a file from the slave library to temporarily save the statements sent by the master library, save it first and then execute it slowly. The pressure on the master library is small, and the slave library can rest assured.

Now that you have your own file from the library as a relay, there is no need to worry. You can start another thread from the library and slowly execute the statements in the relay file. After execution, the original file has no value, so it can be cleaned up so as not to occupy server resources.

So far, the most basic replication mechanism has been designed, and this replication mode from master library to slave library is a typical master-slave architecture, which can be evolved on this basis. For example, there are many slave libraries, and the master library will push data for each slave library. The pressure on the master library will increase accordingly, and because the responsibility of the master library is not only to synchronize data, but also busy reading and writing data, so synchronizing data can be replaced. For example, if you set up another master library between the master library and the slave library, the only responsibility of the newly established master library is to synchronize data to the slave database, so that the real master database only needs to push the data to the new master database once, and the rest of the time you can read and write the data.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.