MySQL replication, master-slave and dual-master configurations

MySQL replication is a master-slave synchronization solution for multiple MySQL databases. It features Asynchronization and is widely used in various scenarios that require higher performance and reliability for MySQL. Another technique corresponding to this is the synchronized MySQL cluster, but because of its complexity, there are fewer users.

MySQL officially provides the scenario for using replication:

Replication principles

MySQL replication is an asynchronous replication process, from a MySQL node (called the master node) to another MySQL node (called the slave ). The entire replication process between the master and slave is mainly completed by three threads. Two threads (SQL thread and I/O thread) are on the slave side, another thread (I/O thread) is on the master side.

To implement MySQL replication, you must first enable the binary log on the master end, the whole replication process is actually because slave obtains the log from the master end and then executes the operations recorded in the log in full order on itself.

It seems that the replication principle of MySQL is very simple. Here is a summary:
* Only one master can be set for each slave.
* After the master executes the SQL statement, it records the binary log file (bin-log ).
* Connect to the primary database, obtain the BINLOG from the primary database, store it in the local relay-log, and execute the SQL statement from the last remembered position. If an error occurs, the synchronization is stopped.

From the perspective of these replication principles, we can draw these inferences:
* Databases between the master and slave databases are not synchronized in real time. Even if the network connection is normal, the master and slave databases may be instantly inconsistent.
* If the master-slave network is disconnected, the slave will be synchronized in batches after the network is normal.
* If you modify the slave data, it is very dangerous to stop synchronization because an error occurs during the execution of the main bin-log. Therefore, you should be very careful when modifying the data from the top.
* One derivative configuration is dual-master, which is a master-slave configuration. It works well as long as the modifications made by both parties do not conflict with each other.
* If you need multiple masters, you can use the ring configuration so that any node modifications can be synchronized to all nodes.

Master/Slave settings

Because the principle is relatively simple, replication is supported from MySQL 3 and can work on all platforms. Multiple MySQL nodes can even be different platforms, versions, and local networks. The replication configuration includes the user and my. ini (My. CnF in Linux.

First, create a user for slave on the master MySQL node:

Grant replication slave, replication client on *. * To 'slave '@ '192. 168.1.10' identified by 'slave ';

In fact, to support master-slave dynamic synchronization or manual switching, this user is generally created on all master-slave nodes. Then the MySQL configuration is completed. You need to modify the my. CNF or my. ini file. Add the following content in the section "mysqld:

Server-id = 1
Auto-increment = 2
Auto-increment-offset = 1
Log-bin
BINLOG-do-DB = mstest
Binlog_format = mixed

Master-host = 192.168.1.62
Master-user = slave
Master-Password = slave
Replicate-do-DB = mstest

In the above two settings, the first one is set as the master, and the last one is set as the slave. That is to say, you can add one segment on two MySQL nodes. BINLOG-do-DB and replicate-do-DB are the databases that need to be synchronized, auto-increment and auto-increment-offset are set to support dual-master (refer to the next section). You can also leave them unspecified when only the master and slave nodes are used.

Dual-Master Settings

From the original theory, MySQL also supports dual-Master Settings, that is, two MySQL nodes are mutually active and standby. However, in theory, dual-master can work well as long as data does not conflict with each other, however, in actual situations, data conflicts may occur. For example, before synchronization is completed, both parties modify the same record. Therefore, in practice, it is best not to allow both sides to modify at the same time. That is, the logic still works in the master-slave mode. However, the dual-master setting still makes sense, because after this is done, switching between the master and slave will become very simple. After a fault occurs, if a dual-master is configured, it is easy to directly switch between the master and slave databases.
When setting the dual master node, you only need to copy the preceding settings to write the configuration files of the two MySQL nodes, but you need to modify the corresponding server-id, auto-increment-offset and master-host. Auto-increment-offset is used to ensure that the dual-master node does not conflict with the ID when adding a table at the same time. Therefore, set auto-increment-offset to different values on the two nodes. Also, do not forget to create users for each other on both nodes. Load Balancing at the application layer This article only introduces the repilication configuration of MySQL. As shown in the figure above, with replication, you also need to use the application layer (or middleware) for load balancing, in this way, we can maximize the advantages of MySQL replication, which will be discussed later.