MySQL Replication common topology detailed

The architecture of replication has the following basic principles:
(1) Each slave can have only one master;
(2) Each slave can only have a unique server ID;
(3) Each master can have a lot of slave;
(4) If you set Log_slave_updates,slave to be the master of other slave, it will spread the update of master.

MySQL does not support multi-master server replication (multimaster Replication)-that is, a slave can have more than one master. However, with a few simple combinations, we can build a flexible and powerful replication architecture.

1, single master and multi-slaveIt is easiest to make a replication system from a master and a slave. The slave does not communicate with each other and only communicates with master.

In real-world scenarios, MySQL replication over 90% is a master copy to one or more slave schema patterns, primarily for low-cost, scalable solutions to database-side applications with large read-stress ratios. Because as long as the pressure of master and slave is not too great (especially the slave pressure), the delay of asynchronous replication is generally very small. Especially since the slave end of the replication mode changed to two thread processing, but also reduced the slave end of the delay problem. The benefit is that the real-time requirements of the data is not particularly critical application, only through the cheap pcserver to expand the number of slave, the reading pressure spread to multiple slave machines, Can solve the reading performance bottleneck of the database by dispersing the reading pressure of the single database server, after all, the reading pressure in most database application system is much higher than the writing pressure. This largely solves the current database pressure bottleneck of many small and medium-sized websites, and even some large websites are using similar schemes to solve the database bottleneck.

As follows:

This structure can be taken if the write operation is small and the read operation is very good. You can distribute the read operations to other slave, thereby reducing the pressure on the master. Butwhen the slave is increased to a certain number, the slave load on the master and the network bandwidth become a serious problem.
This structure is simple, but it is flexible enough to meet the needs of most applications. Some suggestions:
(1) Different slave play different roles (e.g. using different indexes, or different storage engines);
(2) using a Slave as the backup master, copy only;
(3) Use a remote slave for disaster recovery;

It should be clear to everyone, from a master node can be copied out of multiple slave nodes, some might think, that one slave node can be copied from more than the master node? At least for the time being, MySQL is not going to make it, and it is unclear whether it will support it in the future.

MySQL does not support a slave node from multiple master nodes to replicate the schema, mainly to avoid the problem of conflict, to prevent data conflicts between multiple data sources, resulting in inconsistencies in the final data. But I've heard that someone has developed a patch that allows MySQL to support a slave node to replicate from multiple master nodes as data sources, which is the benefit of MySQL's Open source nature.

2. Active mode Master-master (Master-master in active-active mode)Master-master replicates two servers, both master and slave of the other server. In this way, changes made by either party are applied to the other party's database through replication.

Some readers may have a concern,log-slave-updates option is to let slave replication events are also written into Binlog, if in the mutual master from the architecture, Does starting a log-slave-updates cause a transaction to continue looping between two MySQL ? In fact, MySQL has long thought of this, so in MySQL Binarylog recorded the current MySQL Server-id, and this parameter is also the time we build mysqlreplication must be explicitly specified, Moreover, the Server-id parameter values of master and slave are more inconsistent than necessary to make the mysqlreplication build successfully. Once you have the value of Server-id, it is easy for MySQL to tell which mysqlserver the change originated from, so it is easy to avoid a recurring replication situation. Also, if we do not open the slave binarylog option (--log-slave-update), MySQL will not record changes in the replication process to Binarylog, not to mention the possibility of a recurring replication situation.

active Master-master replication has some special uses. For example, two parts that are geographically distributed require a copy of their own writable data. The biggest problem with this structure is the update conflict. Suppose a table has only one row (column) of data, with a value of 1, if two servers execute the following statements at the same time:
mysql> UPDATE tbl SET col=col + 1;
executed on the second server:
mysql> UPDATE tbl SET col= Col * 2;
So what's the result? One server is 4 and the other server is 3, but this does not produce an error.
In fact, MySQL does not support several other DBMS-supported multi-primary server replication (multimaster Replication), This is a huge limitation of MySQL's replication functionality (the difficulty with multi-master servers is to resolve the update conflict), but if you really have this requirement, you can use MySQL Cluster and combine Cluster and replication. Can build a strong high-performance database platform . However, there are other ways to emulate this multi-master server replication.

3. Active-Passive mode master-master (Master-master in active-passive mode)This is a master-master structure change, it avoids the disadvantage of m-m, in fact, this is a fault-tolerant and high-availability system. The difference is that one of the services can only be read-only.
4.cascading replication Schema master–slaves-slaves

In some applications, it may be that the pressure difference between reading and writing is large, the reading pressure is particularly large, and a master may need 10 or more slave to support the pressure of the reading. At this time, master will be more difficult, because only the Slaveio thread is more, so write a little bit more pressure, the master side because replication will consume more resources, it is easy to cause replication delay.

How to solve this problem? At this point, we can use MySQL to record the binarylog information on the slave side of the change that was generated, that is, to open the-log-slave-update option. Then, a two-level (or more-level) copy is required to reduce the stress on the master side as a result of replication. That is, we first replicate from master through a handful of MySQL machines, which we would call the first level slave cluster, and then the other slave to replicate from the first level slave cluster. From the first level slave to replicate the slave, I call it the second level slave cluster. If necessary, we can continue to add more layers of replication. This makes it easy for us to control the number of slave attached to each mysql. This architecture I call master-slaves-slaves architecture.

This multi-tiered replication architecture makes it easy to address the risk of the master side becoming a bottleneck because of the fact that the secondary slave is too many. Shows the replication architecture for multi-level replication.

Of course, if the conditions allow, I would prefer to recommend that you break into multiple replication clusters to solve

The above bottleneck problem. After all, slave did not reduce the amount of write, all slave actually still applied all the data change operations and did not reduce any write Io. Conversely, the more slave, the greater the total amount of write Io for the entire cluster, the more we don't have a very obvious feeling, simply because it's scattered over multiple machines, so it's not easy to show.

In addition, increasing the cascade level of replication, the same change to the bottom of the slave need to pass the MySQL will also be more, the same may cause a longer delay risk.

And if we were to solve it by splitting the cluster, it would probably be much better, and of course, a split cluster would require more complex technologies and more complex application architectures.

5, with the Master-master structure from the server (Master-master with slaves) This structure has the advantage of providing redundancy. Geographically distributed replication structure, it does not have a single node failure problem, and can also put read-intensive requests on the slave.

cascading replication, to a certain extent, does solve the problem that Master has become a bottleneck because of the excessive number of slave attached to it, but he does not solve the problem of manual maintenance and the possibility of re-building replication after an exception needs to be switched. This naturally brings out the replication architecture that Dualmaster and cascade replication, which I call the master-master-slaves architecture

Compared to the master-slaves-slaves architecture, the difference is simply replacing the first-level slave cluster with a separate master, as an alternate master, and then copying from this alternate master to a slave cluster.

The greatest benefit of this dualmaster architecture is that it avoids the impact of the primary master's write operations from the replication of the slave cluster. While the master master needs to switch, there is basically no case of re-replication. However, this architecture also has a disadvantage, that is, the backup master may become a bottleneck, because if the subsequent slave cluster is larger, the standby master may be a bottleneck because of excessive SLAVEIO thread requests. of course, the standby master does not provide any read service, the bottleneck is not particularly high probability, if there is a bottleneck, you can also re-cascade replication after the standby master, set up a multilayer slave cluster. Of course, the more levels of Cascade replication, the more noticeable the data latency that may occur in the slave cluster, so before considering multi-level replication, you need to evaluate the impact of the data latency on the application system.

MySQL Replication common topology detailed