MySQL Replication Thread (understanding verbose process)

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 (mysqlbin.xxxxxx) function on the master side, otherwise it will not be possible. Because the entire replication process is actually a variety of actions recorded in the execution log that slave gets the log from the master and then executes it in its own full sequence

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 reads the master The file name and location of the Binlog is recorded in the Master-info file, so that the next read will be able to clear the high-speed master "I need to start from the location of some 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.

Perhaps some readers will have a concern, so that after the replication environment, will not cause two mysql between the replication?

In fact, MySQL has already thought of this, so in MySQL's binary log the current MySQL Server-id is recorded, and this parameter is also when we build MySQL Replication must be explicitly specified, and the master and slave's Server-id parameter values are inconsistent to make the mysqlreplication build successful. Once you have the value of Server-id, it is easy for MySQL to tell which MySQL Server a change originated from, so it is easy to avoid a recurring replication situation. And, if we don't open the option (--log-slave-update) of logging slave's binary log, MySQL will never record the changes in the copy process to binary log, not to mention the possibility of a recurring replication scenario.

MySQL Replication Thread (understanding verbose process)