Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to understand MySQL Replication threads, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Replication thread

Mysql's Replication is an asynchronous replication process, replicating from one Mysql instace (we call it Master) to another Mysql instance (we call it Slave). The whole replication process between Master and Slave is mainly completed by three threads, of which two threads (Sql thread and IO thread) are on the Slave side, and the other thread (IO thread) is on the Master side.

In order to realize the Replication of MySQL, we must first turn on the Binary Log (mysqlbin.xxxxxx) function on the Master side, otherwise it cannot be realized. Because the whole replication process is actually the various operations recorded in the log that Slave takes from the Master side and then performs the log in full sequence on itself.

The basic process of MySQL replication is as follows:

1. The IO thread on the Slave connects to the Master and requests the log contents from the specified location of the specified log file (or from the beginning of the log)

2. After Master receives the request from the IO thread of Slave, the IO thread responsible for replication reads the log information after the specified location of the log according to the request information, and returns it to the IO thread on the Slave side. In addition to the information contained in the log, the returned information also includes the name of the Binary Log file on the Master side and the location in the Binary Log.

3. After receiving the information, the IO thread of Slave writes the received log contents to the end of the Relay Log file (mysql-relay-bin.xxxxxx) on the Slave side, and records the file name and location of the read binlog on the Master side into the master-info file, so that it can clearly Master at a high speed on the next read. "where do I need to start the log content in a certain bin-log? please send it to me."

4. After Slave's SQL thread detects a new addition to Relay Log, it immediately parses the contents of the Log file into executable Query statements that are actually executed on the Master side, and executes these Query on its own. In this way, the same Query is actually executed on both the Master side and the Slave side, so the data on both sides is exactly the same.

Replication implementation level

Row Level

Statement Level

1. General replication Architecture (Master-Slaves)

Www.2cto.com

2.Dual Master replication Architecture (Master-Master)

Some readers may have a worry, after setting up the replication environment, will it not cause circular replication between the two MySQL? In fact, MySQL thought of this a long time ago, so the server-id of the current MySQL is recorded in the Binary Log of MySQL, and this parameter must be clearly specified when we build the MySQLReplication, and the server-id parameter values of Master and Slave need to be inconsistent to make the MySQLReplication build successfully. Once you have the value of server-id, it is easy for MySQL to determine which MySQL Server a change was originally generated from, so it is easy to avoid circular replication. Moreover, if we do not turn on the option to record Slave's Binary Log (--log-slave-update), MySQL will not record changes during replication to Binary Log at all, let alone worry about the possibility of circular replication.

3. Cascading replication architecture (Master-Slaves-Slaves...)

4.Dual Master combined with cascading replication architecture (Master-Master-Slaves)

The construction and implementation of MySQL Replication environment is relatively simple, generally speaking, it is actually four steps.

The first step is to prepare for the Master side.

The option for 1.MySQL recording Binary Log is turned on

2.GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.0.2'

The second step is to take a "snapshot" backup of the data on the Master side.

Test the group_message table under dump example:

Dump-- master-data-usky-p example group_message > group_message.sql

Www.2cto.com

The third step is to restore the backup "snapshot" of Master on the Slave side.

The fourth step is to set the Master-related configuration on the Slave side, and then start replication

A total of 5 items need to be set for the CHANGE MASTER TO command, which are:

Hostname (or IP address) of the MASTER_HOST:Master

The user name of MASTER_USER:Slave to connect to Master is actually the repl user created earlier.

MASTER_PASSWORD:Slave password of the user who connects to Master

MASTER_LOG_FILE: the name of the log file that started replication

MASTER_LOG_POS: the location of the log file that started the replication, that is, the Log Position that was consistently mentioned during the previous introduction to backup sets.

On how to understand the MySQL Replication thread to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.