MySQL Replication thread Analysis

The Replication of the Replication thread Mysql is an asynchronous Replication process, from one Mysql instace (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. To implement MySQL Replication, you must first enable the Binary Log (mysqlbin. xxxxxx) function on the Master side. Otherwise, it cannot be implemented. Because the whole replication process is actually the basic process for Slave to obtain the log from the Master end and then perform the operations recorded in the complete sequence of execution logs on itself www.2cto.com 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 (or from the beginning) of the log file; 2. after the Master receives a request from the Slave IO thread, the IO thread responsible for replication reads the log information after the specified log location 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 Relay Log file (mysql-relay-bin.xxxxxx) at the Slave end in sequence, and record the file name and location of the read binlog on the Master end to the master-info file, so that the next read can clearly show the High-Speed Master "I need to start from the location of a bin-log, please 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. Replication implementation level Row LevelStatement Level1. General replication architecture (Master-Slaves) www.2cto.com

2. Dual Master replication architecture (Master-Master)

Some readers may have a worry. After the replication environment is set up, won't circular replication between two MySQL instances? In fact, MySQL has long thought of this, so the current MySQL server-id is recorded in the MySQL Binary Log, and this parameter must be explicitly specified when we set up MySQL Replication, in addition, the server-id parameter values of Master and Slave must be different in order to successfully build MySQL replication. Once the value of server-id is available, MySQL can easily determine the MySQL Server from which a change was originally generated, so it is easy to avoid loop replication. Moreover, if we do not enable the -- Log-Slave-update option for logging the slave Binary log, MySQL will not record the changes in the replication process to the Binary Log, you don't have to worry about the possibility of loop replication. 3. Cascade replication architecture (Master-Slaves ...)

4. Dual Master and cascade replication architecture (Master-Slaves)

The establishment and implementation of the MySQL Replication environment is relatively simple. In general, there are actually four steps. The first step is to prepare the Master. 1. the option for logging Binary Log in MySQL is enabled; 2. grant replication slave on *. * TO 'repl' @ '192. 168.0.2 '; the second step is to obtain the "snapshot" backup of the Master data. Test the group_message table in the dump example Database: mysqldump -- master-data-usky-p example group_message> group_message. SQL www.2cto.com. Step 3 restores the Master's backup "snapshot" on the Slave side ". Step 4 is TO configure the Master on the Slave side, and then start TO copy the change master to command. A total of five items need TO be set: MASTER_HOST: Master Host Name (or IP address ); MASTER_USER: the username used by Slave to connect to the Master, which is actually the repl user created earlier; MASTER_PASSWORD: password of the user used by Slave to connect to the Master; MASTER_LOG_FILE: name of the log file to be copied; MASTER_LOG_POS: the location of the Log file to be copied, that is, the Log Position mentioned in the backup set process. Author bengda