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

In this issue, the editor will bring you about the principle of master-slave synchronization of MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Replication thread

MySQL's Replication is an asynchronous replication process, replicating from one Mysql instace (we call it Master) to another Mysql instance (we call it Slave). The whole replication process between Master and Slave is mainly completed by three threads, of which two threads (Sql thread and IO thread) are on the Slave side, and the other thread (IO thread) is on the Master side.

In order to realize the Replication of MySQL, we must first turn on the Binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it cannot be realized. Because the whole replication process is actually the Slave takes the log from the Master side and then performs the various operations recorded in the log in full sequence on its own. You can open the Binary Log of MySQL by using the "--log-bin" parameter option when starting MySQL Server, or by adding the "log-bin" parameter item to the mysqld parameter group (the parameter section identified by [mysqld]) in the my.cnf configuration file.

The basic process of MySQL replication is as follows:

1. The IO thread on the Slave connects to the Master and requests the log contents from the specified location of the specified log file (or from the beginning of the log)

2. After Master receives the request from the IO thread of Slave, the IO thread responsible for replication reads the log information after the specified location of the log according to the request information, and returns it to the IO thread on the Slave side. In addition to the information contained in the log, the returned information also includes the name of the Binary Log file on the Master side and the location in the Binary Log.

3. After receiving the information, the IO thread of Slave writes the received log contents to the end of the Relay Log file (mysql-relay-bin.xxxxxx) on the Slave side, and records the file name and location of the read bin-log on the Master side into the master-info file, so that the next time it is read, it can clearly tell Master, "where do I need to start the log content of a certain bin-log, please send it to me"

4. After Slave's SQL thread detects a new addition to Relay Log, it immediately parses the contents of the Log file into executable Query statements that are actually executed on the Master side, and executes these Query on its own. In this way, the same Query is actually executed on both the Master side and the Slave side, so the data on both sides is exactly the same.

In fact, in the old version, the replication implementation of MySQL on the Slave side was not done by the cooperation of the SQL thread and the IO thread, but by a single thread to do all the work. But MySQL engineers quickly discovered that there were significant risks and performance problems with doing so, mainly as follows:

First of all, if you implement this work independently through a single thread, the Binary Log logs on the Master side will be copied, and these logs will be parsed, and then the process executed by yourself will become a serial process, the performance will naturally be greatly limited, and the delay of Replication under this architecture will naturally be longer.

Second, after the copy thread on the Slave side gets the Binary Log from the Master side, it needs to parse the content, restore it to the original Query executed on the Master side, and then execute it on its own. In this process, the Master side may have produced a lot of changes and generated a large amount of Binary Log information. If there is an irreparable failure of the storage system on the Master side at this stage, all changes made at this stage will be lost forever and cannot be recovered. This potential risk is especially prominent when the pressure on the Slave side is high, because if the Slave pressure is high, it will naturally take longer to parse and apply the logs, and more data may be lost.

Therefore, in the later transformation, in order to minimize this risk and improve the performance of replication, the new version of MySQL changes the replication on the Slave side to two threads, that is, the SQL thread and the IO thread mentioned earlier. The first person to propose this improvement is Yahoo! One of the engineers is Jeremy Zawodny. Through this transformation, it not only solves the performance problem to a great extent, shortens the asynchronous delay time, but also reduces the potential data loss.

Of course, even after the two threads are now working together, there is still the possibility of Slave data latency and data loss, after all, the replication is asynchronous. These problems exist as long as the change to the data is not in a transaction.

If you want to avoid these problems completely, you can only use MySQL's Cluster to solve them. However, until the author wrote this part of the content, the Cluster of MySQL is still a solution of in-memory database, that is, all data, including indexes, need to be Load into memory, so the requirement of memory is very large, and the implementability is not too great for general popular applications. Of course, before communicating with MySQL's CTO David, I learned that MySQL is now constantly improving its Cluster implementation, one of the very big changes is to allow data not all Load to memory, but only index all Load to memory, I believe that after the completion of this transformation, MySQL Cluster will be more popular and more implementable.

The above is the principle of MySQL master-slave synchronization shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.