Four master slave replication architectures commonly used in MySQL

Time:2020-10-21

catalog
  • One master multi slave replication architecture
  • Multi level replication architecture
  • Dual master replication architecture
  • Multi source replication architecture
  • How to optimize the master-slave delay problem?

The four common architectures replicated areOne master multi slave replication architectureMulti level replication architectureDual master replication architectureandMulti source replication architecture

One master multi slave replication architecture

In the scenario where the main database read request pressure is very high, you can configure theOne master multi slave replication architectureRealize read-write separation, divide a large number of read requests that are not particularly high on real-time performance to multiple slave databases through load balancing (read requests with high real-time requirements can be read from the master database), so as to reduce the reading pressure of the master database, as shown in the figure below.

In case of abnormal downtime of the master database, a slave database can be switched to the primary database to continue to provide services.

In the master-slave replication scenario, there will be a master-slave delay. How to solve this problem?

Multi level replication architecture

The architecture of one master and multiple slaves can meet the requirements of most scenarios with extremely high read request pressure. Considering that the replication of MySQL requires the master database to send binlog logs to the I / O threads of the slave database, the I / O pressure and network pressure of the master database will increase with the increase of the slave database (each slave library will have an independent binlog dump thread on the master database to send events)Multi level replication architectureIt solves the additional I / O and network pressure of the master database in the scenario of one master and many slaves. The multi-level replication architecture of MySQL is shown in the figure below.

Compared with a master multi-slave architecture, multi-level replication only adds a secondary master database master2 between master master database master 1 and slave database slave 1, slave 2 and slave 3. In this way, master database master 1 only needs to send binlog logs to master 2, which reduces the pressure on master database master 1. Master 2 sends binlog logs to all slave I / O threads of slave1, slave2 and slave3.

Multi level replication solves the I / O load and network pressure of the master database in the scenario of one master and multiple slaves. Of course, there are also disadvantages: the traditional replication of MySQL is asynchronous. In the multi-level replication scenario, the data of the master database only reaches the slave databases slave1, slave2, and slave3 after two replications, and the delay is larger than that in the scenario of one master multi-slave replication only once.

The delay of multi-level replication can be reduced by selecting Blackhole as the table engine on Master 2.As the name suggests, the blackhole engine is a “black hole” engine. The data written to the blackhole table will not be written to the disk. The blackhole table will always be empty. Insert, update and delete operations only record events in binlog.

CREATE TABLE `user` (
	`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`name` varchar(255) NOT NULL DEFAULT '',
	`age` tinyint unsigned NOT NULL DEFAULT 0
)ENGINE=BLACKHOLE charset=utf8mb4;
INSERT INTO `user` (`name`,`age`) values("itbsl", "26");
SELECT * FROM `user`;

As you can see, there is no data in the user table whose storage engine is blackhole.

The blackhole engine is very suitable for the scenario of masger2, the secondary master database: master2 does not undertake the read and write requests, only responsible for transferring the binlog log log to the slave database as soon as possible.

Dual master replication architecture

Dual master replication architectureIt is suitable for the scenario where the master-slave switch is required during DBA maintenance. The double master replication architecture avoids the trouble of repeatedly building the slave database. The dual master replication architecture is shown in the figure below.

Master database master1 and master are master-slave each other. All write requests of web client access master database master1 or master2. Join, DBA needs to do daily maintenance operations, in order to avoid affecting the service, you need to do the following operations.

  • First, stop the slave thread (stop slave) on the master1 library to avoid the subsequent maintenance operations on master2 database being copied to master1 database in real time, which will affect the service.
  • Secondly, stop the slave thread (stop slave) on the master2 library and start daily maintenance operations, such as modifying the varchar field from 10 to 200.
  • Then, after the maintenance operation is completed on the master2 database, open the slave thread (strip slave) on the master2 library to synchronize the data of master2 with the master1 database. After the synchronization is completed, the write operation of the application is switched to the master2 database.
  • Finally, after confirming that there is no application access on master1, open the slave thread (start slave) of master1.

The dual master replication architecture can greatly reduce the extra work of building slave database brought by the maintenance of master database under one master multi slave architecture.

Of course, the dual master architecture can also be used in combination with master-slave replication: slave databases such as slave1 and slave2 can be configured under the master2 library. In this way, the read pressure can be shared by slave databases such as slave1. Meanwhile, the extra work of rebuilding the slave database can be avoided while the DBA is maintaining. However, attention should be paid to the replication delay of the slave database. MySQL dual master multi-level replication architecture is shown below.

Multi source replication architecture

Multi source replication architectureIt is suitable for complex business requirements. It can support both OLTP (online transaction processing) and OLAP (online analytical processing). I will not draw the multi-source replication architecture of MySQL for the time being, and I will add it when I have time (drawing is also a physical work). If you are interested, you can read the book “MySQL database development, optimization and management maintenance” in simple language.

How to optimize the master-slave delay problem?

  • Optimize network environment
  • In MySQL 5.5 ~ MySQL 5.6, semi synchronous replication is used
  • In MySQL 5.7, enhanced semi synchronous replication (also known as lossless replication) can be used

For specific information about the master-slave delay of MySQL, see my other article to talk about several replication methods of master-slave replication in MySQL.

Organized from:
Deep understanding of MySQL database development, optimization, management and maintenance

Please indicate the link: https://www.cnblogs.com/itbsl/p/13508625.html

If this article is helpful to you, please clickrecommendThank you.

Recommended Today

Kafka learning materials

Kafka 1、 Benefits of message middleware 1. Decoupling It allows you to extend or modify processes on both sides independently, as long as you make sure they comply with the same interface constraints. It would be a great waste to put resources on standby to handle such peak visits. The use of message queue can […]