MySQL master-slave synchronization principle _ MySQL

This article mainly introduces the principle of MySQL master-slave synchronization. This article describes the master-slave synchronization overview, master-slave synchronization requirements, master-slave synchronization process, and so on. For more information, see Overview

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

Master-slave synchronization requirements

To implement Replication for MySQL, you must first enable the BinaryLog (mysql-bin.xxxxxx) function on the Master side, otherwise it cannot be implemented. The whole replication process is actually because Slave obtains the log from the Master end and then executes the operations recorded in the log in full order on itself. To enable the Binary Log of MySQL, you can use the "-log-bin" parameter option during MySQL Server startup, or. the "log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the cnf configuration file.

Master-slave synchronization process

The basic process of MySQL replication is as follows:

1. the IO thread on the Slave connects to the Master, and requests the log content after the specified location of the specified log file (or from the beginning of the log;
2. after the Master receives a request from the Slave IO thread, it reads the log information at the specified location of the specified log based on the request information and returns it to the Slave IO thread. In addition to the information contained in the Log, the returned information also includes the name of the Binary Log file on the Master end and its location in the Binary Log;
3. after the Slave IO thread receives the information, it writes the received log content to the end of the RelayLog File (mysql-relay-bin.xxxxxx) at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, in this way, you can clearly tell the Master "from where a bin-log is to be read, send it to me ".
4. after the Slave SQL thread detects the newly added content in the Relay Log, it will immediately parse the content in the Log file into the executable Query statements during actual execution on the Master end, and execute these queries on your own. In this way, the same Query is actually executed on the Master and Slave ends, so the data at both ends is exactly the same.

In fact, in the old version, MySQL replication implementation on the Slave end is not completed by collaboration between the SQL thread and the IO thread, instead, a single thread is used to complete all the work. However, MySQL engineers quickly discovered that there were great risks and performance problems, mainly as follows:

1. first, if you use a single thread to implement this independently, you can copy the Master end, BinaryLog logs, and parse these logs, then, the execution of this process becomes a serial process, and the performance will naturally be greatly limited. the Replication delay in this architecture is naturally longer.

3. after the replication thread on the Slave side obtains the Binary Log from the Master side, it needs to parse the content, restore it to the original Query executed by the Master side, and then execute it on its own. In this process, the Master end may have produced a lot of changes and generated a lot of Binary Log information. If the Master-side storage system encounters an irreparable fault 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 Slave side is under great pressure, because if the Slave is under great pressure, it takes longer to parse logs and apply these logs, more data may be lost.

Therefore, in the later transformation, in order to minimize this risk and improve the replication performance, the new version of MySQL will replace the replication on the Slave end with two threads, that is, the SQL thread and IO thread mentioned above. Yahoo! was the first to propose this improvement solution! Engineer JeremyZawodny ". This transformation not only solves the performance problem to a large extent, shortens the asynchronous delay time, but also reduces the potential data loss.

Of course, even after two threads are switched to the current mode for collaborative processing, there is still the possibility of Slave data latency and data loss. after all, this replication is asynchronous. As long as data changes are not in a transaction, these problems exist.

Summary

I have a preliminary understanding of the principle of MySQL master-slave replication.