Common MySQL replication Architecture

Preface

Mysqlreplicaion is a simple architecture, that is, a MySQL Server (slave) replicates logs from another MySQL Server (master), then parses the logs and applies them to itself. In a replication environment, only two hosts running mysqlserver are required. In a simpler environment, we can start two mysqldinstances on the same physical server host, one is the master and the other is the slave to build the replication environment. However, in the actual application environment, we can use the mysqlreplication feature to build other replication architectures that are more conducive to scaleout based on actual business needs. Such as dualmaster architecture and cascade replication architecture. Next, we will analyze and introduce the three typical replication architectures.

 

General replication architecture master-slaves

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.

This architecture can be clearly presented:

The architecture implementation of one master node to copy multiple slave instances is very simple. There is no substantial difference between the implementation of multiple slave instances and a single slave. The master does not care how many slave instances are connected to itself. As long as the slave Io thread passes connection authentication, it requests the binarylog information at the specified location to him, according to the requirements of this Io thread, he will read his binarylog information and return it to the slave Io thread.

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.

For details about the configuration of replication, I have already made it very clear in the official MySQL documentation, and even introduced a variety of slave configuration methods, in the next section, we will also use a specific example to demonstrate the detailed process and precautions for building a replication environment.

 

Dualmaster replication architecture master-Master

Sometimes, the basic replication architecture of simply copying data from one MySQL to another MySQL may also require master switching in some specific scenarios. For example, you may need to stop the MySQL service when you need to perform some special maintenance operations on the master. At this time, in order to minimize the downtime of the application system write service, the best practice is to switch our slave node to the master to provide the write service.

However, the data on the original master node will be inconsistent with the actual data. When the original master is started to provide services normally, due to data inconsistency, we had to reverse the original master-slave relationship and re-build the replication environment, and use the original master as the slave to provide external read services. Re-building the replication environment will bring us a lot of extra work. If there is no proper backup, it may make the replication setup process very troublesome.

To solve this problem, we can avoid many problems by setting up the dualmaster environment. What is the dualmaster environment? In fact, two mysqlserver instances use each other as their own master, and copy each other as their own slave. 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. After the replication environment is set up, won't circular replication between two MySQL instances? 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.

The following figure shows the dualmaster replication architecture:

With the dualmaster replication architecture, we can not only avoid re-building the replication environment because of downtime required by normal routine maintenance operations, because all of us have recorded the location where we are copying data to the other party, when the system is up, it will automatically start copying again from the previous location, without human intervention, the maintenance cost is greatly reduced.

In addition, the dualmaster replication architecture is combined with some third-party ha management software, and after the master we are currently using has an exception and cannot provide services, quickly switch the other end to provide corresponding services, reducing the downtime caused by exceptions, and requiring no manual intervention.

Of course, we build a dualmaster environment, not to provide write services at both ends. Under normal circumstances, we will only enable the write service for one end. The other end only provides the read service, or does not provide any service at all, but only serves as a backup machine. Why do we usually only open one end to provide the write service? The main purpose is to avoid data conflicts and avoid data inconsistency. Because replication is an asynchronous implementation mechanism, even if the modifications executed on both sides are sequential, the modifications made later may be overwritten, it is like the following situation:

Time Point MySQL a MySQL B

1. Update the y record of Table X to 10.

2. Update the y record of Table X to 20.

3. obtain and apply the log, and update the y record of Table X to 10 (not as expected)

4. Obtain B logs and update table y to 20 (as expected)

In this case, not only is the data on Database B not the expected result, but the data on both sides of database A and database B is also inconsistent.

Of course, we can also use special conventions to make write operations on some tables all at one end, while write operations on some other tables all at the other end, so that the two ends do not operate on the same table, in this way, the above problem can be avoided.

 

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.

 

Dualmaster Architecture combined with cascade replication (master-slaves)

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. The image below clearly shows the composition of this architecture:

This architecture combines dualmaster with cascade replication. The biggest benefit is that it can avoid the impact of write operations on the master from 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.

 

Transferred from MySQL performance tuning and architecture