MySQL 4 common master-slave replication architectures

One master and multiple slaves replication architecture

In a scenario where the read request pressure on the master database is very high, you can achieve read-write separation by configuring a one-master-multiple-slave replication architecture. A large number of read requests that do not require high real-time performance can be distributed to multiple slave databases through load balancing (read requests with high real-time performance requirements can be read from the master database), reducing the read pressure on the master database, as shown in the following figure.

In the event of an abnormal downtime of the main database, a slave database can be switched to the main database to continue providing services.

In the master-slave replication scenario, there will be master-slave delay. Think about how to solve it?

Multi-level replication architecture

The one-master-multiple-slave architecture can solve the needs of most scenarios with particularly high read request pressure. Considering that MySQL replication requires the master database to send BINLOG logs to the I/O thread of the slave database, the I/O pressure and network pressure of the master database will increase with the increase of slave databases (each slave database will have an independent BINLOG Dump thread on the master database to send events). The multi-level replication architecture solves the additional I/O and network pressure of the master database in the one-master-multiple-slave scenario. The multi-level replication architecture of MySQL is shown in the figure below.

Compared with the architecture of one master and multiple slaves, multi-level replication only adds a secondary master database Master2 in the middle of the replication of the master database Master1 to the slave databases Slave1, Slave2, and Slave3. In this way, the master database Master1 only needs to send BINLOG logs to one slave database Master2, which reduces the pressure on the master database Master1. The secondary master library Master2 then sends the BINLOG log to the I/O threads of all slave libraries 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, it also has disadvantages: MySQL's traditional replication is asynchronous. In the multi-level replication scenario, the data of the master database is replicated twice before reaching the slave databases Slave1, Slave2, and Slave3. The delay during this period is greater than that of only one replication in the scenario of one master and multiple slaves.

You can reduce the latency of multi-level replication by selecting BLACKHOLE as the table engine on the secondary master database Master2. As the name implies, the BLACKHOLE engine is a "black hole" engine. Data written to the BLACKHOLE table will not be written to the disk. The BLACKHOLE table is always an empty table. 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 the secondary master library Masger2: Master2 does not bear read and write requests, but is only responsible for transmitting BINLOG logs to the slave library as soon as possible.

Dual Master Replication Architecture

The dual master replication architecture is suitable for scenarios where the DBA needs to switch between master and slave databases for maintenance. The dual master replication architecture avoids the trouble of repeatedly building slave databases. The dual master replication architecture is shown in the following figure.

The master database Master1 and Master are mutually master-slave, and all Web Client write requests access the master database Master1 or Master2. Add, DBA needs to do daily maintenance operations, in order to avoid affecting the service, need to do the following operations.

• First, stop the Slave thread (STOP SLAVE) on the Master1 database to prevent subsequent maintenance operations on the Master2 database from being copied to the Master1 database in real time and affecting the service.
• Secondly, stop the Slave thread (STOP SLAVE) on the Master2 database and start routine maintenance operations, such as modifying the length of the varchar field from 10 to 200.
• Then, after completing the maintenance operation on the Master2 library, open the Slave thread (STRART SLAVE) on the Master2 library to synchronize the data of Master2 with the Master1 library. After the synchronization is completed, switch the application's write operation to the Master2 library.
• Finally, after confirming that there is no application accessing the Master1 library, open the Slave thread of Master1 (START SLAVE).
  

The dual-master replication architecture can greatly reduce the additional work of setting up slave libraries required for maintaining the master library in a one-master-multiple-slave architecture.

Of course, the dual-master architecture can also be used in conjunction with master-slave replication: configure slave libraries Slave1, Slave2, etc. under the Master2 library. This can share the reading pressure through slave libraries Slave1, etc., and avoid the extra work of rebuilding the slave library while the DBA is doing maintenance. However, you need to pay attention to the replication delay of the slave library. The MySQL dual-master multi-level replication architecture is shown below.

Multi-Source Replication Architecture

The multi-source replication architecture is suitable for complex business needs and 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. I will draw it and add it when I have time (drawing is also a physical job). If you are interested, you can read the book "MySQL Database Development, Optimization, Management and Maintenance in Easy Terms".

How to optimize the master-slave delay problem?

• Optimize network environment
• In MySQL 5.5~MySQL 5.6, use semi-synchronous replication
• In MySQL 5.7, enhanced semisynchronous replication (also known as lossless replication) is available.
  

For more information about MySQL master-slave delays, see my other article on several replication methods for MySQL master-slave replication.

Compiled from:

The book "MySQL Database Development, Optimization, Management and Maintenance in Easy Terms".

The above are the details of MySQL's four commonly used master-slave replication architectures. For more information about MySQL's master-slave replication architecture, please pay attention to other related articles on 123WORDPRESS.COM!