MySQL master-slave replication principle

Replication Threads

MySQL's Replication is aAsynchronousCopy process from one MySQL instace (we call it Master) to another MySQL instance (we call it Slave). The implementation of the entire replication process between Master and Slave is performed by a total of three threads,Two of these threads (SQL thread and IO thread) on the Slave side，another thread (IO thread) on the Master side。
To implement MySQL Replication, you must first turn on the binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it will not be possible. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence. The Binary Log for MySQL can be opened by using the "-log-bin" parameter option in the process of starting MySQL Server, or by adding "Log-bi" in the mysqld parameter group in the MY.CNF configuration file (the Parameters section after the [mysqld] identity) N "parameter entry.
The basic process for MySQL replication is as follows:
1. Slave the above IO line thread attached the Master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;
2. When Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side. In addition to the information contained in the log, the return information includes the name of the binary log file on the Master side of the returned information and its location in binary log;
3. After the Slave IO thread receives the information, it writes the received log content to the end of the relay log file (mysql-relay-bin.xxxxxx) on the Slave side, And the read to the master side of the Bin-log's file name and location to the Master-info file, so that the next time you read the high-speed master "I need to start from somewhere in the Bin-log log content, please send me"
4. When the Slave SQL thread detects a new addition to the Relay log, it immediately parses the contents of the log file into those executable query statements that are executed at the Master end, and executes the query itself. In this way, the same Query is actually executed on the Master and Slave ends, so the data on both ends is exactly the same.
In fact, in the old version, the replication implementation of MySQL was done on the Slave side not by the two threads of the SQL thread and IO thread, but by a single thread to do all the work. But MySQL engineers quickly found that there were a lot of risks and performance issues, mainly as follows:
First of all, if the work is done independently by a single thread, it makes it possible to replicate the Binary log log on the Master side and parse the logs, and then perform this process as a serial process, and the performance is naturally subject to greater limitations, which The delay of the Replication is naturally longer.
Second, after the copy thread from the Slave side gets the Binary Log from the master, it needs to parse the content back into the original Query executed by the master and execute it on its own. In this process, the master side is likely to have generated a lot of changes and generated a lot of Binary Log information. If the storage system at the Master end of this stage fails to repair, all changes made at this stage will be lost forever and cannot be found again. This potential risk is particularly pronounced when the Slave is at a high pressure ratio, because if the Slave pressure is large, the time taken to parse the logs and apply them will naturally be longer, and more data may be lost.
Therefore, in the late transformation, the new version of MySQL in order to minimize this risk, and improve the performance of replication, the Slave end of the replication to two threads to complete, that is mentioned earlier SQL thread and IO thread. The first to propose this improvement is Yahoo! 's engineer, "Jeremy Zawodny". Through such transformation, this solves the performance problem to a large extent, shortens the asynchronous delay time and reduces the potential data loss.
Of course, even with the two threads that are now working together, there is also the possibility of Slave data latency and data loss, after all, the replication is asynchronous. These problems exist as long as the data changes are not in one transaction.
If you want to completely avoid these problems, you can only use the MySQL Cluster to solve. But the MySQL Cluster know the author writes this part of the time, still is a memory database solution, namely need to all the data including index all Load into memory, so the memory requirements are very large, for the general popularity of the application is not too large to implement. Of course, when I first learned about MySQL's CTO David, MySQL is now constantly improving its Cluster implementation, and one of the big changes is that it allows the data to be not all load into memory, but just the index all load into memory, I believe in the completion of the change MySQL Cluster will be more popular and more likely to be implemented.

MySQL master-slave replication principle