MySQL Replication Common Architecture

transferred from: http://www.cnblogs.com/ggjucheng/archive/2012/11/13/2768879.html

Preface

Mysqlreplicaion itself is a simpler architecture, where a MySQL server (Slave) logs from another MySQL server (Master) and then parses the log and applies it to itself. A replication environment requires only two hosts running MySQLServer, or even simpler, we can start two mysqldinstance on the same physical server host, one as Master and the other as slave to complete the replication environment. But in the actual application environment, we can use the Mysqlreplication function according to the actual business demand to build up a variety of other more favorable scaleout replication architectures. such as Dualmaster architecture, cascading replication architecture, and so on. Here are some of the more typical three replication architectures to do some of the corresponding analysis.

General replication Schema master-slaves

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.

This architecture can be clearly demonstrated by:

A master replication of multiple slave implementations is very simple, and there is no substantive difference between multiple slave and the implementation of a single slave. On the master side is not care how many slave connected to their own, as long as there is slave IO thread through the connection authentication, request to him to specify the location of Binarylog information, he will follow the requirements of the IO thread, read his binarylog information, The IO thread returned to slave.

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.

For the configuration details of the replication, the official MySQL document has been said to be very clear, and even introduced a variety of implementation of slave configuration methods, In the next section we will also demonstrate a detailed process and considerations for building a replication environment through a specific example.

dualmaster Copy Schema Master-master

Sometimes, a simple copy from one MySQL to another MySQL basic replication architecture, may also need to be in some specific scenarios in the master switch. If you need to perform some special maintenance operations on the master side, you may need to stop the MySQL service. In this case, in order to minimize the application write service downtime, the best way is to switch our slave node to master to provide the service of writing.

However, our original master node's data will be inconsistent with the actual data. When the original master start can provide services normally, due to inconsistent data, we have to reverse the original Master-slave relationship, re-build the replication environment, and the original master as slave to provide read services. Re-building the replication environment will bring us a lot of extra work, and if there is no proper backup, it may make the replication process very cumbersome.

To solve this problem, we can avoid a lot of problems by building dualmaster environment. What is the Dualmaster environment? In fact, two mysqlserver each other as their master, themselves as each other's slave to replicate. In this way, changes made by either party are applied to the other party's database through replication.

Perhaps some readers will have a concern, so that after the replication environment, will not cause two mysql between the replication? 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.

A clearer presentation of the Dualmaster replication architecture consists of:

With the Dualmaster replication architecture, we are not only able to avoid the re-establishment of the replication environment due to the downtime required for normal routine maintenance operations, because at either end we have recorded where we are currently replicating to each other, and when the system is up, will automatically start to restart replication from the previous location, without the need for any intervention, greatly saving maintenance costs.

More than that, the Dualmaster replication architecture, combined with some third-party ha management software, can also quickly automatically switch the other end to provide the service, reducing the downtime caused by exceptional situations, after the exception of the master that we are currently using fails to provide services. And there is no need for human intervention at all.

Of course, we built a dualmaster environment, not to allow both ends to provide write services. Under normal circumstances, we will only open one end of the write service, the other end is just to provide read services, or completely do not provide any services, just as a spare machine exists. Why do we generally only open one end of it to provide write service? The main purpose is to avoid data conflicts and to prevent the inconsistency of data. Because replication is an asynchronous implementation mechanism even if the modifications executed on both sides are sequential, it can also result in even late modifications being overwritten by earlier modifications, as in the following scenario:

Point in time MySQL A MySQL B

1 update x table y record is 10

2 Update x table y record is 20

3 Get to a log and apply, update x table's Y record to 10 (does not meet expectations)

4 Get B log update x table y record 20 (meet expectations)

In this case, not only the data above the B-Library is not what the user expects, but the data on both sides of A and B are inconsistent.

Of course, we can also through the Special Convention, let some table write operation all at one end, and some other table writes all at the other end, guarantees the two sides not to operate the same table, thus can avoid the above problem occurrence.

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.

Dualmaster combined with cascading replication architecture (master-master-slaves)

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 following image shows a clearer picture of how this architecture is composed:

The great benefit of this dualmaster with cascading replication is that the primary master's write operations are not affected by the replication of the slave cluster, and that there is basically no re-replication when the master master needs to switch. 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.

From MySQL performance tuning and architecture

MySQL Replication Common Architecture