Common topology of MySQL Replication

Common topology of MySQL Replication
The replication architecture has the following basic principles:
(1) Each slave can have only one master;
(2) Each slave can have only one unique server ID;
(3) Each master can have many slave instances;
(4) If you set log_slave_updates, slave can be the master of other slave, thus spreading master updates.

MySQL does not support multi-master Replication-that is, one slave can have multiple masters. However, through some simple combinations, we can build a flexible and powerful replication architecture.

1. a single master and a multi-slave are the simplest scenarios where a replication system is composed of one master and one slave. Slave does not communicate with each other and can only communicate with the master.

In practical application scenarios, MySQL replication over 90% is an architecture mode where one Master node is replicated to one or more Slave instances. It is mainly used for database Expansion Solutions for applications with high read pressure. As long as the pressure on the Master and Slave is not too high (especially on the Slave end), the latency of asynchronous replication is usually very small. Especially since the replication method on the Slave end is changed to two threads for processing, the delay Problem on the Slave end is reduced. The benefit is that the real-time data requirements are not especially Critical. You only need to use a cheap pcserver to expand the number of Slave and distribute the read pressure to multiple Slave machines, the read performance bottleneck of the database can be solved by dispersing the read pressure of a single database server. After all, the read Pressure in most database application systems is much higher than the write pressure. This solves the database pressure Bottlenecks of many small and medium-sized websites to a large extent, and even some large websites are using similar solutions to solve database bottlenecks.

As follows:

This structure can be used if the number of write operations is small and the number of read operations is very high. You can distribute read operations to other slave instances to reduce the pressure on the master node. However, when slave increases to a certain number, slave's load on the master and network bandwidth will become a serious problem.
This structure is simple, but flexible enough to meet the needs of most applications. Some suggestions:
(1) Different slave plays different roles (for example, using different indexes or different storage engines );
(2) Use a Server Load balancer instance as the slave master and only copy the slave database;
(3) Use a remote slave for disaster recovery;

It should be clear that multiple Slave nodes can be copied from a Master node. Some may wonder if the Slave node can be copied from multiple Master nodes? At least for the time being, MySQL cannot be achieved. It is unclear whether MySQL will support MySQL in the future.

MySQL does not support the replication architecture of a server Load balancer node from multiple Master nodes, mainly to avoid conflicts and prevent data conflicts between multiple data sources, as a result, the last data is inconsistent. However, I have heard that some related patches have been developed to allow MySQL to support the replication of a Slave node from multiple Master nodes as data sources. This is also the benefit of the open source nature of MySQL.

2. Active Mode Master-Master (Master-Master in Active-Active Mode) Master-Master copies two servers, both master and slave of another server. In this way, any change made by either party will be copied and applied to the database of the other party.

Some readers may have a worry. The log-slave-updates option allows slave to write replication events into the binlog. If it is in a master-slave architecture, when log-slave-updates is started, will a transaction continuously loop between two mysql databases? In fact, MySQL has long thought of this, so the current MySQL server-id is recorded in MySQL BinaryLog, and this parameter must be explicitly specified when we set up MySQLReplication, in addition, the server-id parameter values of Master and Slave must be different in order to successfully build MySQL replication. Once the value of server-id is available, MySQL can easily determine which MySQLServer was originally generated for a change, so it is easy to avoid loop replication. Moreover, if we do not enable the BinaryLog option (-- log-Slave-update) for recording slave, MySQL will not record the changes in the replication process to BinaryLog, you don't have to worry about the possibility of loop replication.

 

