MySQL master-slave synchronization principle

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The Replication thread

   Mysql Replication is an asynchronous replication process, from a Mysql instace (called Master) is copied to another Mysql instance (Slave known). In the realization of the whole replication process between Master and Slave is mainly composed of three threads to complete, two of which thread (Sql thread and IO thread) in the Slave end, another thread (IO threads) at Master end.
  To realize the MySQL Replication, must first open the Master end of the Binary Log (mysql-bin.xxxxxx) function, otherwise unable to realize. Because of various operation records the entire replication is actually a process of Slave from the Master for the log and then in yourself completely sequential execution log. Open the MySQL Binary Log can be passed in the boot MySQL Server process using the "log-bin" option, or mysqld parameters in my.cnf config file (parameter part [mysqld] after the identification of) add "log-bin" parameter.
  The basic process of MySQL replication.:
  1 Slave above the IO thread connection to the Master, and the request from the specified location specified log file (or from the beginning of the log log content after);
   2 Master received from the Slave IO thread request, by is responsible for copying IO thread according to the request information read the specified log log log information location, returned to the Slave end of the IO thread. Returns information in addition to log the information contained in the name, also includes the Binary Log file the return information in Master terminal and location within the Binary in Log;
  3 Slave IO thread after receiving the message, will log content received are written to the Slave end of the Relay Log file (mysql-relay-bin.xxxxxx) of the terminal, and will read Master end of the bin-log file name and location of the recorded master- info file, when to read in the next to the high speed Master "clearly I need to log back from which the location of a bin-log, please send to me"
   4 Slave SQL thread detected Relay Log in the newly added content, will be immediately on the contents of the Log file into the executable Query statement execution time in real Master terminal, and in its implementation of these Query. So, is actually in the Master and Slave terminal execute the same Query, so both ends of the data is exactly the same.
  In fact, in the old version, MySQL replication on Slave side and not by the SQL thread and IO thread two thread together and complete, but from a separate thread to finish all the work. But MySQL's engineers soon discovered, it risks and huge performance problems, mainly as follows:
   First of all, if by a single thread to perform the work, makes the replication of the Master terminal, Binary Log log, and resolve these logs, and then in the process of its implementation of the process of becoming a serial, performance will severely limit, the architecture of the Replication delay nature relatively long.
   Secondly, after the transfer thread at Slave obtained from the Master Binary Log came to end, then parses the contents, reduced to perform the Master end of the original Query, then in its implementation. In this process, the Master end is likely to have produced a lot of change and generate a lot of Binary Log information. If the storage system at this stage the Master end of the fault can not be repaired, all changes so generated at this stage will be forever lost, can never get it back. The potential risk in the Slave end of the pressure is relatively large when especially, because if Slave pressure is relatively large, the cost analysis and the application of these log log time will be longer, possibly with missing data will be more.
   So, in the later transformation, the new version of the MySQL in order to minimize the risk, and improve the performance of Slave replication, the end replication into two threads to complete, also is the previously mentioned SQL thread and IO thread. First put forward the improvement scheme is an engineer "Yahoo! Jeremy Zawodny". Through this transformation, so that solve the performance problem is to a large extent, shorten the asynchronous delay time, while also reducing the potential data loss.
  Of course, even into the now two threads to cooperate, also still exists the possibility of Slave data delay and data loss, after all this replication is asynchronous. As long as the change in the data is not in a single transaction, these issues are present.
  If you want to completely avoid these problems, we can only use MySQL Cluster to resolve. But MySQL Cluster knows when I write this part of content, still a memory solution according to library, is the need to put all data including index all the Load into memory, so the memory requirements is very large, for the popular application of the general implementation is not too big. Of course, when the CTO David before and MySQL know, MySQL is the improvement of Cluster, a very large one change would be to allow data without all the Load into memory, but only to index all the Load into memory, I think after the letter at the completion of the transformation of MySQL Cluster will be the more popular, the implementation will be greater.
Making making MySQL master-slave replication structure diagram as follows:

 
 
 
The original
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Yolanda at December 04, 2013 - 6:37 PM