Mysql replication Architecture

Mysql replication Architecture
 
This is the most basic principle of mysql master-slave replication. The master is on the left and the slave is on the right. You can see the mysql replication process clearly. The master generates binary log binlog and outputs it to the binlog file, then, the Server Load balancer host requests log files. The master host sends binary data to the Server Load balancer host. After binary data is sent to the server Load balancer host, realylog is written, and the Server Load balancer host calls the SQL process to read the relay log Content, write data to the database to maintain data synchronization on the server.
 
Different from above, this architecture is a three-level architecture. Three mysql servers form a chain replication architecture.
From left to right, master -- slave, the first two are the same as master -- slave and the first graph. There is nothing to say, the only small change is that the intermediate Server Load balancer host has enabled the binary log, which writes the information updated from the master to its own logbin, provides binary logs for the following slave hosts. In this case, this slave host actually acts as the master host.
 
This figure shows the filtering of mysql binary logs, unless you want to synchronize all the databases (including mysql databases, this will cause unnecessary trouble ), binary log filtering mainly depends on the binlog_do_db and binlog_ignore_db settings of the master host. They decide what data will be recorded in the binary log and transmitted to the slave host, the slave host can also decide how to use the data by configuring some options, such as replicate_do_db and replicate_do_table.
 
This is a master-slave replication architecture with many practical applications. You can use the master node as the Update Server and several other slave nodes as the read-only server, to a certain extent, the database traffic is shared and data must be updated only on the master.

In this architecture, the two master nodes act as the master and slave nodes. The update and read operations can be performed on both the master and slave nodes at the same time,

This is a counterexample. This type of replication is not allowed. A Server Load balancer cannot accept the data of two master nodes at the same time. However, we can consider other methods to achieve this, for example, the following blackhole data storage engine can be implemented.

This is an extension of the master-master replication architecture. Each master has a layer of slave structure.

This is actually an extension of the master-master replication structure (my personal understanding), but the master structure forms a ring structure.

This is easy to understand and extends the slave Layer Based on the ring structure.

This figure is not easy to understand, because a blackhole storage engine is involved in the middle. blackhole is actually equivalent to/dev/null in linux.

The main meaning is the structure of one master N slave. Here N may have many, dozens, hundreds, in this way, the master will allocate a binlog dump process to each slave host, which will seriously affect the performance of the master. In this way, you can consider adding a distributed master between the master and slave nodes, when the blackhole storage engine is configured, it acts as a relay. It receives data but loses data, instead of storing it. It only reads the binary log of the master for the slave of the lower layer.

Here is an excerpt from others' explanation:
 
Use the blackhole engine as a server Load balancer instance to configure some filtering rules, such as copying some tables or not copying some tables. Then it acts as a master with multiple slave instances. This saves a certain amount of network bandwidth. Without blackhole, all the logs of the first master must be transferred to each slave. The server Load balancer instance is filtered by blackhole and master, and then transmitted to multiple Server Load balancer instances to reduce bandwidth usage. The reason why the blackhole engine is used is that it does not occupy the hard disk space. As a transit, it is only responsible for logging and transferring logs.
 
Like a "black hole", The BLACKHOLE storage engine receives data but discards it instead of storing it. The query always returns an empty set.

Mysql> create table test (I INT, c CHAR (10) ENGINE = BLACKHOLE;

Query OK, 0 rows affected (0.01 sec)

Mysql> insert into test VALUES (1, 'record one'), (2, 'record two ');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql> SELECT * FROM test;

Empty set (0.00 sec)

Although the BLACKHOLE table does not store any data, if Binary Log is enabled, the SQL statement is written into the Log, and the SQL statement is filtered out.

Other uses of the BLACKHOLE table:

* Compare the performance of BLACKHOLE when Binary Log is enabled or disabled to calculate the overhead of Binary Log records.
* BLACKHOLE is essentially a "no-op" storage engine, which may be used to find performance bottlenecks unrelated to the storage engine itself
The two images below are also related to the blackhole storage, so we will study blackhole in detail.


In the end, there are three mysql servers, 1, 2, and 3, which are used as slave servers for replication. We want to copy DB2 on DB1 and 2 at the same time, in this way, a blackhole DB1 can be introduced on 2 to solve the problem.