Architecture and principles of MySQL master-slave Synchronization

Architecture diagram: 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) in
Master end.

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 the 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 allow the dual master to add a table at the same time without the ID conflict, 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. Server Load balancer at the application layer this article only introduces the repilication configuration of MySQL. As shown in the figure above, with replication, a server Load balancer at the application layer (or middleware) is also required, in this way, MySQL can be maximized.
The advantages of replication will be discussed later.
MySQL replication is an asynchronous replication process, from one MySQL instace (we call it the master) to another MySQL instance (we call it the slave ). The entire replication process between the master and slave is mainly completed by three threads. Two threads (SQL thread and IO thread) are
Slave end, another thread (I/O thread) on the master end. To implement replication for MySQL, you must first enable the binary log (mysql-bin.xxxxxx) function on the master side, otherwise it cannot be implemented. 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. Open MySQL
Binary log can use the "-log-bin" parameter option during MySQL server startup, or. the "Log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the CNF configuration file. The basic process of MySQL replication is as follows: 1. the IO thread on the slave connects to the master and requests the log content after the specified location (or from the beginning) of the log file; 2. after the master receives a request from the slave Io thread, the IO thread responsible for replication reads the log information after the specified log location based on the request information and returns it to the slave Io thread. Besides the information contained in the log, the returned information also includes the name of the binary log file on the master end and
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) at the slave end in sequence, and record the file name and location of the bin-log on the master end to the master-info file, so that the next read can clearly show the High-Speed Master "I need to start from the location of a bin-log, please send it to me" 4. after the slave SQL thread detects the newly added content in the relay log, it will immediately parse the content in the log file into the executable query statements during actual execution on the master end, and execute these queries on your own. In this way, the same query is actually executed on the master and slave ends, so the data at both ends is exactly the same.