Rotten mud: Learn the principle of mysql database master-slave synchronous replication, mysql master-slave

Rotten mud: Learn the principle of mysql database master-slave synchronous replication, mysql master-slave

This article was sponsored by Xiuyi linfeng and first launched in the dark world.

This article is partly reproduced on the Internet.

MySQL Replication is a master-slave synchronization solution for multiple MySQL databases. It features asynchronous Replication and is widely used in various scenarios that require higher performance and reliability for MySQL. Another synchronization technology is MySQL Cluster. However, because the configuration of MySQL Cluster is complex, there are fewer users.

MySQL Replication is an asynchronous Replication process (Mysql 5.1.7 and later versions are divided into two modes: asynchronous Replication and semi-synchronization). It is a MySQL instance) (We call it "Master") to another Mysql instance (we call it "slave ). The entire replication process between the master and slave is mainly completed by three threads, two of which are on the slave side (SQL thread and IO thread) and the other (IO thread) on the master side.

To achieve Replication of MySQL, you must first enable the binlog (mysql-bin.xxxxxx) log function on the master side, otherwise the master-slave Replication of mysql cannot be achieved. Because the whole master-slave replication process of mysql is actually: The slave side obtains the binlog log from the master side, and then executes the SQL operations recorded in the log in full order on itself.

For details about how to enable the binlog function of mysql, refer to this Article "rotten mud: Learning mysql binlog configuration".

The basic interaction process of MySQL master-slave replication is as follows:

1. The I/o thread on the slave end connects to the master end and requests to copy the log content after the specified pos node location (or from the initial log) of the specified binlog log file.

2. After the master receives an IO thread request from the slave end, it notifies the IO thread responsible for copying the process, according to the request information of the IO thread of the slave end, read the log information after the specified position of the pos node in the specified binlog, and then return it to the IO thread on the slave side. In addition to the information contained in the binlog, the returned information also includes the binlog file name on the master end and the position of the pos node in the binlog.

3. After the slave IO thread receives the information returned by the master IO, it writes the received binlog content to the end of the relaylog file (mysql-relay-bin.xxxxxx) of the slave end in sequence, the read binlog file name and pos node location on the master end are recorded in the master-info (this file exists on the slave end) file, in this way, you can clearly tell the master "from which position of the pos node of the binlog file I want to start from" in the next read. Please send the log Content from this node to me ".

4. After the SQL thread on the slave side detects the new content in the relaylog file, it will immediately parse the content in the log file. Then it is restored to the SQL statements actually executed on the master side, and these SQL statements are executed in sequence by SF itself. In this way, the same SQL statement is actually executed on the master and slave ends, so the data on the master and slave ends is exactly the same.

The above mysql master-slave replication interaction process is relatively difficult to understand. I simplified the interaction process. As follows:

1. After the master executes the SQL statement, it records the binary log file (bin-log ).

2. slave connects to the master, obtains the binlog from the master, stores it in the local relay-log, and executes the SQL statement from the last remembered position. If an error occurs, the synchronization is stopped.

From the above mysql Replication principles, we can see that:

* Databases between the master and slave databases are not synchronized in real time. Even if the network connection is normal, the master and slave data may be inconsistent instantly.

* If the master-slave network is disconnected, the slave database synchronizes data in batches after the network recovers.

* If you modify the data of the slave database, if the slave database is executing the bin-log of the master database, the synchronization will be stopped due to an error. This is a very dangerous operation. Therefore, we should be very careful when modifying the data in the slave database.

* One derivative configuration is a dual-master and mutual master-slave configuration. It works well as long as the modifications made by both parties do not conflict with each other.

* If multiple master databases are required, you can use a circular configuration so that any modifications to a node can be synchronized to all nodes.