Slime: Learning MySQL Database master-slave synchronous replication principle

This article by show according to Lin Feng to provide friendship sponsorship, starting in the mud row world.

Description This article is reproduced in part from the Internet.

MySQL replication (English for replication) is a multi-MySQL database master from the synchronous scheme, characterized by asynchronous replication, widely used in a variety of MySQL have higher performance, higher reliability requirements of the occasion. Another synchronization technique is MySQL Cluster, but because the MySQL Cluster configuration is more complex, there are fewer users.

MySQL's replication is a process of asynchronous replication (mysql5.1.7 the above version is divided into asynchronous and semi-synchronous modes), which is from a MySQL instance (instance English as an instance) (we call it master) Copy to another MySQL instance (we call it slave). The entire replication process between master and slave is performed 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 master side of the Binlog (mysql-bin.xxxxxx) log function, or you will not be able to achieve MySQL master-slave replication. Because MySQL's entire master-slave replication process is essentially: the slave end obtains the Binlog log from the master, and then executes the various SQL operations recorded in the log in full order on its own.

For details on how to turn on MySQL's binlog log feature, you can check out this article, "Slime: Learning about MySQL's binlog configuration."

The basic interactive process for MySQL master-slave replication is as follows:

1. The IO line of the slave end is thread attached connected to the master side and requests the log content to be copied from the specified POS node location (or from the first log) of the specified Binlog log file.

2, the master side after receiving the IO thread request from the slave side, notifies the IO thread responsible for the replication process, reads the log information after the specified Binlog log specifies the POS node location according to the request information of the slave-side IO thread, and then returns the IO thread to the slave side. In addition to the information contained in the Binlog log, the return information includes the Binlog file name on the master side of the returned information and the POS node location in the Binlog log.

3. The IO thread of the slave end writes the received Binlog log content to the end of the Relaylog file (mysql-relay-bin.xxxxxx) on the slave side after receiving the information returned by the master side io, and records the Binlog file name and POS node location of the read to the master side to Master-info (the file exists in the slave-side) file so that it can be clearly told to master at the next read I need to start from which POS node location of the Binlog file, please send me the log contents of this node later. "

4. The SQL thread on the slave side resolves the contents of the log file as soon as it detects what is new in the Relaylog file. It then reverts to those SQL statements that were actually executed on the master side, and executes the SQL statements sequentially, in the same order. In this way, the same SQL statements are 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 rather awkward, understanding is also more troublesome, I simplified the interactive process. As follows:

1. After executing SQL, master logs the binary log file (Bin-log).

2, slave connect master, and get Binlog from master, save in local relay-log, then execute SQL statement from last remembered position, and stop synchronizing once an error is encountered.

From the above MySQL replication principle can be seen:

* Master-slave database is not real-time synchronization, even if the network connection is normal, there is an instantaneous master-slave data inconsistency situation.

* If the master-slave network is disconnected, the library will be synchronized in batches after the network has returned to normal.

* If you modify the data from the library, then if the library is executing the bin-log of the main library, there will be an error and stop the synchronization, this is a very dangerous operation. So in general, we have to be very careful to modify the data from the library.

* A derivative configuration is dual-master, inter-primary from the configuration, as long as the two sides of the changes do not conflict, it can work well.

* If you need a multi-master library, you can use a ring configuration so that any node modification can be synchronized to all nodes.

Slime: Learning MySQL Database master-slave synchronous replication principle