Active Master-Master replication has some special functions. For example, both geographically distributed parts require their own writable data copies. The biggest problem with this structure is the update conflict. Assume that a table has only one row (one column) and its value is 1. If the two servers execute the following statement simultaneously:
Run the following command on the first Server:
Mysql> UPDATE tbl SET col = col + 1;
Run the following command on the second server:
Mysql> UPDATE tbl SET col = col * 2;
So what is the result? One server is 4 and the other server is 3, but this does not produce errors.
In fact, MySQL does not support multi-master Replication supported by other DBMS ), this is a huge limitation of the MySQL replication function (the difficulty of Multi-master servers lies in resolving update conflicts). However, if you have such requirements, you can use MySQL Cluster, by combining Cluster and Replication, you can build a powerful and high-performance database platform. However, you can simulate the replication of multiple master servers in other ways.

 

3, Active-Passive Mode Master-Master (Master-Master in Active-Passive Mode) This is the master-master structure changes, it avoids the disadvantages of the M-M, in fact, this is a fault tolerance and high availability system. The difference is that one service can only perform read-only operations.
4. Cascade replication architecture Master-Slaves

In some application scenarios, the read/write pressure may be significantly different, And the read pressure may be extremely high. A Master may need 10 or more server load balancers to support the read injection pressure. At this time, the Master will be more difficult, because there are more SlaveIO threads connected only, so when the write pressure is a little higher, the Master node consumes a lot of resources because of replication, which can easily lead to replication latency.

How can this problem be solved? At this time, we can use MySQL to record the changed BinaryLog information generated by replication on the Slave end, that is, enable the-log-slave-update option. Second-level (or more) replication reduces the pressure on the Master node due to replication. That is to say, we first use a few MySQL instances to replicate from the Master. We call these machines the first-level Slave cluster, then, other Slave instances are copied from the first-level Slave cluster. The server Load balancer instance that replicates data from the first-level Server Load balancer instance, which is called the second-level Server Load balancer cluster. If necessary, we can continue to add more levels of replication. In this way, we can easily control the number of Slave attached to each MySQL. This architecture is called the Master-Slaves architecture.

This multi-layer cascade replication architecture easily solves the risk that the Master node becomes a bottleneck because there are too many Slave instances. Demonstrate the Replication architecture of multi-layer cascade Replication.

Of course, if conditions permit, I would suggest you split them into multiple Replication clusters.

The above bottlenecks. After all, Slave does not reduce the write volume, and all Slave actually applies all data change operations without any write IO reduction. On the contrary, the more Slave, the more I/O writes to the entire cluster. We do not have a very obvious feeling. It is just because it is scattered across multiple machines, so it is not very easy to express.

In addition, the cascade level of replication is increased, and the same change is required to pass to the bottom-layer Slave, which may lead to a longer delay.

If we solve this problem by splitting the cluster, it may be much better. Of course, splitting the Cluster also requires more complex technologies and more complex application system architecture.

5. The Master-Master structure with slave servers has the advantage of providing redundancy. Geographically distributed replication structure, which does not have a single node failure problem, and can also put read-intensive requests on slave.

To a certain extent, cascade replication solves the bottleneck caused by the excessive number of Slave instances attached to the Master, however, it cannot solve the problem of manual maintenance and abnormal switchover. This naturally extends the Replication architecture that combines DualMaster with cascade Replication. I call it the Master-Slaves architecture.

Compared with the Master-Slaves architecture, the difference is only that the first-level Slave cluster is replaced with a separate Master, as a backup Master, then copy the Slave to a Slave cluster.

This architecture combines DualMaster with cascade Replication,The biggest benefit is that the write operation of the Master cannot be affected by the replication of the Slave cluster, at the same time, when the Master needs to be switched, Replication will basically not be redeployed. However, this architecture also has a drawback, that is, the Standby Master may become a bottleneck, because if the subsequent Slave cluster is large, the standby Master may become a bottleneck due to excessive SlaveIO thread requests.Of course, when the standby Master does not provide any read services, the bottleneck may not be very high. If a bottleneck occurs, you can perform cascade replication again after the standby Master, build a multi-layer Slave cluster. Of course, the more levels of cascade replication, the more obvious the data delay that may occur in the Slave cluster. Therefore, before using cascade replication, you also need to evaluate the impact of data delay on the application system.