MySQL Master-Slave Library

http://wangwei007.blog.51cto.com/68019/965575

first, the principle of MySQL master and slave 1. Replication ThreadMySQL's Replication is an asynchronous replication process (mysql5.1.7 above versions are divided intoAsynchronous ReplicationAndSemi-synchronousTwo modes), copied 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,of these, two threads (SQL thread and IO thread) are on the Slave side, and another thread (IO thread) is 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. 2, the basic process of MySQL replication is as follows: 2.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.2.After 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; 2.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" 2.4.Slave's SQL thread detects that the content in the Relay log is newly added, it immediately resolves the contents of the log file into those executable query statements that were 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.3. MySQL Semi-synchronous mode (semisynchronous Replication)We know that before 5.5, MySQL replication is actually an asynchronous operation, not a synchronization, which meansthere is a certain delay between the master and slave data, MySQL originally designed the purpose may also be based on usability considerations, in order to ensure that the master is not affected by slave, and asynchronous replication allows master to be in a state of optimal performance: After writing the Binlog, you can commit without waiting for slave to complete the operation. This is a hidden problem, when you use slave as a backup, if master hangs, then there will be some committed transactions failed to successfully transfer to slave, which means data loss! In the MySQL5.5 version, the introduction of the semi-synchronous replication mode (semi-synchronous Replication) can successfully (but only relatively) avoid the pitfalls of the above data loss. In this mode:Master waits until the Binlog successfully transmits and writes at least one slave of relay log before it is committed, otherwise wait untilTimeout (default 10s). When a timeout occurs, master automatically switches the half-sync to asynchronous until at least one slave is successfully received and sent Acknowledge,master will then switch back to semi-synchronous mode. In combination with this new feature, we can ensure the absolute security of the synchronized data by allowing the loss of a certain amount of transaction throughput, since any submitted data will safely reach slave when you set timeout to a value that is large enough. The mysql5.5 version supports the semi-synchronous replication feature (Semisynchronous Replication), but it is not native supported, is supported by plugin, and is not installed by default. Whether it is a binary release, or its own source code compiled, will default to generate this plug-in, one for the master is for slave, you need to install the two plugins before use.

MySQL master-slave library