MySQL master-Slave synchronization principle

Replication Threads

　 　MySQL Replication is an asynchronous copy process, copied from one MySQL instace (we call Master) to another MySQL instance (we call it Slave).　　The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the Slave side, and 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, The file name and location of the Bin-log read to the master side are recorded in the Master-info file so that the next read will tell Master clearly "where I need to start the log content from somewhere in the Bin-log, please send me" 4. Slave's SQL thread detects that the contents of the Relay log are newly added, it will parse the log file immediately into the executable Query statement at the real execution time on the Master side andLine these Query.　　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, if you do this work independently through a single thread, copy the Binary log logs from the Master side and parse the logs.　 　And then in the process of self-execution become a serial process, the performance will naturally be subject to a large limit, the structure of the Replication delay 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 MySQL's Cluster know that when I write this part of the content, still is a memory database solution, that is, all the data needs to be included in the index all Load into theStorage, so that 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 synchronization principle